Zoeken…


SQLite.NET gebruiken in een gedeeld project

SQLite.NET is een open source-bibliotheek die het mogelijk maakt ondersteuning van lokale databases toe te voegen met behulp van SQLite versie 3 in een Xamarin.Forms project.

De onderstaande stappen laten zien hoe u dit onderdeel kunt opnemen in een gedeeld Xamarin.Forms project:

  1. Download de nieuwste versie van de klasse SQLite.cs en voeg deze toe aan het Shared Project.

  2. Elke tabel die wordt opgenomen in de database moet worden gemodelleerd als een klasse in het gedeelde project. Een tabel wordt gedefinieerd door ten minste twee attributen aan de klasse toe te voegen: Table (voor de klasse) en PrimaryKey (voor een eigenschap).

Voor dit voorbeeld wordt een nieuwe klasse met de naam Song toegevoegd aan het Shared Project, als volgt gedefinieerd:

using System;
using SQLite;

namespace SongsApp
{
    [Table("Song")]
    public class Song
    {
        [PrimaryKey]
        public string ID { get; set; }
        public string SongName { get; set; }
        public string SingerName { get; set; }
    }
}
  1. Voeg vervolgens een nieuwe klasse toe met de naam Database , die SQLiteConnection klasse SQLiteConnection (opgenomen in SQLite.cs). In deze nieuwe klasse is de code voor databasetoegang, het maken van tabellen en CRUD-bewerkingen voor elke tabel gedefinieerd. Voorbeeldcode wordt hieronder weergegeven:
using System;
using System.Linq;
using System.Collections.Generic;
using SQLite;

namespace SongsApp
{
    public class BaseDatos : SQLiteConnection
    {
        public BaseDatos(string path) : base(path)
        {
            Initialize();
        }

        void Initialize()
        {
            CreateTable<Song>();
        }

        public List<Song> GetSongs()
        {
            return Table<Song>().ToList();
        }

        public Song GetSong(string id)
        {
            return Table<Song>().Where(t => t.ID == id).First();
        }

        public bool AddSong(Song song)
        {
            Insert(song);
        }

        public bool UpdateSong(Song song)
        {
            Update(song);
        }

        public void DeleteSong(Song song)
        {
            Delete(song);
        }
    }
}
  1. Zoals u in de vorige stap kon zien, de constructeur van onze Database klasse omvat een path parameter, die de plaats van het bestand dat slaat de SQLite database-bestand vertegenwoordigt. Een statisch Database object kan worden gedeclareerd in App.cs Het path is platformspecifiek:
public class App : Application
{
    public static Database DB;

    public App ()
    {
        string dbFile = "SongsDB.db3";

#if __ANDROID__
        string docPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        var dbPath = System.IO.Path.Combine(docPath, dbFile);
#else
#if __IOS__
        string docPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        string libPath = System.IO.Path.Combine(docPath, "..", "Library");
        var dbPath = System.IO.Path.Combine(libPath, dbFile);
#else
        var dbPath = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, dbFile);
#endif
#endif

        DB = new Database(dbPath);

        // The root page of your application
        MainPage = new SongsPage();
    }
}
  1. Roep nu eenvoudig het DB object op via de App klasse wanneer u een CRUD-bewerking naar de tabel Songs wilt uitvoeren. Als u bijvoorbeeld een nieuw Song wilt invoegen nadat de gebruiker op een knop heeft geklikt, kunt u de volgende code gebruiken:
void AddNewSongButton_Click(object sender, EventArgs a)
{
    Song s = new Song();
    s.ID = Guid.NewGuid().ToString();
    s.SongName = songNameEntry.Text;
    s.SingerName = singerNameEntry.Text;

    App.DB.AddSong(song);
}

Werken met lokale databases met xamarin.forms in visual studio 2015

