Local Database using SQLite

In this article I’m going to show how you can use SQLite as a Local database in windows phone 8.1 without the help of LINQ.

 

We have written some new posts for the SQLite Database. You can view them using the links given below. They provide a more easy and clear descripition on how to set up, install and use SQLite Database in your Windows app.

 

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

SQLite Database in Windows phone app – Part 2 (SQLite Operations)

SQLite Database in Windows phone app – Part 3 (SQLite Exploration)

 

Prerequisite

Data Binding concept, Basic SQL query knowledge.

If you’re not familiar with Data Binding concept please check out our previous article

How to bind ListBox to data in Windows phone application – Introduction

Here’s how you can build the app,

Step 1: Create an empty project

Create a Windows Phone 8.1 XAML application.

Before we can use SQLite in our project, there are some things that you need to have in your project reference.

References

SQLite SDK for Windows Phone 8.1

The SQLite SDK, wp8.1 version. You can download it from here. Then add a reference to the SQLite SDK in your app.

 

add_reference_to_sqllite_wp81

SQLitePCL

SQLitePCL nuget package. You need search for Portable Class Library for Sqlite in nuget search box.
How to integrate a Toolkit or Third party SDK with windows phone app using NuGet Package Manager

 

11

Once the SDK is installed, add it as your project reference and the SQLitePCL nuget package also.

Note

There are several nuget packages (wrapper class) available that can manipulate the SQLite database. One of the most common is sqlite-net. We need to know LINQ query to use that wrapper. But in this article we’re going use SQLitePCL. In another article I’ll show the use of sqlite-net.

 

The advantage of this wrapper is that we can write ‘traditional’ query to use the database. And disadvantage of this wrapper is that it has no official documentation. Basically you’ve use it on a ‘test & trial’ basis. But this wrapper has many functions available that is easy to code.

Note

Once you’re done with adding the reference, you might see a triangle yellow tiny button on the SQLite reference. Change the project configuration to either ‘x86’ or ‘ARM’. Alert should be gone.

 

Queries

To illustrate all the queries and functions, we’ll use a separate class for handling the database related executions.

So create a class Database_Controller. We’ll now write several static function for our project. The first function should be in our class to create the Database table. As you can see from the code that it’s pretty straight forward. You just need to create a connection and pass the query. Step() method basically executes the query.

Create

 


 

Insert

Another common query is to insert data into database. So the next function will does the job of adding data.

The only thing that I need to explain for this function is ‘Bind’. You can see that in query instead of real value I put ‘question mark’. This is how database system is kept protected from SQL Injection virus. Database can keep track of how many ‘?’ is put in the query. Then this mark are replaced the by the original value with help of Bind function. And the index for ‘?’ starts from 1.

Select

Now another common queries of database system is the SELECT query. There’s one thing new here. Let’s take a look.

 

After executing queries, all the results are stored in a class SQLiteResult. You need traverse to get the results. And SQLiteResult class don’t requires to be initialized. When traversing the pointer in the Result set. There remains two state of the class SQLiteResult. When it becomes SQLiteResult.DONE, it means that the pointer has traversed all its data. And when the state is SQLiteResult.ROW, it means the pointer is at the start of any row. I kept the return of this function as a list because I want to bind the data with the xaml. If you’re familiar with data binding you should know why I created Student class inspite of having student table in database. 😉

Delete/Update

Deleting a row and editing a row is pretty much similar in a sense that we need pass a unique attribute to delete or to edit. I’ll demonstrate only deleting part. You’ll learn only when you struggle. And do it efficiently (hint: use queryString concept to use a same page for two different purpose 😉 )

Though I did not make the ID attribute as primary key, I’m assuming it’s unique.

Now that we’re done with queries, we’ll put into our xaml pages.

Wait, before we put it into any xaml, we need to create the table first. The best way to create the table in on the launch time of the app. So we’ll put these two lines in the App.xaml.cs file.

 

Rest of the work is easy for you. Create ListView, Bind it to Student ObservableCollection. And see the result. For the demonstration, I implemented the delete function the ItemClick event.

 

The ListView uses the StudentDataTemplate which is defined as Resources at the top of the page.

abc

You should implement in your real project with a proper way. The code is behind the function is below.

 

 

DONE! Congratulations again for completing this task successfully. You can now make the most use of local database. I’d love to get your feedback on this. Download the full source code of this project and use it as an reference.

 

Download SqliteTutorial.zip

Working with local database in Windows Phone 8- Part III

This is the fourth post of the Windows Phone’s local database series and I will discuss how to move referential database to isolated storage. Here are the links to other posts in this series

In this post we will discuss how you could move a referential database from app’s local storage (resources) to isolated storage. It is not very commonly used but are very useful in some situations.

Moving a referential database from app’s local storage to isolated storage

