SQLite Database in Windows phone app – Part 1 (Installation and Setup)

While developing applications there may be a need to store data and access it for later use. Windows Phone provides us with different ways to store our application data depending on the size. But when dealing with large data involving multiple entries, database is the appropriate method.

 

Windows phone 8.1 Runtime app supports SQLite database. So in this article I will explain you how to install and setup SQLite database reference in your windows phone application.

Follow the steps given below

 

Step 1:
Create a new Project having the name SQLiteDatabase.

 


 

Step 2:
Install the SQLite for Windows phone 8.1 SDK

Go to Tools->Extensions and Updates

 


 

Then type the keyword sqlite in search bar in the online tab. And install the SDK SQLite for Windows Phone 8.1

 


 

Note: If you don’t find the required library in Visual Studio gallery you can download it from the required web page.

 

Step 3:
Add the SQLite Reference to your Project

 


 

Go to References->Windows Phone 8.1->Extensions

 


 

When done, you will see that the proper references to SQLite and Visual C++ 2013 Runtime have been added to your project

 


 

Step 4:
Change the target Platform of your Project

You may have already noticed, that the references show a warning symbols, to resolve that the next thing to do, before compiling the solution is changing the architecture of the target platform. This is the fact that the engine of Sqlite is written in C ++, and the default target platform set in the project is Any CPU. This mode is not supported.

So in order to resolve this go to Configuration Manager as shown in the figure below

 


 

In the next dialog, we note that we have several choices of platforms, Any CPU (the default), ARM, x64 and x86.

Now we change the target platform accordingly. If you want to debug your app on Phone then choose the ARM Platform. But if you wish to debug your app on Emulator or Windows PC select x86 or x64 configuration depending on your processor if it is 32 bits or 64 bits.

 


 

Note: Every time you change your debugging option from emulator to phone you need to change the debug configuration accordingly.

 

Step 5:
Installing sqlite-net package

After installing the library for SQLite, we need sqlite-net 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. This package provides two helper classes (SQLite.cs and SQLiteAsync.cs).

To install it right click on the project->Manage NuGet Packages and search for sqlite-net

 


 

Finally we have completed the SQLite setup. In the next blog post I will be explaining you how to perform different operations like insertion, deletion and searching in SQLite database.

 

https://windowsapptutorials.com/windows-phone/sqlite-database-in-windows-phone-app-part-2-sqlite-operations

Working with local database in Windows Phone- Part I

In my last post I gave an introduction to local databases in Windows Phone. You can have a look at it again to refresh things.

http://www.windowsapptutorials.com/windows-phone/working-with-local-databases-in-windows-phone-8/

I will start from where I left in the last post so do have a look.

Creating a database

Now that you have added reference to System.Data.Linq, you can create a local database in your app. The tables of the database can be created anywhere as long as it is accessible globally in the app. Still I would recommend that make a separate class DB.cs for your tables.

Step 1. Add a new class DB.cs

Right click on your project in solution explorer and choose to add a new item. Choose a class in the dialog box that opens up and name it DB.cs


Step 2. Add the namespace System.Data.Linq.Mapping

Remove the class DB that was created in the file DB.cs when you added the class. Also add the following reference in your file.

using System.Data.Linq.Mapping;


Step 3. Creating a table

Now our first task is to create a database which is used locally isolated to the application. Since we are going to use the LINQ to SQL data context as a bridge between the local database and the data context we need to create 2 classes as shown below. The screen shot also shows a snapshot of a table whose code is given below. Add this table in DB.cs.




Note the following

  • The table name is User_details which is just below the declaration for the table. A table can be visualized as a class with attributes which can be imagined as data members.
  • The columns are added in a similar fashion as data members are added in a class. The data type of the column is defined along with the column name.
  • Notice that column ID is defined as the primary key for the table.
  • The table has three columns, ID, user_name and user_email

Next you need to define the DataContext for User_details which is basically used as the database schema to create an instance, copy the code from the below code block as shown in the screen below.

