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.