How to use SQLite in Xamarin.Forms applications?

SQLite today is a great solution for data storage application. It works quickly and allows you to store large amounts of data.

To use SQLite, create a new project Xamarin.Forms. Then add nuget library in all projects:

%d1%81%d0%bd%d0%b8%d0%bc%d0%be%d0%ba-%d1%8d%d0%ba%d1%80%d0%b0%d0%bd%d0%b0-2016-09-21-%d0%b2-13-57-43

Then you need to write a connector for connection to the local database. Lets create an interface:

 public interface ISQLiteConnector
 {
    SQLiteConnection GetConnection();
 }

Create a file of constants in the overall project PCL:

  public static class Constants
  {
     public const string csDataBaseName = "SampleSQLite.db3";
  }

Further, in each of the projects under the platform we should create the implementation of this interface.

For Android implementation is as follows:

[assembly: Dependency(typeof(SQLiteConnector))]
 
 namespace SampleSQLite.Droid
 {
   public class SQLiteConnector:ISQLiteConnector
   {
       public SQLiteConnector()
       {
       }
       public SQLiteConnection GetConnection()
       {
           try
           {
               string documentsPath = System.Environment
               .GetFolderPath(System.Environment.SpecialFolder.Personal);
               var path = 
               Path.Combine(documentsPath, Constants.csDataBaseName);
               var isShouldGenerateTables = false;
               if (!System.IO.File.Exists(path))
                   isShouldGenerateTables = true;
               var conn = new SQLite.SQLiteConnection(path);
 
               if (isShouldGenerateTables)
                   conn.CreateTable<InfoItem>();
               return conn;
           }
           catch (Exception ex)
           {
              Console.WriteLine(ex);
           }
           finally
           {
           }
           return null;
       }
   }
}

For iOS implementation is as follows:

 [assembly: Dependency(typeof(SQLiteConnector))]
 
 namespace SampleSQLite.iOS
 {
     public class SQLiteConnector:ISQLiteConnector
     {
         public SQLiteConnector()
         {
         }
 
         public SQLiteConnection GetConnection()
         {
             try
             {
                 string documentsPath = 
                        Environment.GetFolderPath(Environment
                            .SpecialFolder.Personal);
                 string libraryPath = 
                 Path.Combine(documentsPath, "..", "Library"); 
                 var path = 
                 Path.Combine(libraryPath, Constants.csDataBaseName);
 
                 var isShouldGenerateTables = false;
                 if (!System.IO.File.Exists(path))
                     isShouldGenerateTables = true;
                 
                 var conn = new SQLite.SQLiteConnection(path);
 
                 if (isShouldGenerateTables)
                     conn.CreateTable<InfoItem>();
 
                 return conn;
             }
             catch (Exception ex)
             {
                 Console.WriteLine(ex);
             }
             finally
             {
             }
             return null;
         }
     }
 }

Then write a simple page, where we will use our SQLite-layer. On the page we should implement a read,write and delete operations with the database. For markup will use XAML page. MVVM pattern will not be used, and we will write all the logic in the .cs-file. Source code of the page:

 <ContentPage 
xmlns="http://xamarin.com/schemas/2014/forms" 
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" 
xmlns:local="clr-namespace:SampleSQLite" 
x:Class="SampleSQLite.SampleSQLitePage">
     <Grid Padding="20">
         <Grid.RowDefinitions>
             <RowDefinition Height="Auto"/>
             <RowDefinition Height="Auto"/>
             <RowDefinition Height="Auto"/>
             <RowDefinition Height="*"/>
         </Grid.RowDefinitions>
 
         <Label Grid.Row="0" Text="CRUD operations with SQLite"/>
 
         <Button Grid.Row="1" Text="Create" 
                 Clicked="Handle_Clicked_Create"/>
         <StackLayout Orientation="Horizontal" Grid.Row="2" 
              IsVisible="false" 
              x:Name="ctrAddContainer">
             <Entry x:Name="ctrNewItem" 
                    Placeholder="Add new text..." />
             <Button Text="Save" Clicked="Handle_Clicked_Save"/>
         </StackLayout>
 
         <ListView Grid.Row="3" x:Name="ctrListView" 
             ItemTemplate="{StaticResource keyItemTemplate}"/>
 
     </Grid>
 
     <ContentPage.Resources>
         <ResourceDictionary>
             <DataTemplate x:Key="keyItemTemplate">
                 <ViewCell>
                     <StackLayout Orientation="Horizontal">
                         <Label Text="{Binding Name}" 
                                VerticalOptions="Center"/>
                         <Button Text="Delete" 
                                Command="{Binding DeleteCommand}"/>
                     </StackLayout>
                 </ViewCell>
             </DataTemplate>
         </ResourceDictionary>
     </ContentPage.Resources>
 </ContentPage>

Source code-behind file:

 public partial class SampleSQLitePage : ContentPage
     {
         SQLiteConnection _connection;
         public SampleSQLitePage()
         {
             try
             {
                 InitializeComponent();
 
                 _connection = DependencyService
                           .Get<ISQLiteConnector>().GetConnection();
                 UpdateSource();
             }
             catch (System.Exception ex)
             {
             }
             finally
             {
             }
         }
 
         void UpdateSource()
         {
             var items = _connection.Table<InfoItem>()
                                    .AsEnumerable()
                                    .ToList();
             foreach (var item in items)
             {
                 item.Deleted += Item_Deleted;
             }
             ctrListView.ItemsSource = items;
         }
 
         void Handle_Clicked_Create(object sender, System.EventArgs e)
         {
             ctrAddContainer.IsVisible = !ctrAddContainer.IsVisible;
         }
 
         void Handle_Clicked_Save(object sender, System.EventArgs e)
         {
             _connection.Insert(new InfoItem() 
             { 
                 Name = ctrNewItem.Text 
             });
             ctrNewItem.Text = null;
             ctrAddContainer.IsVisible = false;
             UpdateSource();
         }
 
         void Item_Deleted(object sender, System.EventArgs e)
         {
             UpdateSource();
         }
     } 

As the data model we will create a class InfoItem. It will be stored in the appropriate table. The table is created the first time an SQLite-layer was requested after installation. When the app restarts the table is not recreated. Source Code of Data Model:

     [Table("Info")]
     public class InfoItem:INotifyPropertyChanged
     {
         public event EventHandler Deleted;
 
         public InfoItem()
         {
             DeleteCommand = new Command(()=>{
                 DependencyService.Get<ISQLiteConnector>()
                 .GetConnection().Delete(this);
                 Deleted?.Invoke(this, null);
             });
         }
 
         private int _id;
         [PrimaryKey, AutoIncrement]
         public int Id
         {
             get
             {
                 return _id;
             }
             set
             {
                 this._id = value;
                 OnPropertyChanged(nameof(Id));
             }
         }
 
         private string _name;
         [NotNull]
         public string Name
         {
             get
             {
                 return _name;
             }
             set
             {
                 this._name = value;
                 OnPropertyChanged(nameof(Name));
             }
         }
 
         [Ignore]
         public ICommand DeleteCommand { get; private set;}
 
         public event PropertyChangedEventHandler PropertyChanged;
 
         private void OnPropertyChanged(string propertyName)
         {
             this.PropertyChanged?.Invoke(this,
                 new PropertyChangedEventArgs(propertyName));
         }
 
         public override string ToString()
         {
             return string.Format("[InfoItem: Id={0}, 
                    Name={1}]", Id, Name);
         }
     }

Source code is here

Video of the sample for iOS:

 

Video of the sample for Android:

 

Have a good time

1 Response

  1. Hello! Cool post, amazing!!!

Leave a comment