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.  

Up Next
    Ebook Download
    View all
    Learn
    View all