微软WP7本机数据库解决方案之SQLite(1)(3)

列表1：更新版本的工具类DBHelper

//others omitted…  
using SQLiteClient;  
using System.Linq;  
using System.IO.IsolatedStorage;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
namespace WP7SQLiteClient.Helpers  
{  
   public class DBHelper  
   {  
      private String _dbName;  
      private SQLiteConnection db = null;  
      public DBHelper(String assemblyName, String dbName)  
      {  
         IsolatedStorageFile store =IsolatedStorageFile.GetUserStoreForApplication();  
         if (!store.FileExists(dbName))  
         {  
            CopyFromContentToStorage(assemblyName, dbName);  
         }  
         _dbName = dbName;  
      }  
      ~DBHelper()  
      {  
         Close();  
      }  
      private void Open()  
      {  
         if (db == null)  
         {  
            db = new SQLiteConnection(_dbName);  
            db.Open();  
         }  
      }  
      private void Close()  
      {  
         if (db != null)  
         {  
            db.Dispose();  
            db = null;  
         }  
      }  
      //Insert operation  
      public int Insert(T obj, string statement) where T : new()  
      {  
         try 
         {  
            Open();  
            SQLiteCommand cmd = db.CreateCommand(statement);  
            int rec = cmd.ExecuteNonQuery(obj);  
            return rec;  
         }  
         catch (SQLiteException ex)  
         {  
            System.Diagnostics.Debug.WriteLine("Insert failed: " + ex.Message);  
            throw ex;  
         }  
      }  
      // Delete operation  
      public void Delete(string statement) where T : new()  
      {  
         try 
         {  
            Open();  
            SQLiteCommand cmd = db.CreateCommand(statement);  
            cmd.ExecuteNonQuery();  
         }  
         catch (SQLiteException ex)  
         {  
            System.Diagnostics.Debug.WriteLine("Deletion failed: " + ex.Message);  
            throw ex;  
         }  
      }  
      //Query operation  
      public List SelectList(String statement) where T : new()  
      {  
         Open();  
         SQLiteCommand cmd = db.CreateCommand(statement);  
         var lst = cmd.ExecuteQuery();  
         return lst.ToList();  
      }  
      public ObservableCollection SelectObservableCollection(String statement)  
      where T : new()  
      {  
         List lst = SelectList(statement);  
         ObservableCollection oc = new ObservableCollection();  
         foreach (T item in lst)  
         {  
            oc.Add(item);  
         }  
         return oc;  
      }  
      private void CopyFromContentToStorage(String assemblyName,String dbName)  
      {  
         IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication();  
         System.IO.Stream src = Application.GetResourceStream(  
                                new Uri("/" + assemblyName + ";component/" + dbName,UriKind.Relative)).Stream;  
         IsolatedStorageFileStream dest = new IsolatedStorageFileStream(dbName,  
                                          System.IO.FileMode.OpenOrCreate,  
                                          System.IO.FileAccess.Write, store);  
         src.Position = 0;  
         CopyStream(src, dest);  
         dest.Flush();  
         dest.Close();  
         src.Close();  
         dest.Dispose();  
      }  
      private static void CopyStream(System.IO.Stream input,IsolatedStorageFileStream output)  
      {  
         byte[] buffer = new byte[32768];  
         long TempPos = input.Position;  
         int readCount;  
         do 
         {  
            readCount = input.Read(buffer, 0, buffer.Length);  
            if (readCount > 0)  
            {  
               output.Write(buffer, 0, readCount);  
            }  
         } while (readCount > 0);  
         input.Position = TempPos;  
      }  
   }  
} 

顺便说一句，对于上面这个帮助类我也没有提供细致的优化编码。希望读者根据您的相关工作能够继续进行这项工作(例如提供更好的泛型化的CRUD支持)并分享给广大网友。简言之，我主要是增加了插入和删除方法。上面的代码中最引起您注意是地方一定是方法CopyFromContentToStorage，正是借助这个方法我们实现了上述目标-建立起SQLite数据库与独立存储的关系。

三、小结

本文中简要介绍了Sqlite Client for Windows Phone的主要功能及相关的辅助开发工具。在接下来的第二篇文章中，我们将具体构建一个简单的Windows Phone 7客户端应用程序，当然要涉及到Sqlite Client for Windows Phone的基本编程技巧。