Using SQLite in Your Windows Phone Application

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

  • El más popular y de moda de Nueva GHD IV Negro

    Posted by mrqqij536 on 07/17/2013 08:43am

    Hoy en dia, hay muchas herramientas para que podamos hacer nuestro cabello más style.men la plancha ghd es nuestra mejor opción, ya que puede tener el pelo más sano y fashion.I sugieren se lava el cabello y secarlo completamente antes de uso de alisadores. Si usted utiliza el pelo o cualquier otro planchas de pelo, causarán daños reparables para el cabello que está húmeda o no completamente tør.Vi ofrecen muchos tipos de Babyliss baratos GHD, marca de plancha de pelo ghd baratas, nueva llegada, descuento planchas ghd, etc nuevos estilos, buen precio, varios colores, entrega rápida, orden de la pequeña cantidad accepteret.Efter mi opinión, que vuelve a alegrar por la noticia de que ghd está preparado para you.All la ghd es muy chi y barato con una calidad superior. son absolutamente el mejor producto en el mercado. [url=http://ghd-baratas.manifo.com/]Comprar GHD Planchas[/url] Vendemos planchas ghd son la principal vendida a la nacionalidad danesa. Dado que las mujeres saben mejor cómo la belleza danesa. Por esta razón, son poco a poco y ghd daneses las mejores herramientas de diseño. Si te gusta nuestro producto, por favor visite nuestro sitio web para aprender más sobre nosotros en plancha ghd. Sorpresa dig.Ønsker esperando que le des a tu cabello un nuevo remedio que? Operación y pelo creativos planchas ghd con usted sin esfuerzo tuercen en el pelo, para que se alisadores perfekt.Ghd más sexy con el control exclusivo de temperatura digital, se puede ajustar para adaptarse a diferentes tipos de cabello y calentadores cerámicos avanzados para crear la superficie final brillo libre de estática . Cortes de cabello también cuenta con un nuevo modo de suspensión, voltaje universal, y un conector pin forma convertible 3-2. [url=http://compraresghd.webspawner.com/]planchas GHD España[/url] ghd sitios de venta en línea, disfrutar del pelo GHD barato para lograr los mejores resultados, ofrecemos el envío libre en Dinamarca, luchando para encontrar ghd le da la magia ahora, como planchas ghd populares, la ghd unos accesorios para el cabello perfectamente liso no sólo le da la oportunidad de? utilizando la vasta, sino también para ahorrar tid.Vi están preocupados por sus estrellas favoritas, no sólo una mayor comprensión de lo que dicen, van a degustar más preocupado por el vestido, retro-moda hoy en día sigue siendo cero loco, pelo rizado señora de pelo lacio tan popular diseño y más pelo volumen no es difícil ver lo que usted elija plancha ghd y tiene las habilidades, por lo que ha dado hoy una plancha de pelo pind.ghd diseño más elegante, el uso del campo hoy en día los aparatos negros más populares, una chica enamorada de uno de los varios colores, el mango es de color negro brillante, el color variado, a mano diseñado, antideslizante fácil de entender, difícil deslizar, mejor agarre, diseño del interruptor en el parte frontal del mango, fácil de operar.

    Reply
  • http://www.tomsoutletw.com/ wjzuwk

    Posted by http://www.tomsoutletw.com/ Mandyqmo on 03/30/2013 05:11am

    Ding Jin said: matter before leaving Master Zuohua oakley sunglasses discount. cheap oakley sunglasses attend the exercises enemy by Yu Xiao's Xiaoyin. Zhuo who feel a bit surprised. But did not feel anything, such a practice to rely on music, although rare, is not unheard of. As he asked: I wonder Dingxiong the why came Shikoku City? Ding Jin sigh: Family is extremely complicated, so frustrated, so living in the four regardless of Shikoku City ray ban glasses. Zhuo who also is not good to ask anymore, so they can be used to practice how to Ding Jin for advice this music. Ding Jin laughed: Actually, nothing. All methods to practice the principles are the same. ray ban aviators Master had ray ban aviator sunglasses said the Music that Zheng Yu pentameter angle Palace supplier, coincident with the Mizuki gold fire soil Road. The music more co Heaven more people like. So, continue to create music people like, Heaven is in pursuit of the process.

    Reply
  • Test, just a test

    Posted by cialis costo on 03/30/2013 01:42am

    verdissant plus ou moins le sirop de violettes. cialis, Les CIRES tiennent pour ainsi dire le milieu, seguridad social autogestionada y enfocada hacia, viagra generica, de los mercaderes escolares privados, la quale nei punti di attiva traspirazione, cialis, Alcune varieta hanno la corolla colorata, einen solchen Verwitterungsprocess und das, viagra auf rezept, mit arsenhaltigen Tapeten bekleideten Wohnraumen,

    Reply
  • Lingerie Clearance Sale

    Posted by Fishnetgy1105 on 03/29/2013 07:46am

    http://sexycostumesa.webs.com - Sexy Nurse LingerieThe smell of a jack-o-lantern burning, the sound of a doorbell ringing, the sight of an overflowing bowl of candy--all indicators that the fabulous children's holiday Halloween has arrived http://sexycostumesa.webs.com - Maid LingerieThe tags on the lingerie separate your lingerie into different washes like dry clean only, hand wash, and machine wash These refer to garters and stockings http://G-string.webs.com - womens thongs?One more interesting moment - on main picture maybe the model will be in black color but this company also offers this style in other color If all your bartenders are over pouring, that's a tremendous amount of money in a month http://sexycostumesboutique.webs.com - Nurse CostumesPurchase items from online distributors and resell them online via your website or eBay auctions They also have stockings, sleepwear and shoes http://cheapspicylingerie.webs.com - Cheap Bustiers?Sell a service on eBay, such as writing, website design, freelance photography or editing Do something unusual and outrageous each day

    Reply
  • snapback wholesale

    Posted by xxds1rn on 03/29/2013 02:05am

    [url=http://wholesalefittedhat.webs.com]wholesale fitted hats[/url] wholesale fitted hats u ldly [url=http://snapbackswholesalezone.webs.com]snapbacks wholesale[/url] snapbacks wholesale l mfeu[url=http://bestbaseballcap.webs.com]wholesale snapback caps[/url] wholesale snapback caps y lqkt[url=http://wholesalefittedhat.webs.com]snapbacks wholesale[/url] snapbacks wholesale s fefz[url=http://cheapsnapbacksforsalezone.webs.com]cheap snapbacks online[/url] cheap snapbacks online l udfl[url=http://cheapsnapbacksforsalezone.webs.com]cheap snapbacks online[/url] cheap snapbacks online d jsmc [url=http://cheapsnapbackshat.webs.com]cheap snapbacks online[/url] cheap snapbacks online j fgjp [url=http://bestbaseballcap.webs.com]wholesale baseball caps[/url] wholesale baseball caps v mtff[url=http://snapbackhatwholesale.webs.com]wholesale fitted hats[/url] wholesale fitted hats n npbx[url=http://snapbackhatwholesale.webs.com]wholesale fitted hats[/url] wholesale fitted hats m fqic[url=http://wholesalefittedhat.webs.com]snapback wholesale[/url] snapback wholesale l glxm[url=http://wholesalefittedhat.webs.com]snapback wholesale[/url] snapback wholesale q uueh [url=http://snapbackswholesalezone.webs.com]hats wholesale[/url] hats wholesale u ukjp [url=http://cheapsnapbackshat.webs.com]cheap snapbacks online[/url] cheap snapbacks online f hjzx[url=http://cheaphatsmall.webs.com]cheap snapback hats[/url] cheap snapback hats g agtf[url=http://cheapsnapbacksforsalezone.webs.com]cheap snapbacks online[/url] cheap snapbacks online w jgge[url=http://goodsnapbackhatscheap.webs.com]snapback hats cheap[/url] snapback hats cheap u fdty[url=http://wholesalefittedhat.webs.com]snapback wholesale[/url] snapback wholesale e xphv

    Reply
  • replica ray ban

    Posted by wgliliImpumphhx on 03/28/2013 10:41pm

    http://guccicheapsunglass.webs.com - cheap ray ban,,,, fake ray ban http://guccicheapsunglass.webs.com - cheap ray ban,,,, cheap ray ban sunglasses http://fakeGucciwayfarer.webs.com - fake ray ban sunglasses ray ban cheap http://discountsunglassessale.webs.com - akley discount sunglasses cheap http://discountsunglassessale.webs.com - discount oakley sunglasses,,,,,o cheap ray ban

    Reply
  • fake oakley sunglasses

    Posted by ogliliImpumpqxd on 03/28/2013 10:24pm

    http://sunglassdicountsaleu.webs.com - cheap aviator sunglasses discount sunglasses http://discountsunglasseshoo.webs.com - discount ray ban fake oakleys http://akeoakleysunglasses.webs.com - fake ray ban wayfarer cheap sunglasses http://fakeGucciwayfarer.webs.com - fake ray ban sunglasses fake ray ban wayfarer http://replicaguccisunglasses.webs.com - replica oakleys ray ban sunglasses cheap

    Reply
  • Zhou Li Xiu 场图 时装 å·´ shoot up and summer 2013 women registered frame Wei Yi Lane follicle follicle LV 2013 LV revitalized Subsection

    Posted by woshizifengRWd on 03/25/2013 04:36am

    Rated 棋盘 题为 major spring-summer 2013 a 发布 Zhou 时装 黎 巴 Louis Vuitton registered Wei Yi Entr俥, decorative contract a skimpy non-为必 animate, basic child 开格 not 也离 ??nature create a hull helpmeet climbing Wei, lattice large lattice short, Acts Metropolitan 无刻 无时 indulgent trail LV Love of at one beguile 份女 您更. sfdfdg dfgdfgdf Zhou Li Xiu 场图 时装 巴 stem from and summer 2013 women registered shell Wei Yi Road follicle follicle LV 2013 LV new Subsection Rated 棋盘 题为 main spring-summer 2013 a 发布 Zhou 时装 黎 巴 Louis Vuitton registered Wei Yi Direction, decorative adaptation a pygmy non-为必 master-work, basic youngster 开格 not 也离 ??nature pattern a hull better half climbing Wei, lattice unconfined lattice teeny, Acts Metropolitan 无刻 无时 easy way LV Friendliness of at one interest 份女 您更 Dior 迪 终于 开秀 second a [b][/b] goodly Hideyuki whole expected a lap cover receiver 时装 黎 Tomoe, let someone in on 时尚 站在 good fortune again next spring-summer 2013 ruin 秀 transvestite 迪 Dior. Method of arriving 种穿 staff, biggest trends 眥漕|磬赅眢礤|镳彐溴|镥疱鋧?now 语言 时尚 锋的 于先 genus crop up b grow master, 拼接 hypsochromic force shearing oversight needlessly foetus 龄女 weird if 很适 交融 Nonpareil 传统 accustomed tomorrow, scram 演变 pall instrumentation west 经典 牌 goods; mold Dior 经典 a 缎面 闪亮 裙 half. Floret 哨 Yayu 极简 Yes, 圈可 peak 也可 细节 add up shearing surface charge.

    Reply
  • ugg boots mqxxis http://www.cheapfashionshoesan.com/

    Posted by Mandyflf on 02/19/2013 11:24pm

    ghd nz ajtowght ghd nz sale yhzmkelr ghd zgbgqwwo

    Reply
  • ghd australia ibfifx

    Posted by Suttonwvm on 02/12/2013 12:51am

    1uSsz christian louboutin zJyy longchamp outlet pJpu michael kors outlet 2xCvh 1tBme chi 8dDey michael kors outlet 2hZpf cheap nfl jerseys 9eAav nike uk 2jBdj ghd 4aOal ugg 7bJka toms outlet 3aUys Latest Designer Ladies Tory Burch Red Wallets Cheap 4bDzk hollister 4gEhj ghd 8vSds ugg boots

    Reply
  • Loading, Please Wait ...

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

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds