Windows 10 UWP SQLite: How to Store Data in DataBase for Beginners Tutorial(C#-XAML)

Introduction:

If you want to implement sqlite database application for WinRT windowsphone 8.1, you may helped from my previous article. Now from this article we will learn about 'Sqlite support in Windows 10 UWP'. This article is compared with my previous articles, so that we can easily understand the changes in WP8.0, WP8.1 & Windows 10 UWP to setup Sqlite environment.

Note: If you are new to Windows 10 UWP app development, please read Windows 10 Beginners Guide.

Requirements:

This article is targeted for windows 10 Anniversary Update, so make sure you’ve downloaded and installed the latest Anniversary Windows 10 SDK from here.
We no longer need a developer license with Windows 10. But we must Enable Developer Mode to your device for development.
This post assumes you’re using Microsoft Visual Studio 2015 Update 3 or Later.
This article is developed on windows 10 machine.

Description:

SQLite is a very light weight database. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations. So let's start with above hierarchy which is earlier discussed in "Table of Contents".
1. How to create new project With Windows 10 Anniversary SDK?

Before to use Windows 10 UWP Community Toolkit, first we need to create the new project.

Launch Visual Studio 2015 Update 3 or Later
On the File menu, select New > Project.
The New Project dialog appears. The left pane of the dialog lets you select the type of templates to display. In the left pane, expand Installed > Templates > Visual C# > Windows, then pick the Universal template group. The dialog's center pane displays a list of project templates for Universal Windows Platform (UWP) apps.
In the center pane, select the Blank App (Universal Windows) template. The Blank App template creates a minimal UWP app that compiles and runs but contains no user interface controls or data. You add controls to the app over the course of this tutorial. In the Name text box, type "SqliteUWP". Click OK to create the project.

Next dialog will ask for you to confirm that your app support min & target versions. For this sample, I target the app with minimum version 10.0.10240 like below:

2)How to setup SQLite environment?
Sqlite is directly not available in UWP, and don't worry now it is very easy to setup sqlite in Windows 10 UWP app. So we need to follow two steps only (i.e Installing SQLite for Universal Windows Platform SDK, Installing SQLite.Net-PCL package)

2.1)Installing SQLite for Universal Windows Platform SDK:

The first step is to install the SQLite for Universal Windows Platform SDK in the Visual Studio that you are using. To do that, click on TOOLS -> Extensions and Updates -> Online -> Then search for "SQLite for Universal Windows Platform". It should look like the following picture.

Click on Download and You can directly open it while downloading, authorize installation and click install when ready.

Open Solution explorer, right click on 'References' folder of your current project. Then add the reference to the "SQLite for Universal Windows Platform" library.

Also you need to add manually Visual C++ 2015 Runtime

2.2)Installing SQLite.Net-PCL package:

After installing the library for SQLite, we need SQLite.Net-PCL NuGet package. With this library, we will be able to perform all the operations that you normally do in a database, such as Insert, Delete, Update and run search queries. Sqlite-net also offers an approach typical ORM. So to install it right click on your project (i.e in my case project name is SqliteUWP) =>Click on "Manage NuGet Packages" and search for "SQLite.Net-PCL" => Click on "Install" button. After that we should found below dialog:

***Wow now whole SQLite set up process is completed, so now we are going to work with all CRUD operations***

3)How to perform all SQLite CRUD operations ?

Note: From this step on-wards, most of content will be same as my previous article. Please be note a few changes made in this post.

So its time to perform all SQLite CRUD(Create,Read,Update,Delete) operations. So my thought is in this sample i made one single "DatabaseHelperClass.cs" class for whole application and handling the SQLite operations with this helper class. Lets see first my table structure first

Here i am trying to create Table name is "Contacts" in "ContactsManager.sqlite" database. So my class "Contacts" with all getter and setter methods(Id,Name,PhoneNumber,CreatedDate) to maintain single contact as an object.

public class Contacts
{
//The Id property is marked as the Primary Key
[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }
public string PhoneNumber { get; set; }
public string CreationDate { get; set; }
public Contacts()
{
//empty constructor
}
public Contacts(string name, string phone_no)
{
Name = name;
PhoneNumber = phone_no;
CreationDate = DateTime.Now.ToString();
}
}

3.1)Writing DatabaseHelper Class to handle database operations(i.e CRUD)