Note the DBConnectionString is used to identify the database in the isolated storage. An app could contain multiple databases and you should be able to identify each of them uniquely so each one is given a unique name. Here we will be using a single database so you don’t need to worry about the naming.

In our app the table User_details will be referred by the name Users. For each table of the database you need to create its data context. Here’s a screen shot from another app showing how the data context was defined for its database which contains multiple tables.



Step 4. Add the code to create database if it doesn’t exist when the app is opened

Below InitializeComponent in Mainpage.xaml add the following code which checks if a database exists and creates one if doesn’t.



Adding records to the database

Now that you have added a table in your database and set up the DataContext for connecting to that database, you can now add records to your table. Again it would be convenient for you if you create a separate class DatabaseAdd.cs for adding any record to your database. Here’s how you could add records to a table

Step 1. Add a new class DatabaseAdd.cs in your project

We will create a new class DatabaseAdd.cs for adding any record to our database

Step 2. Create a function AddUser in DatabaseAdd.cs

Our function AddUser takes name and email id as parameters and adds a record to User_details table. It uses the UserDataContext to connect to the database and creates a new instance of User_details class (table) and lastly inserts the record.

Note the following

  • UserDataContext is used to establish a connection with the database
  • A new instance of User_details is created
  • The data members are initialized with the values to be inserted
  • InsertOnSubmit inserts the record to the table
  • SubmitChanges commits the changes to the database

Here’s the code for AddUser function

Now that we have created a function to add records to the table, let us make a page in our app where one could enter the details and insert it to the database.

Step 3. Add a new page AddUsers.xaml and create a form to insert users in the database

Go to solution explorer and add a new page to your project and name is AddUsers.xaml. Create a form with two textboxes and a button as shown below.


Step 4. Add the button click event handler which when clicked will insert records to the table

Add a Click event handler for the button. The event handler takes the text from the two textboxes and checks if they are empty. If the entered name and email are not empty then it calls the AddUser function by creating an object of DatabaseAdd class.



Fetching records from database

Now that we have inserted records to our table, we would like to fetch records from it. Again I would like you to create a separate class FetchDatabase.cs to fetch records from any table of our database. Here’s how could fetch records from a table

Step 1. Add a new class FetchDatabase.cs to fetch records from any table of the database

Add a new class to your project and name is FetchDatabase.cs to your project

Step 2. Add a public function GetAllUsers which returns a list of records from a table

The function GetAllUsers returns a IList which contains all the records fetched from the database. We have set up the connection using the data context and then queried records from the User_details table. The query returns a list of records which is saved to a IList list. IList is nothing but a collection of objects of same type.



Step 3. Create a class Users to hold the details

We define a class with data-members id, name and email. It will be used to hold the details from the fetched records.

Step 4. Create a function getUsers to fetch and return a list of Users

  • GetAllUsers function returned a list of records. We call GetAllUsers and keep the fetched records in a Ilist usrs.
  • We also create a new list allmsgs to hold the details of all users
  • We then iterate through all the usrs and put the details of each user to a new instance of Users class. We then add this instance to our list allmsgs
  • Lastly we return the list

Now that the function to fetch records is ready, let us create a new page to display records from the database.

Step 5. Add a new page AllUsers.xaml and create a Listbox to display records from the table

Go to solution explorer and a new page AllUsers.xaml. Add a ListBox to this page and create its data template. The data template contains two TextBlock with name and email binded to it. These TextBlock are contained in a StackPanel and its tag property is binded to the id. We won’t be needing this tag now but when we need to manipulate the selected record, the id binded to the tag will let us identify the record.

Step 6. Add the code to fetch records and bind it to the listbox

In the constructor of the page after call to InitializeComponent create an instance of FetchDatabase and call the getUsers function as shown below



Now let us run the project and see if everything works perfectly.

UPDATE: Here’s the link to other posts in this series

Get the full source code here,

Download full project Databases.zip