One of the topics that gathered more attention on my blog is SQLite: developing an application without the need to store, somehow, the data locally is almost impossible, so developers are always searching the best way to achieve this objective. SQLite is one of the most interesting solutions out there, since it’s open source and it’s available for almost every platform on the market (mobile, web, client, etc.).
I’ve already talked you about sqlite-net, a library that is available for Windows Phone 8 and Windows 8 that can be used to perform operations on a SQLite database using a high level API that support LINQ operations. This library is very simple to use, but it has some limitations: the biggest one is that doesn’t support relationships out of the box.
Now Peter Torr (a member of the Windows Phone development team in Microsoft) with the support of Andy Wigley (a former Windows Phone Development MVP that now has joined Microsoft) have released on Codeplex a new SQLite wrapper, that is totally different from sqlite-net and that satisfies another type of approach: total control. In fact, this new wrapper doesn’t support any LINQ operation but only manual SQL statement: the biggest pro is that you can perform any type of operation, even managing relationships. The biggest cons is that you’ll have to rely on old plain SQL queries to do any operation, even the simplest ones, like creating a table or inserting some data.
Let’s see, step by step, how to use it and how the approach is different from using sqlite-net. In this first post we’re going to see the basics, in the next one we’ll talk about relationships.
Configure the wrapper
For the moment, since it’s written in native code, the library isn’t available on NuGet: you’ll have to download the source code from the Codeplex page. The solution contains two projects: one is called SQLiteWinRT and the other one is called SQLiteWinRTPhone. The first one is for Windows Store apps, the second one for Windows Phone 8 apps (Windows Phone 7 is not supported, since it doesn’t support native code): you’ll have to add to your solution the project that fits your needs. For this tutorial I’m going to create a Windows Phone 8 application, so I’m going to use the second project. Plus, as we did for sqlite-net, we need to install the official SQLite runtime, that is available as a Visual Studio extension: here is the link for the Windows Store apps version, here, instead, is the link for the Windows Phone 8 version.
Now you’re ready to use it! We’re going to use the same sample we used in the sqlite-net post: a table to store data about people.
Create the database
Let’s see how to do it:
private async void OnCreateDatabaseClicked(object sender, RoutedEventArgs e)
{
Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");
await database.OpenAsync();
string query = "CREATE TABLE PEOPLE " +
"(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"Name varchar(100), " +
"Surname varchar(100))";
await database.ExecuteStatementAsync(query);
}
First we create a new Database object, that we’re going to use to make operations on the database. There are various ways to instantiate it: in this sample we’re going to create it by specifying the folder in the isolated storage where to save the file and the file name. The folder is passed to the constructor using a StorageFolder object, which is the Windows Runtime class that identifies a folder in the isolated storage. In the sample, we’re simply using the storage’s root. If the database doesn’t exist, it will be created; otherwise it will simply be opened.
Then we can open the connection, by calling the OpenAsync() method: it accepts, eventually, a parameter to set the opening mode (like read only). If we don’t set it, it will be used the default one, that supports read and write operations.
Then, we go manual! As already anticipated, the wrapper doesn’t support LINQ operations, so you’ll have to write the needed SQL statements to perform the operation. I assume that you already know the basics of SQL, so I won’t describe in details the queries: in this sample we define a query to create a table called People with 3 columns: Id (which is the primary key and it’s an auto increment value), Name and Surname (which simply contains strings).
To execute the query we call the async method ExecuteStatementAsync() on the Database object, passing as parameter the string with the query statement. ExecuteStatementAsync() is the method we need to use when the query doesn’t return any value and when we don’t need to define any parameter (we’ll see later how to use them).
As you can see, there’s a huge difference with the sqlite-net approach: there’s no mapping with a class and there’s no automatic conversion to a table. Everything is made with plain SQL statements.
Perform operations on the database
The approach to perform operations (insert, update, select, etc.) is the same we’ve seen: we open the connection, we define the query statement and we execute the query. The only difference is that when you do an insert, for example, usually you need to set some parameters, because some elements of the query are not fixed but dynamic. For example, if we want to add a new row in the People table we’ve created in the step before, we need to pass two dynamic values: Name and Surname.
Here is the code to perform this operation:
private async void OnAddDataClicked(object sender, RoutedEventArgs e)
{
Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");
await database.OpenAsync();
string query = "INSERT INTO PEOPLE (Name, Surname) VALUES (@name, @surname)";
Statement statement = await database.PrepareStatementAsync(query);
statement.BindTextParameterWithName("@name", "Matteo");
statement.BindTextParameterWithName("@surname", "Pagani");
await statement.StepAsync();
statement = await database.PrepareStatementAsync(query);
statement.BindTextParameterWithName("@name", "John");
statement.BindTextParameterWithName("@surname", "Doe");
await statement.StepAsync();
}
Please enter the Statement class, that can be used to perform additional operations on the query, like adding parameters and iterating over the results. To prepare a Statement object, you’ll need to call the PrepareStatementAsync() method passing as parameter the query to execute. How to manage parameters? The simplest way is to use named parameters, which can be added to a query simply by prefixing the @ symbol to the name of the parameter. In the sample, the insert query accepts two parameters: @name and @surname.
How to define the value of these parameters? By calling the BindTextParameterWithName() method on the Statement object with two parameters: the first one is the name of the parameter, the second one is the value we want to assign. In the sample, we’re going to add two users to the table: one with name Matteo Pagani and one with name John Doe. To execute the query, we call the method StepAsync() on the Statement object. There are also other versions of the BindParameterWithName() method, according to the data type of the parameter (for example, if it’s a number you can use the method BindIntParameterWithName()).
But the StepAsync() method has another purpose: it can be used also to iterate through the results of the query, in case we’re performing a query that can return one or more values. Let’s see, for example, how to perform a select to retrieve the data we’ve just inserted:
private async void OnGetDataClicked(object sender, RoutedEventArgs e)
{
Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");
await database.OpenAsync();
string query = "SELECT * FROM PEOPLE";
Statement statement = await database.PrepareStatementAsync(query);
while (await statement.StepAsync())
{
MessageBox.Show(statement.GetTextAt(0) + " " + statement.GetTextAt(1));
}
}
The first part of the code is the same we’ve seen before: we define the query (in this case, we retrieve all the rows of the People table), we prepare a Statement and we execute it with the StepAsync() method. The difference is that, this time, the StepAsync() method is performed into a while statement: it’s because the method will iterate over all the rows returned by the query so, every time we enter into the while loop, a new row is retrieved. In this sample, we expect to see the MessageBox twice: one for the user Matteo Pagani and one for the user John Doe. In the sample you see also how to get the values from the results: the Statement object offers some methods that starts with the Get prefix, that accepts as parameter the column index of the value to retrieve. There are some method for the most common data types: in this sample, since both Name and Surname are strings we use the GetTextAt() method, passing 0 as index to get the name and 1 as index to get the surname.
Of course we can combine what we’ve learned in the last two samples and, for example, we can perform a select query that contains some parameters:
private async void OnGetSomeDataClicked(object sender, RoutedEventArgs e)
{
Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");
await database.OpenAsync();
string query = "SELECT * FROM PEOPLE WHERE Name=@name";
Statement statement = await database.PrepareStatementAsync(query);
statement.BindTextParameterWithName("@name", "Matteo");
while (await statement.StepAsync())
{
MessageBox.Show(statement.GetTextAt(0) + " " + statement.GetTextAt(1));
}
}
In this sample, we retrieve from the People table only the rows where the column Name contains the value Matteo.
We have also another option to access to the columns of the rows we’ve retrieved with a query, but it’s disabled by default because it slower and it returns every value as string, instead of its proper type. To enable it you have to call the EnableColumnsProperty method of the Statement object: once you’ve done it, you can access to the values by using the Columns property of the Statement object: it’s a collection and you can access to each item by using the column’s name as index. Here is a sample:
private async void OnGetSomeDataWithColumnsPropertyClicked(object sender, RoutedEventArgs e)
{
Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");
await database.OpenAsync();
string query = "SELECT * FROM PEOPLE";
Statement statement = await database.PrepareStatementAsync(query);
statement.EnableColumnsProperty();
while (await statement.StepAsync())
{
MessageBox.Show(statement.Columns["Name"] + " " + statement.Columns["Surname"]);
}
}
Coming soon
In this post we’ve learned the basic concepts to use this new wrapper to perform operations on a SQLite database and which are the differences with another popular wrapper, sqlite-net. In the next post we’ll see how to deal with relationships, one of the most problematic scenarios to manage nowadays. Meanwhile, you can play with the sample project.
I can see that you are specifying the folder to store the database in:
Database database = new Database(ApplicationData.Current.LocalFolder, “people.db”);
Does this mean that this wrapper allows you to save the database into, for example, the “My Documents” folder, something that sqlite-net cannot do?
I’m intrigued by this wrapper but it does seem like you’ve lost the power of mapping classes onto the database, or am I misunderstanding something here?
Hi Philip,
honestly I haven’t tried, I’ve played with this wrapper just with a Windows Phone 8 application, but I think it will work only if the database file is stored in the isolated storage.
About the second question, you’re totally right: this new wrapper works only in “manual mode”, with plain SQL queries. There’s no mapping with classes, there are no LINQ operations, just plain SQL statement and iteration over the results.
So how exactly does one decide on whether to go with sqlite-net or codeplex for a windows phone project?
It all depends by your needs. If you have a simple app (for example, one table only) sqlite-net is much easier to use; if you need full control or you have a complex scenario (lot of tables, relationships, etc.) the new sqlite wrapper on codeplex is definitely better.
This database god compared to sqlite-net.In this database how to check table already created or not?
Hi, according to the SQLite documentation if you have to perform the following query:
SELECT name FROM sqlite_master WHERE type=’table’ AND name=’table_name’;
Replace table_name with the name of the table you want to check.
Hi,post my code check correct or not? this return column count=1.
string test = “Select name from sqlite_master where type=’table’ and name=’employeeRecord'”;
var statement = await myDataBase.PrepareStatementAsync(test);
I think yes, if it returns a value it means that the table exists; otherwise, the query would return no results.
any time return same value .but i got solution another way
while creating table itself use IF NOT EXISTS the problem will solved. sample :
string createTable = “create table if not exists employeeRecord ” + “(RegNo integer primary key not null,” + “Name varchar(20),” + “City varchar(20))”;
I will face another problem because While deleting particular row code will executed but table record is there. example code:
string deleteQuery = “delete from employeeRecord where RegNo=’3′”;
Statement statment = await myDataBase.PrepareStatementAsync(deleteQuery);
MessageBox.Show(“Deleted Successfully”);
this is correct or not?
Sorry i got this problem i mised following commends:
await statment.StepAsync();
thanks your cordination…
Sorry, but I think I didn’t understand your question.
Sorry, but I think I didn’t understand your question.
With sqlite-net I used http://www.codeproject.com/Articles/13419/SelectQueryBuilder-Building-complex-and-flexible-S to create queries. It might come in handy with this wrapper too.
Is this native wrapper need biuld in arm same as sqlite-net-wp8?If so,the solution with this can not build in emulater,isn’t it?
I think yes, since it’s the SQLite runtime that requires two different compilations processes: one for ARM and one for X86. If you want to target the phone, you’ll need to build it for ARM; otherwise, if you want to deploy on the emulator, you’ll have to use X86.
X86? Why Sqlite in my solution is ARM and Win32?
I just found my mistake.Thanks a lot.
So I’m finally running my project and I get “WinRT information: SQL Error”. And I’m not even sure where the error is coming from. Any suggestions qmatteoq?
After a couple of code changes, now it gives a System.ArgumentException on this line:
Statement readStatement = await database.PrepareStatementAsync(query);
which is really weird because I can’t find anything wrong with that line
Hi, did you work out how to fix this? I keep getting System.ArgumentException exceptions too and I have no idea why.
Thanks
Can you send me a sample project? With mine I can’t reproduce your issue.
i have downloaded your code but i am unable to find the Database wrapper class. Can you please show us Database wrapper class ?
and one thing more i would like to ask during building your code i am facing following error.
App.xaml The name “LocalizedStrings” does not exist in the namespace “clr-namespace:SQLiteNew”.
I had downloaded Sqlite new from link given by you in tutorial.
I really like your post and it will solve my so much issue. Please rectify my issues. It will be great help.
Hi, the Database class is part of the wrapper. Make sure that you’ve added, in your project, a reference to both SQLiteWrapper project and SQLite runtime (the one that is installed using the Visual Studio extension).
How to use the Windows phone 8 project. its not mentioned anywhere in your blog
I’m sorry, but what do you mean exactly? This post is about using the new SQLite wrapper with a Windows Phone 8 project.
I want to know how will i use the sqlite wrapper for windows phone 8 in my app project. there are no steps mentioned on how will I include the sqlite wrapper in my project.
Hi, I guess you didn’t read the post carefully 🙂 The procedure is explained in the section titled “Configure the wrapper”.
I just wanted to say that, it will be difficult for a beginner to able to deduce how to add project into your solutions. For more clarity these steps can be added
Right Click on you Solution–>Add–>Existing Project…–>select the SQLiteWinRTPhone.vcxproj
Thanks for the feedback, I’ll try to be more precise in the future. However, the post was targeted to developers that already has a basic development and Visual Studio knowledge: if a developer doesn’t know how to add a project to a solution, maybe there are a few steps that he should take before learning how to use SQLite in a Windows Phone app 🙂
I am getting error while I am importing the SQLiteWinRTPhone.vcxproj in my app project.
it says Unable to read the project file SQLiteWinRTPhone.vcxproj. Also it says
The imported project C:\Program Files\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.1.1\DesignTime\Common Configuration\Neutral\SQLite.QP80.props not found.
The SQlite version I have downloaded for windows phone is 3.8.0.2. What to do now ?
Unfortunately the SQLite library should match the installed SQLite runtime. You have to wait that the Codeplex project is updated or, otherwise, manually install an old SQLite runtime that matches (in your case, it’s 3.7.1.1).
🙁 from where can I get old (3.7.1.1) SQLite Runtime
I am able to include the SQLite wrapper project in my windows phone 8 app project by editing the SQLiteWinRTPhone.vcxproj and replacing the path for 3.1.17 to 3.8.0.2
Good alternative, I was going to propose this solution since I can’t find on the web the 3.7.1.1 SQLite Runtime for Windows Phone. Good catch, meanwhile I’ll try to contact the wrapper’s team to understand if they’re planning to keep it updated.
I built an app using this wrapper and when I tried to run the app on WP8 Simulator, it is giving an exception A first chance exception of type ‘System.Reflection.TargetInvocationException’ occurred in mscorlib.ni.dll ion the statement await database.ExecuteStatementAsync(query);
If you have the chance, send me your project and I’ll take a look.
Can’t we use this wrapper in a Portable Class Library for Windows Phone 8 ?
Nope, because the wrapper is based on Windows Runtime APIs, which are not supported by Portable Class Libraries.
Are you aware of any solution, to use this wrapper in a PCL
Hi, there’s no solution, since sqlite-net (as every other native library) can’t be reference in a PCL. What you can do is to write a add a shared interface in the PCL that describes the methods needed to access to the data: then, the specific project implements it using sqlite-net.
Hi, in your post you mention “f the database doesn’t exist, it will be created; otherwise it will simply be opened.”, is this definitely true for your example code?
I’ve got it all setup and using it with my own project, but if I run the section of code with the creation query, my app quits and I’m sure it’s because the database already exists.
Is there a way to check if the database already exists? I guess I could just check for the “people.db” file already being present in the local storage?
Thanks
Hi, you’re correct. The easiest way is to change the creation query like this:
string query = "CREATE TABLE IF NOT EXISTS PEOPLE " +
"(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"Name varchar(100), " +
"Surname varchar(100))";
By adding the “IF NOT EXISTS” statement, the table will be created only if it doesn’t exist. If it already exists, instead, the operation will simply be ignored and it won’t return any error.
Hi , I have implemented this solution in my VB project , I now have two projects in my solution – one containing the wrapper in c# and one my vb project , I also have the reference to Windows Phone SQLite correctly references – problem is that when I try to convert the C# examples to vb , my vb project cannot reference database , I do not have an import for referring to the C# wrapper – how do I use the wrapper with vb? thank you by the way , this is very useful if I can get it to work
Honestly I’ve never tried, I’m a C# developer so I’ve never tried this library with a VB.NET application. Maybe any blog reader out there can help Jerry?
I got it working with a VB wrapper , it reads the database ok and writes to the data base ok , I am using an existing database , but when I exit the program (I am running in the emulator) my edits to the data base are not saved. I am not sure what is going on , perhaps it is creating an instance of the database and when the program is complete it is not permanently saved permanently. Any ideas? Thanks
Hi, I got past the problem and have it working somewhat in VB. The problem now is when I am in the debugger and run it in the emulator and then stop the debugger and then run again it does not save me edits to the database. Between runs I still have to emulator up , I tried an old version of my app where I am reading and writing sequential files to isolated storage and it did save be data. What I think it is – I believe one has to create a folder and then put the SQLite data base in that folder , one cannot read and write to isolated storage in the root on the phone emulator or phone itself. This is different than regular windows 8 , in your example , if you have it creating a data base in the root , I do not think that data will persist and be there in the next execution of the app even though you still have the emulator running , you might try it and see if I am wrong or right. Most examples that are published are very helpful but the regularity situation is where one wants to load a database into isolated start up when the app is installed and first executed and then on subsequent runs just read and write to it. To do this on the phone maybe the file has to be loaded in a subdirectory that is created rather than the root. What do you think?
Hi, I got the code working with VB , the only problem is that the data in the SQLite file will not persist in the emulator from execution to execution and I am not turning the emulator off. Your example has it creating a file and does not demonstrate reading a SQLite file into working storage , editing it and then having it persist from run to run.
Hi, in the apps i’ve developed using this wrapper data is persisted in the storage from run to run. Check with a tool like Windows Phone Power Tools (http://wptools.codeplex.com) if the database has been correctly created in storage.
I past the problem of the data being persisted. However , I am have big problems with coordinating the read statements with the rest of my code. The read routines use the await and while it is awaiting my code continues on into the rest of the logic , I do not want it to , I have about 20 major pages and on each page I simply want to read all the data into memory and once that is complete I want to perform calculations and manipulations of the data in memory and once the user has completed this and presses the back arrow I want to save the data. I cannot coordinate the reading , is there a way to pause the code until the awaits are done , on a read there are two awaits one for the prepare and one for the step. This appears to be a problem. Also , I have worked with the iPhone SQLite3 and on there I am able to access the lower level sqlite3 calls , can this be done and how would you do it with this wrapper. Thanks for all your help , I know that I can make this work. Jerry
Hi, about awaiting the read operation, you can use the Task.WhenAll() method: by passing, as parameter, a list of operations, the method will return only when all of them are completed and have returned the result.
About the second question, what do you exactly mean with “access the lower level sqlite3 calls?” What you’re trying to achieve?
General question for wp8
Default design pattern for windows phone 8?
What do you mean exactly? If you’re talking about an architectural point of view, Model-View-ViewModel (MVVM) is the way to go.
Thanks for the information about using Task.WhenAll , I will try this. I can send the lower level calls to SQLite in an email to you , if you can send me you email.
How to I add to my solution the SQLiteWinRT project so that the classes become visible and I use it ? Please explain the steps. I’m just a newbie to Windows 8 development.
Hi Denil,
which problem are you facing? Everything is explained in the first part of the post titled “Configure the wrapper.”