Local Database SQLite For Windows 10

Here's the step by step implementation.

Introduction

SQLite is a lightweight database used for mobile local storages.

Create New UWP project.

Setup SQLite environment.

Install SQLite-UAP extensions form NuGet Package Manager as in the following screen.

SQLite-UAP

Next Install SQLite.Net-PCL extension from NuGet Package

 Install SQLite

Now, we are going to the following areas:

  • How to perform SQLite CRUD operations.
  • How to bind SQLite data to a ListBox.

Design the UI as in the following screenshot:

Design

XAML Code

  1. <Grid Background="#FFF589E2">  
  2.     <Grid.ColumnDefinitions>  
  3.         <ColumnDefinition></ColumnDefinition>  
  4.     </Grid.ColumnDefinitions>  
  5.     <Grid.RowDefinitions>  
  6.         <RowDefinition Height="Auto"></RowDefinition>  
  7.         <RowDefinition Height="Auto"></RowDefinition>  
  8.         <RowDefinition Height="Auto"></RowDefinition>  
  9.         <RowDefinition Height="Auto"></RowDefinition>  
  10.         <RowDefinition Height="*"></RowDefinition>  
  11.     </Grid.RowDefinitions>  
  12.     <Button x:Name="CreateDBbutton" Grid.Row="0" Content="Create Local Database" HorizontalAlignment="Center" VerticalAlignment="Top" Click="button_Click" />  
  13.     <Button x:Name="create" Grid.Row="1" Content="Create New Students" HorizontalAlignment="Center" Click="create_Click"></Button>  
  14.     <Button x:Name="read" Grid.Row="2" Content="Read Students List" Width="300" Click="read_Click" HorizontalAlignment="Center"></Button>  
  15.     <Button x:Name="update" Grid.Row="3" Content="Update Details" Width="300" Click="update_Click" HorizontalAlignment="Stretch"></Button>  
  16.     <ListView x:Name="allstudents" HorizontalAlignment="Stretch" Grid.Row="4">  
  17.         <ListView.ItemTemplate>  
  18.             <DataTemplate>  
  19.                 <TextBlock x:Name="ee" Text="{Binding Name}" FontSize="14"></TextBlock>  
  20.             </DataTemplate>  
  21.         </ListView.ItemTemplate>  
  22.     </ListView>  
  23. </Grid>  
Now write the following code in the bcorresponding button click events.

I am going to create one Student DB with Students Table with id, Name, Address and Mobile. First design the table like the following:
  1. public class Students  
  2. {  
  3.     [SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]  
  4.     public int Id  
  5.     {  
  6.         get;  
  7.         set;  
  8.     }  
  9.     public string Name  
  10.     {  
  11.         get;  
  12.         set;  
  13.     }  
  14.     public string Address  
  15.     {  
  16.         get;  
  17.         set;  
  18.     }  
  19.     public string Mobile  
  20.     {  
  21.         get;  
  22.         set;  
  23.     }  
  24.     public Students()  
  25.     {}  
  26.     public Students(string name, string address, string mobile)  
  27.     {  
  28.         Name = name;  
  29.         Address = address;  
  30.         Mobile = mobile;  
  31.     }  
  32. }  
Create DB
  1. public static void CreateDatabase()  
  2. {  
  3.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  4.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  5.     {  
  6.         conn.CreateTable < Students > ();  
  7.     }  
  8. }  
Insert New Student details
  1. public void Insert(Students objContact)  
  2. {  
  3.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  4.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  5.     {  
  6.         conn.RunInTransaction(() =>  
  7.         {  
  8.             conn.Insert(objContact);  
  9.         });  
  10.     }  
  11. }  
Retrieve the specific contact from the database
  1. // Retrieve the specific contact from the database.  
  2. public Students ReadContact(int contactid)  
  3. {  
  4.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  5.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  6.     {  
  7.         var existingconact = conn.Query < Students > ("select * from Students where Id =" + contactid).FirstOrDefault();  
  8.         return existingconact;  
  9.     }  
  10. }  
Read All Student details
  1. //Read All Student details  
  2. public ObservableCollection < Students > ReadAllStudents()  
  3. {  
  4.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  5.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  6.     {  
  7.         List < Students > myCollection = conn.Table < Students > ().ToList < Students > ();  
  8.         ObservableCollection < Students > StudentsList = new ObservableCollection < Students > (myCollection);  
  9.         return StudentsList;  
  10.     }  
  11. }  
Update student details
  1. //Update student detaisl  
  2. public void UpdateDetails(string name)  
  3. {  
  4.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  5.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  6.     {  
  7.         var existingconact = conn.Query < Students > ("select * from Students where Name =" + name).FirstOrDefault();  
  8.         if (existingconact != null)  
  9.         {  
  10.             existingconact.Name = name;  
  11.             existingconact.Address = "NewAddress";  
  12.             existingconact.Mobile = "962623233";  
  13.             conn.RunInTransaction(() =>  
  14.             {  
  15.                 conn.Update(existingconact);  
  16.             });  
  17.         }  
  18.     }  
  19. }  
Delete all student or delete student table
  1. //Delete all student or delete student table  
  2. public void DeleteAllContact()  
  3. {  
  4.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  5.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  6.     {  
  7.         conn.DropTable < Students > ();  
  8.         conn.CreateTable < Students > ();  
  9.         conn.Dispose();  
  10.         conn.Close();  
  11.     }  
  12. }  
  13. Delete specific student  
  14. //Delete specific student  
  15. public void DeleteContact(int Id)  
  16. {  
  17.     var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");  
  18.     using(SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))  
  19.     {  
  20.         var existingconact = conn.Query < Students > ("select * from Studentdb where Id =" + Id).FirstOrDefault();  
  21.         if (existingconact != null)  
  22.         {  
  23.             conn.RunInTransaction(() =>  
  24.             {  
  25.                 conn.Delete(existingconact);  
  26.             });  
  27.         }  
  28.     }  
  29. }  
Now run the app with different devices and you will get the following output.

Here I have tested with Windows 10 Stimulator.

Windows 10 Stimulator

Source code.