Windows Phone 7 应用 sqlite 数据库 之 增删改查

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]

• 1 Introduction
• 2 Introduction to Sqlite Client for Windows Phone
  • 2.1 Infrastructure in Sqlite Client for Windows Phone
• 3 Using SQLite Manager to Simplify Database Management
• 4 A Useful Utility Class - DBHelper
• 5 A Simple Customer Editor
  • 5.1 Running-time screenshots
  • 5.2 Behind implementation
• 6 Summary

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 athttp://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 itsBuild 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

001.//others omitted...

002.usingSQLiteClient;

003.usingSystem.Linq;

004.usingSystem.IO.IsolatedStorage;

005.usingSystem.Collections.Generic;

006.usingSystem.Collections.ObjectModel;

007.namespaceWP7SQLiteClient.Helpers

008.{

009.publicclass DBHelper

010.{

011.privateString _dbName;

012.privateSQLiteConnection db = null;

013.publicDBHelper(String assemblyName, String dbName)

014.{

015.IsolatedStorageFile store =IsolatedStorageFile.GetUserStoreForApplication();

016.if(!store.FileExists(dbName))

017.{

018.CopyFromContentToStorage(assemblyName, dbName);

019.}

020._dbName = dbName;

021.}

022.~DBHelper()

023.{

024.Close();

025.}

026.privatevoid Open()

027.{

028.if(db == null)

029.{

030.db =new SQLiteConnection(_dbName);

031.db.Open();

032.}

033.}

034.privatevoid Close()

035.{

036.if(db != null)

037.{

038.db.Dispose();

039.db =null;

040.}

041.}

042.//Insert operation

043.publicint Insert<T>(T obj, stringstatement) where T : new()

044.{

045.try

046.{

047.Open();

048.SQLiteCommand cmd = db.CreateCommand(statement);

049.intrec = cmd.ExecuteNonQuery(obj);

050.returnrec;

051.}

052.catch(SQLiteException ex)

053.{

054.System.Diagnostics.Debug.WriteLine("Insert failed: "+ ex.Message);

055.throwex;

056.}

057.}

058.// Delete operation

059.publicvoid Delete<T>(stringstatement) where T : new()

060.{

061.try

062.{

063.Open();

064.SQLiteCommand cmd = db.CreateCommand(statement);

065.cmd.ExecuteNonQuery();

066.}

067.catch(SQLiteException ex)

068.{

069.System.Diagnostics.Debug.WriteLine("Deletion failed: "+ ex.Message);

070.throwex;

071.}

072.}

073.//Query operation

074.publicList<T> SelectList<T>(String statement) where T : new()

075.{

076.Open();

077.SQLiteCommand cmd = db.CreateCommand(statement);

078.var lst = cmd.ExecuteQuery<T>();

079.returnlst.ToList<T>();

080.}

081.publicObservableCollection<T> SelectObservableCollection<T>(String statement)

082.where T :new()

083.{

084.List<T> lst = SelectList<T>(statement);

085.ObservableCollection<T> oc =new ObservableCollection<T>();

086.foreach(T item in lst)

087.{

088.oc.Add(item);

089.}

090.returnoc;

091.}

092.privatevoid CopyFromContentToStorage(String assemblyName,String dbName)

093.{

094.IsolatedStorageFile store =

095.IsolatedStorageFile.GetUserStoreForApplication();

096.System.IO.Stream src =

097.Application.GetResourceStream(

098.newUri("/" + assemblyName + ";component/" + dbName,

099.UriKind.Relative)).Stream;

100.IsolatedStorageFileStream dest =

101.newIsolatedStorageFileStream(dbName,

102.System.IO.FileMode.OpenOrCreate,

103.System.IO.FileAccess.Write, store);

104.src.Position = 0;

105.CopyStream(src, dest);

106.dest.Flush();

107.dest.Close();

108.src.Close();

109.dest.Dispose();

110.}

111.privatestatic void CopyStream(System.IO.Stream input,

112.IsolatedStorageFileStream output)

113.{

114.byte[] buffer =new byte[32768];

115.longTempPos = input.Position;

116.intreadCount;

117.do

118.{

119.readCount = input.Read(buffer, 0, buffer.Length);

120.if(readCount > 0)

121.{

122.output.Write(buffer, 0, readCount);

123.}

124.}while (readCount > 0);

125.input.Position = TempPos;

126.}

127.}

128.}

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 theInsert and Delete methods. The most outstanding feature in the above code is the methodCopyFromContentToStorage, 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

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.

Behind implementation

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

01.publicpartial class App : Application

02.{

03.privateDBHelper _db;

04.publicDBHelper db

05.{

06.get

07.{

08.Assembly assem = Assembly.GetExecutingAssembly();

09.if(_db == null)

10._db =new DBHelper(assem.FullName.Substring(0, assem.FullName.IndexOf(',')),"database1.sqlite");

11.return_db;

12.}

13.}

14.//...others omitted

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

01.//others omitted...

02.usingWP7SQLiteClient.Dal;

03.usingSystem.Collections.ObjectModel;//ObservableCollection

04.usingSystem.ComponentModel;

05.usingSQLiteClient;

06.usingCommunity.CsharpSqlite;

07.usingSystem.Collections;

08.namespaceWP7SQLiteClient

09.{

10.publicpartial class TestDataEditor : PhoneApplicationPage

11.{

12.ObservableCollection<Customer> _customerEntries =null;

13.publicTestDataEditor()

14.{

15.InitializeComponent();

16.//retrieve dat

17.stringstrSelect = "SELECT ID,Name,Email,Desc FROM Customer ORDER BY ID ASC";

18._customerEntries = (Application.Currentas App).db.SelectObservableCollection<Customer>(strSelect);

19.foreach(Customer data in _customerEntries)

20.{

21.TextBlockID.Text += data.ID + Environment.NewLine;

22.TextBlockName.Text +=data.Name + Environment.NewLine;

23.TextBlockEmail.Text +=data.Email + Environment.NewLine;

24.TextBlockDesc.Text +=data.Desc + Environment.NewLine;

25.}

26.}

27.//others omitted...

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.

01.privatevoid btnAdd_Click(objectsender, RoutedEventArgs e)

02.{

03.DateTime start = DateTime.Now;

04.intrec;

05.Random rnd =new Random();

06.stringstrInsert = " Insert into Customer (Name,Email,Desc) values (@Name,@Email,@Desc)";

07.for(int i = 0; i < 5; i++)

08.{

09.Customer tst =new Customer

10.{

11.Name ="Name " + i,

12.Email = Name +"@" + "aaa.com",

13.Desc ="Desc for " + i

14.};

15.rec = (Application.Currentas App).db.Insert < Customer>(tst,strInsert);

16.}

17.System.Diagnostics.Debug.WriteLine("\nInserted 5 "+ " rows\r\nGenerated in "+ (DateTime.Now - start).TotalSeconds);

18.}

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.

1.privatevoid btnDel_Click(objectsender, RoutedEventArgs e)

2.{

3.DateTime start = DateTime.Now;

4.stringstrDel = " Delete from Customer where ID="+"(SELECT COUNT(*) FROM Customer)" ;

5.(Application.Currentas App).db.Delete<Customer>(strDel);

6.}

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 ArticleContinue reading and see our next or previous articlesNext 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 profilehere.