Published: 10 Mar 2011
By: Xianzhong Zhu
Download Sample Code

In this article I will introduce to you a new, strong and easy-to-use native database solution, called Sqlite Client for Windows Phone, provided by CodePlex, together with other associated programming tips.

Contents [hide]

Introduction

Either from a user's perspective or from a developer's perspective, Windows Phone 7 has brought us dozens of new and exciting features. At the same time, there are also numerous missing features in the current Windows Phone 7 Series expected by various kinds of users. One of the representative regrets may be lacking local database API – the current Windows Phone 7 OS only supports access to data via XML, Isolated Storage, and Cloud Storage. Do we really have no other choice? Not necessarily so. Luckily there are several developers and teams have been working to fill this gap.

Note although there have been several efforts to create Windows Phone 7 databases, in the end, these all run on top of isolated storage. In this article I will introduce to you a new, strong and easy-to-use native database solution, called Sqlite Client for Windows Phone, provided by CodePlex, together with other associated programming tips.

NOTE

The sample test environments in this article involve:

1. Windows 7;

2. .NET 4.0;

3. Visual Studio 2010;

4. Windows Phone Developer Tools RTW;

5. Sqlite Client for Windows Phone (http://sqlitewindowsphone.codeplex.com/);

6. (Optional) sqlite-manager (http://code.google.com/p/sqlite-manager/);

7. (Optional) MVVM Light Toolkit (http://mvvmlight.codeplex.com/).

Introduction to Sqlite Client for Windows Phone

As is well-known, SQLite is a famous open sourced embedded database system, already supported on iOS and Android. Thanks to at least Dan Ciprian Ardelean, we can now move those same files into our WP7 versions via C#-SQLite! Recently, Dan has put a renewed, stronger and easier-to-use solution, named Sqlite Client for Windows Phone, on CodePlex at http://sqlitewindowsphone.codeplex.com/releases.

Figure 1: Download Sqlite Client for Windows Phone

Download Sqlite Client for Windows Phone

Sqlite Client for Windows Phone has made some great updates with Booleans, Blobs, and transactions, based upon the old C#-SQLite. And also, it provides a good sample included in the download.

Figure 2: Sqlite Client for Windows Phone and the sample project

Sqlite Client for Windows Phone and the sample project

The subsequent thing is simple: rebuild the library Community.CsharpSqlite.WP to get an assembly named Community.CsharpSqlite.WP.dll (the release version size is 525KB), add related reference in your WP7 Silverlight project, and then put in use the functionalities.

Infrastructure in Sqlite Client for Windows Phone

If you have any SQL scripts based database experience, you can easily work with Sqlite Client for Windows Phone. Especially, built upon the former C#-SQLite project, it further simplifies the basic database and table related operations by introducing a few helper classes (in the file SQLiteClient.cs), such as SQLiteException, SQLiteConnection, and SQLiteCommand. Figures 3, 4, and 5 illustrate the main components and associated relationships in Sqlite Client for Windows Phone.

Figure 3: The topmost components in Sqlite Client for Windows Phone

The topmost components in Sqlite Client for Windows Phone

Figure 4: The main components inside SQLiteConnection

The main components inside SQLiteConnection

Figure 5: The main components inside SQLiteCommand

The main components inside SQLiteCommand

Before using Sqlite Client for Windows Phone, it's necessary to introduce another useful tool named sqlite-manager (http://code.google.com/p/sqlite-manager/). Since we have to deal with a lot of Sqlite related stuff, some readers may query: how can we create Sqlite databases, tables, views, and more? Just take it easy; all these can be accomplished via sqlite-manager, a famous Firefox add-on.

Using SQLite Manager to Simplify Database Management

SQLite Manager is a FireFox add-on, which can be easily retrieved and installed using FireFox's add-on manager (Figure 6).

Figure 6: Retrieve SQLite Manager and install it in FireFox

Retrieve SQLite Manager and install it in FireFox

As indicated in Figure 5, if you open up the add-on explorer in FireFox and enter "sqlite manager" in the textbox and start searching you will easily retrieve this add-on. Then you can click the button "Add to Firefox..." to start to download and install SQLite Manager. Note, as hinted later in Firefox, you should restart Firefox to finish the installation.

Using SQLite Manager is easy. This is the first time I meet SQLite Manager; I find it mighty and pretty easy to start with. If you ever used VB6, you might be familiar with the built-in database manager – VisData with which to create small Access database. To be honest, it was not easy to use, but at that time we felt well. Now you bear in mind SQLite Manager is better than VISDATA 1,000 times.

Figure 7: The experience of SQLite Manager is cool

The experience of SQLite Manager is cool

You can use SQLite Manager to deal with nearly all kinds of SQLite related things. To learn SQLite related concepts and syntaxes, you can use the Help menu to get a quick guide.

OK, in this article related sample project, I created one table named Customer in a database named database1.sqlite.

Figure 8: Schema for table Customer

Schema for table Customer

After creating database1.sqlite, copy/move it to the sample project (WP7SQLiteClient) root folder. Then set its Build Action property to Resource. The reason to do this is relevant to the following handling with this file. You can of course choose Content, but you should resort to other related solutions.

A Useful Utility Class - DBHelper

As indicated above, Sqlite Client for Windows Phone has provided a high-level encapsulation around the common database operations using the widely-known SQL statements. To deal with the SQLite database in Silverlight for Windows Phone 7, we can use the objects defined in the file SQLiteClient.cs (in the source library project) directly, i.e. SQLiteException, SQLiteConnection, and SQLiteCommand.

Although Sqlite Client for Windows Phone does not interact with Isolated Storage directly, to add support for Isolated Storage is necessary to improve the system performance. Hence, we can further encapsulate the SQLiteClient objects mentioned previously. For this, Chris has brought a good utility class called DBHelper. To be used in our case, I've made a slight modification with it.

Listing 1: The modified utility class DBHelper

By the way, I've not also performed optimization with the above helper. Hope readers to continue with this work according to your related job. Simply put, I've mainly added the Insert and Delete methods. The most outstanding feature in the above code is the method CopyFromContentToStorage, with which we achieved the preceding target – setting up relations with Isolated Storage.

Following up the above routine, you can continue to extend the previous DBHelper class, including more typical and helpful CRUD operations and other more detailed table manipulations, as well as full optimization. Next, we'll construct a simple customer editor using the above stuff.

A Simple Customer Editor

Start up Visual Studio 2010 to create a simple Windows Phone 6 application WP7SQLiteClient. Then, open the solution and add a reference to the assembly Community.CsharpSqlite.WP.dll (from the bin directory of the compiled Sqlite Client for Windows Phone project).

Running-time screenshots

To gain a better understanding with the following explanation, let's first look at the running-time snapshots, as shown in Figures 9 and 10.

Figure 9: The initial screenshot of the customer editor

The initial screenshot of the customer editor

Note in the initial screenshot of the customer editor there are only three records which have been populated using Sqlite Manager. When you click the button "Add" five customers will be added at the table Customer. In this sample, I've not refresh the screen immediately. However, if you press the hardware Back button (we've used the emulator) and navigate back to this screen again you will see the five newly-appended records, as shown in Figure 10.

Figure 10: Five sample customer records added to the table Customer

Five sample customer records added to the table Customer

Now, press the button "Del Last 1" in Figure 10, the last record will be removed from the table Customer in the database database1.sqlite. Figure 11 illustrates the related screenshot.

Figure 11: The last record is removed from the table Customer

The last record is removed from the table Customer

Next, let's look into the behind implementation.

Behind implementation

First, we'd better defined a public property db in the App class, so that we can access it from every page.

Next, let's put initialization in the Behind-Code file TestDataEditor.xaml.cs.

In the above code, we first defined a variable _customerEntries of type ObservableCollection<Customer>. Then, in the constructor we built up a standard SQL SELECT string. Next, by invoking the method SelectObservableCollection<Customer> defined in the DBHelper class related instance in the global App class, all customer data have been retrieved into the variable _customerEntries. At last, by iterating through the collection structure, we succeeded in displaying all customer data onto the screen.

1. Adding records

Now, let's check out how to add the five sample customer data into the table Customer.

As is seen, with the help of the helper class DBHelper, the retrieving and insert operations with SQLite database seem quite similar to those in desktop-styled relational database systems – easy to write and follow up.

2. Deleting records

Deletion operation is even easier, as shown in Listing 5 below.

In the above code, the last record in the table Customer will be removed. Obviously, to write complex and fully-functional SQL statements you should first know the SQLite database related syntaxes very well. In fact, this is not difficult – there have already tons of tutorials concerning SQLite (for example at http://www.sqlite.org/lang.html).

Summary

In this article we've brought to you a mighty and easy-to-use open sourced project Sqlite Client for Windows Phone that supports native database manipulations in Windows Phone 7. As you've seen, I've still given pretty elementary usage about Sqlite Client for Windows Phone. Hence, the real pearl and agate inside Sqlite Client for Windows Phone will depend upon you readers' further digging. Happy programming with Sqlite Client for Windows Phone!

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Xianzhong Zhu

I'm a college teacher and also a freelance developer and writer from WeiFang China, with more than fourteen years of experience in design, and development of various kinds of products and applications on Windows platform. My expertise is in Visual C++/Basic/C#, SQL Server 2000/2005/2008, PHP+MyS...

This author has published 81 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category


Displaying Notification Messages in a Silverlight Dashboard Application
In this article we will see how we could display a notification message and further a list of notifi...
Air Space Issue in Web Browser Control in Silverlight
Air Space issue is a common issue in Web Browser control in Silverlight and WPF. To explain the issu...
TextBox Row Filter in Telerik's RadGridView in Silverlight
If you have come across the JQGrid features, you might have seen the filter row available as the fir...
Widget Refresh Timer in MVVM in Silverlight
In this article we'll see how to refresh and disable widgets using the Model View View-Model pattern...
Develop a Flexible 2.5D Scene Editor Targeting Silverlight RPG Games - Part 2
In this article, I'm going to introduce to you how to construct such a 2.5D RPG game scene editor th...

You might also be interested in the following related blog posts


Running the Same Query Against Multiple Databases read more
Mapping Associations in the EDM Designer read more
SQLite, ADO.NET, Prepared Statements, Transactions and Enterprise Manager read more
Batch files to check SQL 2005 (MDF) files in and out of Subversion source control read more
Top
 
 
 

Discussion


Subject Author Date
placeholder transactions Nermin Tanovic 4/1/2011 4:26 PM
My simple answer Xianzhong Zhu 4/2/2011 9:22 PM
placeholder More details about your code? Xianzhong Zhu 4/5/2011 6:47 AM
again Nermin Tanovic 4/4/2011 12:26 PM
placeholder sure Nermin Tanovic 4/5/2011 10:32 AM
My final answer Xianzhong Zhu 4/6/2011 9:56 AM
placeholder thank you Nermin Tanovic 4/9/2011 10:46 PM
Code Gaby Juárez 5/7/2011 12:29 PM
placeholder RE: Code Sonu Kapoor 5/9/2011 10:09 AM
I'll try that asap Xianzhong Zhu 5/9/2011 4:11 AM
placeholder Please try like this Xianzhong Zhu 5/9/2011 10:06 AM
???dll???????? Li Weiwei 9/15/2011 6:17 AM
placeholder What do you mean Xianzhong Zhu 9/15/2011 9:57 PM
What do you mean Xianzhong Zhu 9/15/2011 10:00 PM
placeholder Thank's Marco Aguilar 10/17/2011 11:24 AM
Help please...! Marco Aguilar 10/17/2011 11:45 AM
placeholder Great article on SQLite Patrick Roy 4/24/2012 7:58 AM
Errors in DBHelper Class Bob Zou 7/5/2012 10:23 PM
placeholder Unable to open databse file Kamal v 9/1/2012 6:47 AM
Unable to open databse file Kamal v 9/1/2012 7:11 AM
placeholder RE: Unable to open databse file Kamal v 9/2/2012 2:42 PM

Please login to rate or to leave a comment.