class DatabaseHelperClass
{
//Create Tabble
public void CreateDatabase(string DB_PATH)
{
if (!CheckFileExists(DB_PATH).Result)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DB_PATH))
{
conn.CreateTable<Contacts>();
}
}
}
private async Task<bool> CheckFileExists(string fileName)
{
try
{
var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
return true;
}
catch
{
return false;
}
}
// Insert the new contact in the Contacts table.
public void Insert(Contacts objContact)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
conn.RunInTransaction(() =>
{
conn.Insert(objContact);
});
}
}
// Retrieve the specific contact from the database.
public Contacts ReadContact(int contactid)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query<Contacts>("select * from Contacts where Id =" + contactid).FirstOrDefault();
return existingconact;
}
}
public ObservableCollection<Contacts> ReadAllContacts()
{
try
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
List<Contacts> myCollection = conn.Table<Contacts>().ToList<Contacts>();
ObservableCollection<Contacts> ContactsList = new ObservableCollection<Contacts>(myCollection);
return ContactsList;
}
}
catch
{
return null;
}
}
//Update existing conatct
public void UpdateDetails(Contacts ObjContact)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query<Contacts>("select * from Contacts where Id =" + ObjContact.Id).FirstOrDefault();
if (existingconact != null)
{
conn.RunInTransaction(() =>
{
conn.Update(ObjContact);
});
}
}
}
//Delete all contactlist or delete Contacts table
public void DeleteAllContact()
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
conn.DropTable<Contacts>();
conn.CreateTable<Contacts>();
conn.Dispose();
conn.Close();
}
}
//Delete specific contact
public void DeleteContact(int Id)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query<Contacts>("select * from Contacts where Id =" + Id).FirstOrDefault();
if (existingconact != null)
{
conn.RunInTransaction(() =>
{
conn.Delete(existingconact);
});
}
}
}
}
}

Also I created, below another helper class name is "ReadAllContactsList" that can read all contacts from DB

public class ReadAllContactsList
{
DatabaseHelperClass Db_Helper = new DatabaseHelperClass();
public ObservableCollection<Contacts> GetAllContacts()
{
return Db_Helper.ReadAllContacts();
}
}

4)How to bind SQLite data to listbox?

In above step 3.1, I created one Database helper class name is "DatabaseHelperClass.cs" which is to perform all SQlite operations.

In the app.xaml class lets create a database. In the constructor we check if the database exists and if it does not we create it. Since if there is no file exists ,it will get an exception.

public static string DB_PATH = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "ContactsManager.sqlite"));//DataBase Name
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
if (!CheckFileExists("ContactsManager.sqlite").Result)
{
using (var db = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DB_PATH))
{
db.CreateTable<Contacts>();
}
}
}
private async Task<bool> CheckFileExists(string fileName)
{
try
{
var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
return true;
}
catch
{
}
return false;
}
}

Next I divided my project into MVVM pattern for simplicity. So in Model folder I placed table class name is "Contacts.cs". In ViewModels I placed DB helpers classes(DatabaseHelperClass.cs & ReadAllContactsList.cs). And finally in Views folder I placed All my three UI related pages

AddConatct.xaml:This page for adding contact to database, when click on "Add Contact" button it will be add contact in database like this

<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<StackPanel Grid.Row="0" Margin="12,17,0,28">
<TextBlock Text="Add Contact" Margin="9,-7,0,0"/>
</StackPanel>
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<StackPanel>
<TextBox Name="NametxtBx" Margin="10" Header="Name"/>
<TextBox InputScope="Digits" Name="PhonetxtBx" Margin="10" MaxLength="10" Header="PhoneNumber" />
<Button Content="Add Contact" Margin="10" HorizontalAlignment="Stretch" Click="AddContact_Click"/>
</StackPanel>
</Grid>
</Grid>

private async void AddContact_Click(object sender, RoutedEventArgs e)
{
DatabaseHelperClass Db_Helper = new DatabaseHelperClass();//Creating object for DatabaseHelperClass.cs from ViewModel/DatabaseHelperClass.cs
if (NametxtBx.Text != "" & PhonetxtBx.Text != "")
{
Db_Helper.Insert(new Contacts(NametxtBx.Text, PhonetxtBx.Text));
Frame.Navigate(typeof(HomePage));//after add contact redirect to contact listbox page
}
else
{
MessageDialog messageDialog = new MessageDialog("Please fill two fields");//Text should not be empty
await messageDialog.ShowAsync();
}
}

ReadContactList.xaml:This page for displaying all DB contact list with listbox.And in this screen there is two buttons(Add Contact & DeleteAll) for correspondingly add contact to DB / Delete entire table data.Firstly i made following listbox datatemplate for binding database contacts