This is particularly useful when one feels the need to preload the database with some data when the app is first launched rather than creating a new database and inserting values in it. When the amount of data you wish to preload is quite large then it is beneficial to put the database containing the preloaded data in app’s local storage (app resources) and then move it to the isolated storage when the app is launched. You would remember from previous tutorials that in the constructor of the default navigation page we have added a condition that checks whether a database exists. If it doesn’t then a new database is created. So instead of creating a new database we need to move the referential database. Here’s how it can be done.

Step 1. Run the app and load the database with desired data

Firstly, while debugging you need to run the app and load it with all the data you wish to preload. Here we will use the same sample application that we have been using in this series. Let us add a few users in the database. You could see that we have added three users in the database.

 

 

Step 2. Use ISETool to export the database to your computer

You need to copy the database from app’s isolated storage to your computer’s folder. Follow this article ti easily copy files using ISETool

Accessing the Isolated Storage Data of developer apps installed to emulator or unlocked Windows Phone device

Here’s what you need to do in command prompt. Two simple commands are all you require! You will get the files in the path you specified, here c:\data\myfiles

Note: If you are using a windows phone 8 emulator then the path should have v8.0 in it otherwise it should have v8.1 for windows phone 8.1 emulator. I was using an 8.1 emulator so I had to change the path from v8.0 to v8.1.

 

Step 3. Locate the database on your computer and import it in your project

As specified the files will be located inside c:\data\myfiles\IsolatedStorage folder.

Right click on project name in solution explorer to add an existing item.

 

In the dialog box that appears, navigate to the folder containing the copied database and add it to your project.

 

 

Step 4. Add the method MoveReferenceDatabase() to the MainPage.xaml.cs of your project.

This function copies the specified file from app resources to isolated storage. We will call this function instead of creating a new database when no existing database is found in our project. This function is executed only when the app is run for the first time or whenever no database is found.

Step 5. Replace the create database statement with a call to MoveReferenceDatabase()

In the constructor of MainPage.xaml.cs where we check for the existence of a database, replace the statement that creates a new database when none exists with a call to MoveReferenceDatabase(). This will copy the database from app’s resources to isolated storage.

 

Note:

  • The name of the database copied should match with name specified in the connection string variable of data context class.
  • The schema of the copied database should be exactly the same as specified in the app.
  • The copied database will contain all the preloaded data in it.
  • It should be used cautiously and care must be taken to ensure that any triggers or actions on database update is consistent with the copied database. For example, if the local data is synced with some server, then care must be taken to maintain consistency

You can get the source code from the link below.

Download full project Database-Part-3.zip

Working with local database in Windows Phone 8- Part II

This is the third post of the Windows Phone’s local database series and I will discuss updating and deleting data from a database. Here are the links to other posts in this series

Deleting data from a table

Follow these steps to delete data from a local database’s table

Step 1. Create a new class DatabaseDelete.cs

This class will contain all the methods needed in the project to delete records from any table. It is always beneficial to follow a modular approach keeping similar codes in same file.

 

 

Step 2. Add functions DeleteAllUsers and DeleteUser(string id)

We are going to create two separate functions for two different purposes. The function DeleteAllUsers() will delete all records from User_details table and DeleteUser function accepts a parameter id and deletes a single user whose id matches the parameter passed.

 

Note

  • The function DeleteAllUsers call DeleteAllOnSubmit to execute the delete operation. This function accepts a list of records and deletes all the records in that list

    If ever you need to delete duplicate records from a table then this article may prove to be useful. http://www.maskaravivek.com/blog/c/how-to-delete-duplicates-records-from-a-table-using-linq-in-c/

  • The function DeleteUser calls DeleteOnSubmit accepts a single record and deletes it.
  • In both case the changes are saved when SubmitChanges() function is called

We are done with the back end work. Let us work with the UI to use both these functions

Step 3. All a `Delete all users` button in MainPage.xaml to delete all records

<Button Content=”Delete all users” Height=”80″ Click=”Button_Click2″/>

 


 

Step 4. Add the click event handler for this button and put the code to delete all users

On the click event of this button we will insert the code to delete all users by calling DeleteAllUsers() function

 

Step 5. Modify the data template of listbox on AllUsers.xaml page to delete a single user

Now to delete a single user let us move over to AllUsers page. We will eidt the data template of the listbox to add a delete button to it. Notice the change in the XAML code where we have added a delete button and placed the id of that user in its Tag attribute.

 

 

Step 6. Add the click event handler code for this button

In the click event handler we first fetch the id from the tag element of the button which sent the click event and then call the code to delete the record from the table. Then we update the list to reflect the changes

 

The code is self-explanatory and it accomplishes the desired result.

 

Updating records in a table

Updating records is very similar to adding records to a table.

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

Continuing with our modular approach we create a new class for all database updates. Unlike delete operation there’s no function to update all records. You need to iterate manually through all records to do so.

 


 

Step 2. Add UpdateUsers function to this class

