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.
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
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
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
Figure 4: The main components inside SQLiteConnection
Figure 5: 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
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
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
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
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).
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
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
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
Next, let's look into the 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).
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!
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.
Please login to rate or to leave a comment.