SQlite-voorbeeld Stap voor stap Verklaring

  1. De onderstaande stappen laten zien hoe u dit onderdeel kunt opnemen in een gedeeld Xamarin.Forms-project: pakketten toevoegen in (pcl, Andriod, Windows, Ios) Referenties toevoegen Klik op Beheer Nuget-pakketten -> klik op Bladeren om SQLite.Net.Core te installeren- PCL , SQLite Net Extensions nadat de installatie is voltooid, controleer het eenmaal in de referenties dan

  2. Om Class Employee.cs onder de code toe te voegen

     using SQLite.Net.Attributes;
    
         namespace DatabaseEmployeeCreation.SqlLite
         {
             public   class Employee
             {
                 [PrimaryKey,AutoIncrement]
                 public int Eid { get; set; }
                 public string Ename { get; set; }
                 public string Address { get; set; }
                 public string phonenumber { get; set; }
                 public string email { get; set; } 
             }
         }
    
  3. Om één interface ISQLite toe te voegen

 using SQLite.Net;  
            //using SQLite.Net;
            namespace DatabaseEmployeeCreation.SqlLite.ViewModel
            {
                public interface ISQLite
                {
                    SQLiteConnection GetConnection();
                }
            }
  1. Maak een klasse voor database-logica en methoden hieronder code is volgen.

met behulp van SQLite.Net; met behulp van System.Collections.Generic; met behulp van System.Linq; Xamarin.Forms gebruiken; namespace DatabaseEmployeeCreation.SqlLite.ViewModel {public class DatabaseLogic {static object locker = new object (); SQLiteConnection-database;

    public DatabaseLogic()
    {
        database = DependencyService.Get<ISQLite>().GetConnection();
        // create the tables
        database.CreateTable<Employee>();
    }

    public IEnumerable<Employee> GetItems()
    {
        lock (locker)
        {
            return (from i in database.Table<Employee>() select i).ToList();
        }
    }

    public IEnumerable<Employee> GetItemsNotDone()
    {
        lock (locker)
        {
            return database.Query<Employee>("SELECT * FROM [Employee]");
        }
    }

    public Employee GetItem(int id)
    {
        lock (locker)
        {
            return database.Table<Employee>().FirstOrDefault(x => x.Eid == id);
        }
    }

    public int SaveItem(Employee item)
    {
        lock (locker)
        {
            if (item.Eid != 0)
            {
                database.Update(item);
                return item.Eid;
            }
            else
            {
                return database.Insert(item);
            }
        }
    }

    public int DeleteItem(int Eid)
    {
        lock (locker)
        {
            return database.Delete<Employee>(Eid);
        }
    }
}

}

  1. om een xaml.forms EmployeeRegistration.xaml te maken
    <?xml version="1.0" encoding="utf-8" ?>
    <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
                 xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
                 x:Class="DatabaseEmployeeCreation.SqlLite.EmployeeRegistration"
      Title="{Binding Name}" >
      <StackLayout VerticalOptions="StartAndExpand" Padding="20">
    
        <Label Text="Ename" />
        <Entry x:Name="nameEntry" Text="{Binding Ename}"/>
        <Label Text="Address" />
        <Editor x:Name="AddressEntry" Text="{Binding Address}"/>
        <Label Text="phonenumber" />
        <Entry x:Name="phonenumberEntry" Text="{Binding phonenumber}"/>
        <Label Text="email" />
        <Entry x:Name="emailEntry" Text="{Binding email}"/>
    
        <Button Text="Add" Clicked="addClicked"/>
    
       <!-- <Button Text="Delete" Clicked="deleteClicked"/>-->
    
        <Button Text="Details" Clicked="DetailsClicked"/>
    
        <!--  <Button Text="Edit" Clicked="speakClicked"/>-->
    
      </StackLayout>
    </ContentPage>

