Local Database SQLite For Windows 10

Introduction

 
SQLite is a lightweight database used for mobile local storages.
 
Here's the step by step implementation.
 
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 corresponding 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.
 

Summary

 
In this article, we learned about local database SQLite For Windows 10.