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