EmployeeRegistration.cs

    using DatabaseEmployeeCreation.SqlLite.ViewModel;
    using DatabaseEmployeeCreation.SqlLite.Views;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    using Xamarin.Forms;
    
    namespace DatabaseEmployeeCreation.SqlLite
    {
        public partial class EmployeeRegistration : ContentPage
        {
            private int empid;
            private Employee obj;
    
            public EmployeeRegistration()
            {
                InitializeComponent();
    
            }
    
            public EmployeeRegistration(Employee obj)
            {
                this.obj = obj;
                var eid = obj.Eid;
                Navigation.PushModalAsync(new EmployeeRegistration());
                var Address = obj.Address;
                var email = obj.email;
                var Ename = obj.Ename;
                var phonenumber = obj.phonenumber;
                AddressEntry. = Address;
                emailEntry.Text = email;
                nameEntry.Text = Ename;
    
                //AddressEntry.Text = obj.Address;
                //emailEntry.Text = obj.email;
                //nameEntry.Text = obj.Ename;
                //phonenumberEntry.Text = obj.phonenumber;
    
                Employee empupdate = new Employee(); //updateing Values 
                empupdate.Address = AddressEntry.Text;
                empupdate.Ename = nameEntry.Text; 
                empupdate.email = emailEntry.Text;
                empupdate.Eid = obj.Eid;
                App.Database.SaveItem(empupdate);
                Navigation.PushModalAsync(new EmployeeRegistration());
              
            }
    
            public EmployeeRegistration(int empid)
            {
                this.empid = empid;
                Employee lst = App.Database.GetItem(empid);
                //var Address = lst.Address;
                //var email = lst.email;
                //var Ename = lst.Ename;
                //var phonenumber = lst.phonenumber;
                //AddressEntry.Text = Address;
                //emailEntry.Text = email;
                //nameEntry.Text = Ename;
                //phonenumberEntry.Text = phonenumber;
    
                // to retriva values based on id to 
                AddressEntry.Text = lst.Address;
                emailEntry.Text = lst.email;
                nameEntry.Text = lst.Ename;
                phonenumberEntry.Text = lst.phonenumber;
                
                Employee empupdate = new Employee(); //updateing Values 
                empupdate.Address = AddressEntry.Text;
                empupdate.email = emailEntry.Text;
                App.Database.SaveItem(empupdate);
                Navigation.PushModalAsync(new EmployeeRegistration());
            }
    
            void addClicked(object sender, EventArgs e)
            {
                //var createEmp = (Employee)BindingContext;
                Employee emp = new Employee();
                emp.Address = AddressEntry.Text;
                emp.email = emailEntry.Text;
                emp.Ename = nameEntry.Text;
                emp.phonenumber = phonenumberEntry.Text;
                App.Database.SaveItem(emp);
                this.Navigation.PushAsync(new EmployeeDetails());
    
            }
            //void deleteClicked(object sender, EventArgs e)
            //{
            //    var emp = (Employee)BindingContext;
            //    App.Database.DeleteItem(emp.Eid);
            //    this.Navigation.PopAsync();
            //}
            void DetailsClicked(object sender, EventArgs e)
            {
                var empcancel = (Employee)BindingContext;
                this.Navigation.PushAsync(new EmployeeDetails());
            }
            //    void speakClicked(object sender, EventArgs e)
            //    {
            //        var empspek = (Employee)BindingContext;
            //        //DependencyService.Get<ITextSpeak>().Speak(empspek.Address + " " + empspek.Ename);
            //    }
        }
    }
  1. om EmployeeDetails onder de code erachter weer te geven

     using DatabaseEmployeeCreation;
     using DatabaseEmployeeCreation.SqlLite;
     using System;
     using System.Collections.Generic;
     using System.Linq;
     using System.Text;
     using System.Threading.Tasks;
     
     using Xamarin.Forms;
     
     namespace DatabaseEmployeeCreation.SqlLite.Views
     {
         public partial class EmployeeDetails : ContentPage
         {
             ListView lv = new ListView();
             IEnumerable<Employee> lst;
             public EmployeeDetails()
             {
                 InitializeComponent();
                 displayemployee();
             }
     
             private void displayemployee()
             {
                 Button btn = new Button()
                 {
     
                     Text = "Details",
                     BackgroundColor = Color.Blue,
                 };
                 btn.Clicked += Btn_Clicked;
                 //IEnumerable<Employee> lst = App.Database.GetItems();
                 //IEnumerable<Employee> lst1 = App.Database.GetItemsNotDone();
                 //IEnumerable<Employee> lst2 = App.Database.GetItemsNotDone();
                 Content = new StackLayout()
                 {
                     Children = { btn },
                 };
             }
     
             private void Btn_Clicked(object sender, EventArgs e)
             {
                 lst = App.Database.GetItems();
     
                 lv.ItemsSource = lst;
                 lv.HasUnevenRows = true;
                 lv.ItemTemplate = new DataTemplate(typeof(OptionsViewCell));
     
                 Content = new StackLayout()
                 {
                     Children = { lv },
                 };
     
             }
         }
    
        public class OptionsViewCell : ViewCell
        {
    
            int empid;
            Button btnEdit;
            public OptionsViewCell()
            {
            }
            protected override void OnBindingContextChanged()
            {
                base.OnBindingContextChanged();
    
                if (this.BindingContext == null)
                    return;
    
                dynamic obj = BindingContext;
                empid = Convert.ToInt32(obj.Eid);
                var lblname = new Label
                {
                    BackgroundColor = Color.Lime,
                    Text = obj.Ename,
                };
    
                var lblAddress = new Label
                {
                    BackgroundColor = Color.Yellow,
                    Text = obj.Address,
                };
    
                var lblphonenumber = new Label
                {
                    BackgroundColor = Color.Pink,
                    Text = obj.phonenumber,
                };
    
                var lblemail = new Label
                {
                    BackgroundColor = Color.Purple,
                    Text = obj.email,
                };
    
                var lbleid = new Label
                {
                    BackgroundColor = Color.Silver,
                    Text = (empid).ToString(),
                };
    
                //var lblname = new Label
                //{
                //    BackgroundColor = Color.Lime,
                //    // HorizontalOptions = LayoutOptions.Start
                //};
                //lblname.SetBinding(Label.TextProperty, "Ename");
    
                //var lblAddress = new Label
                //{
                //    BackgroundColor = Color.Yellow,
                //    //HorizontalOptions = LayoutOptions.Center,
                //};
                //lblAddress.SetBinding(Label.TextProperty, "Address");
    
                //var lblphonenumber = new Label
                //{
                //    BackgroundColor = Color.Pink,
                //    //HorizontalOptions = LayoutOptions.CenterAndExpand,
                //};
                //lblphonenumber.SetBinding(Label.TextProperty, "phonenumber");
    
                //var lblemail = new Label
                //{
                //    BackgroundColor = Color.Purple,
                //    // HorizontalOptions = LayoutOptions.CenterAndExpand
                //};
                //lblemail.SetBinding(Label.TextProperty, "email");
                //var lbleid = new Label
                //{
                //    BackgroundColor = Color.Silver,
                //    // HorizontalOptions = LayoutOptions.CenterAndExpand
                //};
                //lbleid.SetBinding(Label.TextProperty, "Eid");
                Button btnDelete = new Button
                {
                    BackgroundColor = Color.Gray,
    
                    Text = "Delete",
                    //WidthRequest = 15,
                    //HeightRequest = 20,
                    TextColor = Color.Red,
                    HorizontalOptions = LayoutOptions.EndAndExpand,
                };
                btnDelete.Clicked += BtnDelete_Clicked;
                //btnDelete.PropertyChanged += BtnDelete_PropertyChanged;  
    
                btnEdit = new Button
                {
                    BackgroundColor = Color.Gray,
                    Text = "Edit",
                    TextColor = Color.Green,
                };
                // lbleid.SetBinding(Label.TextProperty, "Eid");
                btnEdit.Clicked += BtnEdit_Clicked1; ;
                //btnEdit.Clicked += async (s, e) =>{
                //    await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration());
                //};
    
                View = new StackLayout()
                {
                    Orientation = StackOrientation.Horizontal,
                    BackgroundColor = Color.White,
                    Children = { lbleid, lblname, lblAddress, lblemail, lblphonenumber, btnDelete, btnEdit },
                };
    
                //View = new StackLayout()
                //{ HorizontalOptions = LayoutOptions.Center, WidthRequest = 10, BackgroundColor = Color.Yellow, Children = { lblAddress } };
    
                //View = new StackLayout()
                //{ HorizontalOptions = LayoutOptions.End, WidthRequest = 30, BackgroundColor = Color.Yellow, Children = { lblemail } };
    
                //View = new StackLayout()
                //{ HorizontalOptions = LayoutOptions.End, BackgroundColor = Color.Green, Children = { lblphonenumber } };
    
    
    
    
                //string Empid =c.eid ;
    
            }
    
            private async void BtnEdit_Clicked1(object sender, EventArgs e)
            {
               Employee obj= App.Database.GetItem(empid);
                if (empid > 0)
                {
                    await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration(obj));
                }
                else {
                await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration(empid));
                }
            }
    
    
    
            private void BtnDelete_Clicked(object sender, EventArgs e)
            {
                // var eid = Convert.ToInt32(empid);
                // var item = (Xamarin.Forms.Button)sender;
                int eid = empid;
                App.Database.DeleteItem(eid);
            }
            //private void BtnDelete_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
            //{
            // var ename=  e.PropertyName;
            //}
        }
    
        //private void BtnDelete_Clicked(object sender, EventArgs e)
        //{
        //    var eid = 8;
        //    var item = (Xamarin.Forms.Button)sender;
    
        //    App.Database.DeleteItem(eid);
        //}
    }
  1. De methode implementeren in de GetConnection () -methode voor Android en ios
    using System;
    using Xamarin.Forms;
    using System.IO;
    using DatabaseEmployeeCreation.Droid;
    using DatabaseEmployeeCreation.SqlLite.ViewModel;
    using SQLite;
    using SQLite.Net;
    
    [assembly: Dependency(typeof(SQLiteEmployee_Andriod))]
    namespace DatabaseEmployeeCreation.Droid
    {
        public class SQLiteEmployee_Andriod : ISQLite
        {
            public SQLiteEmployee_Andriod()
            {
            }
    
            #region ISQLite implementation
            public SQLiteConnection GetConnection()
            {
                //var sqliteFilename = "EmployeeSQLite.db3";
                //string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
                //var path = Path.Combine(documentsPath, sqliteFilename);
    
                //// This is where we copy in the prepopulated database
                //Console.WriteLine(path);
                //if (!File.Exists(path))
                //{
                //    var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.EmployeeSQLite);  // RESOURCE NAME ###
    
                //    // create a write stream
                //    FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
                //    // write to the stream
                //    ReadWriteStream(s, writeStream);
                //}
    
                //var conn = new SQLiteConnection(path);
    
                //// Return the database connection 
                //return conn;
                var filename = "DatabaseEmployeeCreationSQLite.db3";
                var documentspath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
                var path = Path.Combine(documentspath, filename);
                var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
                var connection = new SQLite.Net.SQLiteConnection(platform, path);
                return connection;
            }
    
            //public  SQLiteConnection GetConnection()
            //{
            //    var filename = "EmployeeSQLite.db3";
            //    var documentspath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            //    var path = Path.Combine(documentspath, filename);
    
            //    var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
            //    var connection = new SQLite.Net.SQLiteConnection(platform, path);
            //    return connection;
            //}
            #endregion
    
            /// <summary>
            /// helper method to get the database out of /raw/ and into the user filesystem
            /// </summary>
            void ReadWriteStream(Stream readStream, Stream writeStream)
            {
                int Length = 256;
                Byte[] buffer = new Byte[Length];
                int bytesRead = readStream.Read(buffer, 0, Length);
                // write the required bytes
                while (bytesRead > 0)
                {
                    writeStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }
                readStream.Close();
                writeStream.Close();
            }
        }
    }

Ik hoop dat dit bovenstaande voorbeeld heel gemakkelijk is, legde ik uit



Modified text is an extract of the original Stack Overflow Documentation
Licentie onder CC BY-SA 3.0
Niet aangesloten bij Stack Overflow