<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<StackPanel Grid.Row="0" Margin="12,17,0,28">
<TextBlock Text="Read All contacts with listbox" TextWrapping="Wrap" Margin="9,-7,0,0" FontSize="28"/>
</StackPanel>
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<Grid.RowDefinitions>
<RowDefinition Height="auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<Grid Grid.Row="0">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<Button Grid.Column="0" Content="Add Contact" Margin="5" HorizontalAlignment="Stretch" Click="AddContact_Click"/>
<Button Grid.Column="1" Name="btnDelete" IsEnabled="False" Content="DeleteAll" Margin="5" HorizontalAlignment="Stretch" Click="DeleteAll_Click"/>
</Grid>
<ListView BorderBrush="#FF141EE4" Grid.Row="1" x:Name="listBoxobj" SelectionChanged="listBoxobj_SelectionChanged">
<ListView.ItemTemplate>
<DataTemplate>
<Grid>
<Border Margin="5" BorderBrush="White" BorderThickness="1">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>
<TextBlock Margin="5,0,0,0" Grid.Row="0" x:Name="NameTxt" TextWrapping="Wrap" Text="{Binding Name}" FontSize="28" Foreground="White"/>
<TextBlock Grid.Row="0" Text=">" FontSize="28" HorizontalAlignment="Right" VerticalAlignment="Center" Foreground="White"/>
<TextBlock Margin="5,0,0,0" Grid.Row="1" x:Name="PhoneTxt" TextWrapping="Wrap" Foreground="White" FontSize="18" Text="{Binding PhoneNumber}" />
<TextBlock HorizontalAlignment="Right" Margin="0,0,35,0" Grid.Row="3" x:Name="CreateddateTxt" Foreground="White" FontSize="18" TextWrapping="Wrap" Text="{Binding CreationDate}" />
</Grid>
</Border>
</Grid>
</DataTemplate>
</ListView.ItemTemplate>
<ListView.ItemContainerStyle>
<Style TargetType="ListViewItem">
<Setter Property="HorizontalContentAlignment" Value="Stretch" />
</Style>
</ListView.ItemContainerStyle>
</ListView>
</Grid>
</Grid>

So when page is loaded ,i done like this

private void ReadContactList_Loaded(object sender, RoutedEventArgs e)
{
ReadAllContactsList dbcontacts = new ReadAllContactsList();
DB_ContactList = dbcontacts.GetAllContacts();//Get all DB contacts
if (DB_ContactList.Count > 0)
{
btnDelete.IsEnabled = true;
}
listBoxobj.ItemsSource = DB_ContactList.OrderByDescending(i => i.Id).ToList();//Binding DB data to LISTBOX and Latest contact ID can Display first.
}

When "Add Contact" button is clicked is navigated to "AddConatct.xaml" page to add contact in DB.

private void AddContact_Click(object sender, RoutedEventArgs e)
{
Frame.Navigate(typeof(AddPage));
}

When "DeleteAll" button is clicked ,i done like this

private void DeleteAll_Click(object sender, RoutedEventArgs e)
{
DatabaseHelperClass delete = new DatabaseHelperClass();
delete.DeleteAllContact();//delete all DB contacts
DB_ContactList.Clear();//Clear collections
btnDelete.IsEnabled = false;
listBoxobj.ItemsSource = DB_ContactList;
}

When selected listbox item ,i navigate to "DetailsPage.xaml" page for delete/update corresponding contact details on listbox SelectionChanged event like this.

private void listBoxobj_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (listBoxobj.SelectedIndex != -1)
{
Contacts listitem = listBoxobj.SelectedItem as Contacts;//Get slected listbox item contact ID
Frame.Navigate(typeof(DetailsPage), listitem);
}
}

DetailsPage.xaml:This page is for updating/delete selected contact details

See in above when listbox item is selected ,i passed selected contact Id as query string,so in this i get that id in "OnNavigatedTo" method like this

protected override void OnNavigatedTo(NavigationEventArgs e)
{
currentStudent = e.Parameter as Contacts;
NametxtBx.Text = currentStudent.Name;//get contact Name
PhonetxtBx.Text = currentStudent.PhoneNumber;//get contact PhoneNumber
}

When "Delete" button is pressed i done like this.

private void DeleteContact_Click(object sender, RoutedEventArgs e)
{
Db_Helper.DeleteContact(currentStudent.Id);//Delete selected DB contact Id.
Frame.Navigate(typeof(HomePage));
}

When "Update" button is pressed i done like this.

private void UpdateContact_Click(object sender, RoutedEventArgs e)
{
currentStudent.Name = NametxtBx.Text;
currentStudent.PhoneNumber = PhonetxtBx.Text;
Db_Helper.UpdateDetails(currentStudent);//Update selected DB contact Id
Frame.Navigate(typeof(HomePage));
}

Important Note:

Please make sure to add reference of "Visual C++ 2015 Runtime" as discussed in step 2.1, otherwise you may get exception like "An exception of type "System.DllNotFoundException' occurred in SQLite.Net.Platform.WinRT.dll but not handled in user code".

Summary:

In this article, we have seen the basics of Sqlite, how to install the engine, the library Sqlite-net, and saw the most common operations (also known as code first), such as inserting, updating, and deleting data from a table of a database.

FeedBack Note: Please share your thoughts,what you think about this post,Is this post really helpful for you?otherwise it would be very happy ,if you have any thoughts for to implement this requirement in any another way?I always welcome if you drop comments on this post and it would be impressive.

Follow me always at @Subramanyam_B
Have a nice day by Subramanyam Raju :)