using sqlite in windows phone 7/successful to deal with the problem in wp to use like select chinese character

In the current version of Windows Phone, there’ s no support for a built-in local database. This will be taken care of in the next release of the OS (codename “Mango”), which will be released late this year. Mango will have a built-in SQL CE (-like) database which can be accessed using LINQ to SQL. At the time of writing, there seem to be a few drawbacks you’ll have to keep in mind:

• The backend store is in a non-standard format
• It’s not possible to execute ad-hoc SQL queries (neither DDL nor DML)
• Shipping a database file with your app is a complicated process

If any of these limitations is a blocker for your application, or you just want to have a local database right now, using SQLite is a great alternative.

Using CoolStorage for easy SQLite access on Windows Phone

By far the easiest way to use a local SQLite database in your Windows Phone app is by using the free, open-source Vici CoolStorage ORM library. There is a specific native build for Windows Phone which includes a driver for SQLite.

Adding CoolStorage to your app is pretty straightforward:

• Option 1: If you have NuGet installed in VS2010, add the Vici CoolStorage package (available in the NuGet gallery)
• Option 2: If you don’t have NuGet, download the binaries from the Vici Project website and reference both Vici.Core.WP7.dll and Vici.CoolStorage.WP7.dll

Then you add the following line of code somewhere in your initialization code:

CSConfig.SetDB("mydb.sqlite"); // "mydb.sqlite" is the name of your database file 

 

By default, CoolStorage will create the database file for you if it doesn’t exist. But for this article, we’ll assume you already have a database with data (see here)

CoolStorage is a full-blown ORM with support for relations, lazy loading, relation prefetching, etc, so you will be able to use all of that in your Windows Phone app, but for once I won’t go into that, because if you want you can also use CoolStorage as a lightweight data layer and execute ad-hoc SQL queries.

Here are some examples:

// execute a SQL insert statement 
CSDatabase.ExecuteNonQuery("insert into customer (name,city) values (@name,@city)", 
                                  new { name="Microsoft", city="Redmond" });

// execute a select SQL statement and map the result to a class 
  
class QueryResult 
{ 
   public string name; 
   public int numsales; 
   public decimal totalsales; 
} 
  
QueryResult[] results = CSDatabase.RunQuery<QueryResult>( 
                                        @"select name,count(*),sum(s.total) 
                                            from salesperson sp 
                                            inner join sales s on s.salespersonid=sp.id 
                                            group by sp.name"); 

// retrieve a scalar value (for example, the total number of customers) 
  
int numCustomers = CSDatabase.GetScalar<int>("select count(*) from customer");

 

So if you’re not that crazy about ORM’s, or you want to execute some very specific SQL satements, you can do that very easily on Windows Phone with a local SQLite db.

Shipping a SQLite database file with your app

If you have an existing SQLite database file and you want to ship it with your application, you’ll have to make it available in Isolated Storage. This is pretty straightforward, but not always obvious:

First, add your database file to your Visual Studio project and set the build action to “Content” (also leave “Do not copy”).

Then add the following piece of code before calling CSConfig.SetDB(…):

string fn = "mydb.sqlite"; 
  
StreamResourceInfo sr = Application.GetResourceStream(new Uri(fn, UriKind.Relative)); 
  
IsolatedStorageFile iStorage = IsolatedStorageFile.GetUserStoreForApplication(); 
  
if (!iStorage.FileExists(fn)) 
{ 
   using (var outputStream = iStorage.OpenFile(fn, FileMode.CreateNew)) 
   { 
      byte[] buffer = new byte[10000]; 
  
      for(;;) 
      { 
         int read = sr.Stream.Read(buffer, 0, buffer.Length); 
  
         if (read <= 0) 
             break; 
  
         outputStream.Write(buffer, 0, read); 
      } 
   } 
} 
  
// Now you can use your database 
  
CSConfig.SetDB(fn);

 

Having the possibility to ship a SQLite database file with your app is especially useful if you want to share a pre-built database with other mobile platforms, since both iPhone and Android have built-in support for SQLite.

In a next post, I will show how to use the ORM features of CoolStorage.

And while you’re at it, you might as well use MonoTouch (and CoolStorage) for building the iPhone version of your app, allowing you to reuse all of your business logic and data layer code.