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.
Data Binding concept, Basic SQL query knowledge.
If you’re not familiar with Data Binding concept please check out our previous article
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.
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.
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
Once the SDK is installed, add it as your project reference and the SQLitePCL nuget package also.
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.
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.
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.
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.
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. 😉
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
ListView uses the
StudentDataTemplate which is defined as
Resources at the top of the page.
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.