UpdateUsers function accepts id, name and email id and updates the row whose id matches in the database. It’s not necessary to update all columns in the table. You could update all columns or even just one.

Note

  • We first match the id passed in the parameter with the ID in the database
  • We take the first matched record in the entityToUpdate variable
  • We simply assign the new values and submit the changes to update the database
  • The database is not updated until we call the SubmitChanges() function

Now let us try updating all records. We will add an update function that will convert all the user_name values to lower case

Note

  • We store the fetched records in a IList
  • We then use a foreach iterator to go through all the records and update the user_name by replacing it with its lower case equivalent
  • Notice that we have used the SubmitChanges() after the foreach loop. This lets us submit all the changes at once

Now let us move over to the design part as the backend work is done.

Step 3. Add a button on MainPage.xaml to update all user names to its lower case equivalent

Again we update the MainPage.xaml to add one more button to it.

<Button Content=”Update all user names to lower case” Height=”80″ Click=”Button_Click3″/>

 

 

Step 4. Add the click event handler for the update button

The code behind is simple as you have already done all the hard work. Just call the UpdateUserToLower() function and you are done

 

Step 5. Move over to AllUsers.xaml and add an update button in its ListBox data template

The data template is already clumsily handled but that’s not a big concern right now. So go ahead and add an update button to its data template.

 

Step 6. Add a new page Update.xaml and put two text boxes in it for name and email id respectively and an Update button

We will be needing this page to update the record once it is selected. Add two text boxes and a button in it

 

 

Step 7. Add the LayoutRoot_Loaded event handler to get the id passed from AllUsers.xaml page

When an update button is clicked on AllUsers.xaml page we pass the id of the clicked button while navigating to Update.xaml. We use the navigation context to fetch the id when the layout root is loaded. We already have variables id, nam and email_id global to this page. So in the LayoutRoot_Loaded event handler just add the following line of code

id=NavigationContext.QueryString[“id”];

 

Step 8. Add the Button_Click event handler to call the UpdateUser function

On Update button click we update the user by calling the UpdateUser function which we created earlier in DatabaseUpdate.cs class


 

Step 9. Move back to AllUsers.xaml and add the code behind to handler the Update button click event

We just have to navigate to the Update.xaml page passing the id with the navigation Uri.


 

Now you are done with the code. Let run the app in the emulator to see if things work as intended.

 

I can’t show you all the screen shots but things do work. Try implementing it in your app and sound off below if you face any difficulties. In the next part of this series I will talk about updating database schema to add columns to existing tables, adding new tables or adding attributes to tables. Also we will see how one can move a referential database from app resources to isolated storage.

Get the source code here

Download full project Database-Part-2.zip

UPDATE: More posts have been added to this series. Here are the links to other posts in this series

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

Working with local databases in Windows Phone 8

In this article I am going to explain how you could use the Isolated Storage by creating a local referential database which can be accessed locally by that Windows Phone application. Database operations could be performed using LINQ to SQL or using SQLite. Usually developers prefer LINQ to SQL for DB operations but SQLite becomes a better option when you plan to make apps both for Windows Phone and Windows 8. Currently Windows 8 supports databases using SQLite only, so if you don’t wish to rewrite the code for both platforms you should prefer SQLite.

LINQ to SQL object model uses the System.Data.Linq.DataContext namespace to basically make a proxy call to the local database in the Isolated Storage container. LINQ to SQL Runtime plays as a bridge between the data context object and the real data to do the manipulations based on the user selection. Here are a few points which you should keep in mind while making databases for your app

  • Database file is stored in Isolated Storage Container
  • The database is available only for the application targeted as it is isolated from other apps
  • LINQ is used for queries
  • Local database feature can be accessed directly by adding System.Data.Linq assembly only since primary support is available with the framework.
  • Connection string much be used in the format of “Data Source =’isostore:/DirectoryName/Databasename.sdf”;

We are going to perform the following tasks to get a good grasp on using databases

  • Creating a local database
  • Adding data to the local database
  • Fetching data from local database
  • Deleting data from the local database
  • Deleting a local database

Before you create a local database, you need to create a new project and add the required references to it.

Step 1. Create a new project `Database’ targeted for Windows Phone 8

Open Visual Studio and create a black Silverlight Windows Phone app.

 


 

Step 2. Check and add a reference to System.Data.Linq to your project

Right Click on References in your project’s Solution Explorer and click on Add Reference and

 

Browse and choose System.Data.Linq and add it to your project. I am not sure if this reference would be already added. If you are using the latest VS(currently VS 13 update 1) then it may be already referenced in your project as it was in mine. Otherwise you need to add this reference.

 

 

You could right click on Windows Phone under References to open the object browser and check if a reference to System.Data.Linq already exists.

 

 

Now you are all set to create a local database in your app. In my next post on databases I will explain how you can create, add and fetch from a database. I will update this post with the links to those posts.

UPDATE: New articles in this series