Using SQLite in Your Windows Phone Application

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Introduction

In the rush to get the first version of Windows Phone out, Microsoft did not include a lot of features in the initial launch of the Windows Phone platform. Many useful things, like copy/paste, were missing; another notable absentee feature was support for a local database.

Application developers were expected to use server-side databases to achieve their desired results. Fortunately for us, open source efforts from the developer community delivered support for SQLite to the Windows Phone, allowing the platform to have local database support before it was officially launched in Windows Phone Mango.

Getting the SQLite Library

The SQLite client for Windows Phone is available at http://sqlitewindowsphone.codeplex.com/

At the time of writing the article, version 0.6.1 was available (download link is http://sqlitewindowsphone.codeplex.com/releases/58255/download/191978).

The download (available under the LGPL license) includes the source code.  When you extract the contents of the download, you will notice that it contains 2 Visual Studio Projects, (a) Community.CsharpSqlite.WP (which contains the code for the wrapper), and (b) a test project CSharpSqlite.TestProject (which contains test code).

For application developers targeting local database support, the binary, which is compiled from the project "Community.CsharpSqlite.WP" is necessary.

How SQLite on Windows Phone Works

SQLite on Windows Phone uses the feature of isolated storage as is data repository.

Hands-On

Open the downloaded code and compile the code for the SQLite Client for Windows Phone. Copy the binary file "Community.CsharpSqlite.WP.dll" to a temporary location.

Now Create  a Visual Studio project called "WLSQLiteDemo" of type Windows Phone application, from the available templates under Silverlight for Windows Phone, under Visual C#.

Create a Visual Studio Project
Create a Visual Studio Project

When prompted for the target Windows Phone OS version, select WP OS7.1

Target Windows Phone OS Version
Target Windows Phone OS Version

On the MainPage.xaml file, add four button controls titled, (a) "Create/Open", (b) "Populate", (c), "Clear", and (d) "Close".

Now, add the SQLite client file as a reference from the project settings. The file should be in the temporary location we mentioned above.

In the code behind for MainPage.xaml (the file MainPage.xaml.cs), add the following line to make the SQLite library available on the page.

// MainPage.xaml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
using SQLiteClient;
 

Now we need to add the event handler for the button click events. But before we do that, we need to add a variable of type SQLiteConnection, which we will use in the application.

public partial class MainPage : PhoneApplicationPage
    {
        SQLiteConnection mySQLiteDB = null;
 
        // Constructor
        public MainPage()

Now, we can add code to establish the connection to the SQLite database under the click event handler for the button "Create/Open".

private void buttonOpen_Click(object sender, RoutedEventArgs e)
        {
            if (mySQLiteDB == null)
            {
                mySQLiteDB = new SQLiteConnection("MyTestDB");
                mySQLiteDB.Open();
 
                buttonOpen.IsEnabled = false;
                buttonClear.IsEnabled = false;
                buttonPopulate.IsEnabled = true;
                buttonClose.IsEnabled = true;
            }
        }

The above code will ensure to open the connection only when a connection object does not exist. It also sets the initial visibility state of the other UI controls on the page.

Next, we need to create a table and populate data. We will achieve this using the SQLiteCommand object.

We will first create a table that will contain an ID, a name and a zipcode. We will then populate this table with dummy data.

private void buttonPopulate_Click(object sender, RoutedEventArgs e)
        {
            SQLiteCommand cmd = mySQLiteDB.CreateCommand("Create table RegisteredStudents (id int primary key,name text,zipcode numeric(7))");
            int i = cmd.ExecuteNonQuery();
            int id = 0;
            string name = "Name" + id;
            int zipcode = 98000;
            for (int j = 0; j < 10; j++)
            {
                id++;
                name = "Name" + id;
                zipcode = 98000 + id;
                cmd.CommandText = " Insert into RegisteredStudents (id, name, zipcode) values (" + id + ",\"" + name + "\"," + zipcode + ")";
                i = cmd.ExecuteNonQuery();
            }
            buttonPopulate.IsEnabled = false;
            buttonClear.IsEnabled = true;
        }

In the code snipped above, we created a table called RegisteredStudents and added 20 rows in the table. We also updated the visibility of the UI controls on the page.

The next step involves cleaning up of the database. Here we can again use SQLiteCommand object and fire off a SQL script to drop the table.

We will do that in the click event handler for the "Clear" button.

private void buttonClear_Click(object sender, RoutedEventArgs e)
        {
            SQLiteCommand cmd = mySQLiteDB.CreateCommand("drop table RegisteredStudents");
            int i = cmd.ExecuteNonQuery();
 
            buttonPopulate.IsEnabled = true;
            buttonClear.IsEnabled = false;
        }

In the code snippet above, we also change the visibility of the buttons for "Clear" and "Populate"

Finally, as part of application cleanup, we have provided the ability to drop and dispose the connection. This will help preserve system resources.

private void buttonClose_Click(object sender, RoutedEventArgs e)
        {
            if (mySQLiteDB != null)
            {
                mySQLiteDB.Dispose();
                mySQLiteDB = null;
                buttonOpen.IsEnabled = true;
                buttonClear.IsEnabled = false;
                buttonPopulate.IsEnabled = false;
                buttonClose.IsEnabled = false;
            }
 
        }

Now, build and compile your applications. If you want, you can add some TextBox controls and bind them to the database fields (this exercise is left to the reader) to see that you are creating a real  data-bound application.

If you are having issues compiling code, you can download a sample of working code below.

Congratulations, we just built a Windows Phone application using SQLite as a local database store. Woohoo!!!!

Summary

In this article, we learned how to create a local database store on Windows Phone using SQLite client libraries. I hope you have found this information useful.

Download the code for this article.



Related Articles

Downloads

Comments

  • read data

    Posted by tusss on 10/30/2012 07:40pm

    how to read data...?i am not getting anything like sqlitedatareader?example please and what to use in using .... to get reader?

    Reply
  • how to read

    Posted by tek on 10/30/2012 03:56pm

    how to read stored data? I want to retrieve data stored (eg : ID ,Name, Zip). Thank you

    Reply
  • database deleted when close the emulator

    Posted by aruna on 10/01/2012 05:53am

    hi, i have same problem with this solution which is posted by kyzzer. when i close emulator my data goes away..please suggest any solution if anyone have.

    • hw to read

      Posted by tussar on 10/30/2012 07:37pm

      Hi how can we retrieve data. sqldatareader is not showing up in my code...

      Reply
    Reply
  • Data didn't show

    Posted by eng_dev on 09/05/2012 02:28am

    When i display data i didn't get anything but in id i get according to table rows 0 in the id column

    Reply
  • Problem

    Posted by N pareek on 09/04/2012 11:44pm

    If I Close the emulator and rerun the application, Data Not found i have inserted. how can I maintain the data in sqlite table.

    Reply
  • Problem

    Posted by N Pareek on 09/04/2012 11:32pm

    If I Shutdown the emulator and rerun the application there is no data found. how can I maintain the the data in the table?

    Reply
  • tnx..

    Posted by kyzzer on 08/10/2012 12:32am

    thankyou for this post it helps on my project,but i edit few codes because the add and delete is not working,i made it run in my side... the one problem is when the emulator is close the database is deleted.... any suggestion to this....

    Reply
  • solve

    Posted by gopikrishna on 06/15/2012 05:11am

    i want sqlite datareader component how to get

    Reply
  • hope this article help a lot

    Posted by gopikrishna on 06/04/2012 02:59am

    super and simple concept

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date