快捷導(dǎo)航

Sqlite 操作類代碼

更新時(shí)間：2009年11月15日 22:52:56 作者：

這兩天用了用SQLite,感覺比SQL CE好用多了,最主要的是性能好多了.這一點(diǎn)對手持設(shè)備來講很重要.閑話少說,下來將怎么在WM用.NET CF訪問SQLite.

1. ADO.NET Provider For SQLite.
　　ADO.NET 提供程序是香港(貌似)一個公司提供的.項(xiàng)目地址見:http://sourceforge.net/projects/sqlite-dotnet2
　　2. 對SQLite.NET的封裝,提供一個簡單的操作幫助類.
SQLiteHelper

復(fù)制代碼代碼如下:

 
/**//** 
* SQLite操作的幫助類. 
* 
* Author: egmkang.wang 
* Date: 2009-06-21 
*/ 
namespace System.Data.SQLite 
{ 
using System.Data; 
using System.Data.SQLite; 
using System.IO; 
public class SqliteHelper 
{ 
private static string pwd = "PWD"; 
private static string path = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\sqliteTest.db"; 
private static string connString = string.Format("Data Source =\"{0}\"", path, pwd); 
/**//// <summary> 
/// 返回?cái)?shù)據(jù)庫鏈接字符串 
/// </summary> 
public static string ConnString 
{ 
get { return connString; } 
} 
/**//// <summary> 
/// 執(zhí)行SQL語句,返回受影響的行數(shù) 
/// </summary> 
/// <param name="cmdText">需要被執(zhí)行的SQL語句</param> 
/// <returns>受影響的行數(shù)</returns> 
public static int ExecuteNonQuery(string cmdText) 
{ 
return ExecuteNonQuery(ConnString, cmdText); 
} 
/**//// <summary> 
/// 執(zhí)行帶有事務(wù)的SQL語句 
/// </summary> 
/// <param name="trans">事務(wù)</param> 
/// <param name="cmdText">SQL語句</param> 
/// <returns>受影響的行數(shù)</returns> 
public static int ExecuteNonQuery(SQLiteTransaction trans, string cmdText, params SQLiteParameter[] parameters) 
{ 
int val = 0; 
using (SQLiteCommand cmd = new SQLiteCommand()) 
{ 
PrepareCommand(cmd, (SQLiteConnection)trans.Connection, trans, cmdText, parameters); 
val = cmd.ExecuteNonQuery(); 
cmd.Parameters.Clear(); 
} 
return val; 
} 
/**//// <summary> 
/// 執(zhí)行SQL語句,返回受影響的行數(shù) 
/// </summary> 
/// <param name="connString">連接字符串</param> 
/// <param name="cmdText">SQL語句</param> 
/// <param name="parameters">SQL的參數(shù)</param> 
/// <returns>受影響的行數(shù)</returns> 
public static int ExecuteNonQuery(string connString, string cmdText, params SQLiteParameter[] parameters) 
{ 
using (SQLiteConnection conn = new SQLiteConnection(connString)) 
{ 
return ExecuteNonQuery(conn, cmdText, parameters); 
} 
} 
/**//// <summary> 
/// 執(zhí)行SQL語句,返回受影響的行數(shù) 
/// </summary> 
/// <param name="connection">數(shù)據(jù)庫鏈接</param> 
/// <param name="cmdText">SQL語句</param> 
/// <param name="parameters">參數(shù)</param> 
/// <returns>受影響的行數(shù)</returns> 
public static int ExecuteNonQuery(SQLiteConnection connection, string cmdText, params SQLiteParameter[] parameters) 
{ 
int val = 0; 
using (SQLiteCommand cmd = new SQLiteCommand()) 
{ 
PrepareCommand(cmd, connection, null, cmdText, parameters); 
val = cmd.ExecuteNonQuery(); 
cmd.Parameters.Clear(); 
} 
return val; 
} 
/**//// <summary> 
/// 執(zhí)行查詢,并返回結(jié)果集的第一行的第一列.其他所有的行和列被忽略. 
/// </summary> 
/// <param name="cmdText">SQL 語句</param> 
/// <returns>第一行的第一列的值</returns> 
public static object ExecuteScalar(string cmdText) 
{ 
return ExecuteScalar(ConnString, cmdText); 
} 
/**//// <summary> 
/// 執(zhí)行查詢,并返回結(jié)果集的第一行的第一列.其他所有的行和列被忽略. 
/// </summary> 
/// <param name="connString">連接字符串</param> 
/// <param name="cmdText">SQL 語句</param> 
/// <returns>第一行的第一列的值</returns> 
public static object ExecuteScalar(string connString, string cmdText) 
{ 
using (SQLiteConnection conn = new SQLiteConnection(connString)) 
{ 
return ExecuteScalar(conn, cmdText); 
} 
} 
/**//// <summary> 
/// 執(zhí)行查詢,并返回結(jié)果集的第一行的第一列.其他所有的行和列被忽略. 
/// </summary> 
/// <param name="connection">數(shù)據(jù)庫鏈接</param> 
/// <param name="cmdText">SQL 語句</param> 
/// <returns>第一行的第一列的值</returns> 
public static object ExecuteScalar(SQLiteConnection connection, string cmdText) 
{ 
object val; 
using (SQLiteCommand cmd = new SQLiteCommand()) 
{ 
PrepareCommand(cmd, connection, null, cmdText); 
val = cmd.ExecuteScalar(); 
} 
return val; 
} 
/**//// <summary> 
/// 執(zhí)行SQL語句,返回結(jié)果集的DataReader 
/// </summary> 
/// <param name="cmdText">SQL語句</param> 
/// <param name="parameters">參數(shù)</param> 
/// <returns>結(jié)果集的DataReader</returns> 
public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] parameters) 
{ 
return ExecuteReader(ConnString, cmdText, parameters); 
} 
/**//// <summary> 
/// 執(zhí)行SQL語句,返回結(jié)果集的DataReader 
/// </summary> 
/// <param name="connString">連接字符串</param> 
/// <param name="cmdText">SQL語句</param> 
/// <param name="parameters">參數(shù)</param> 
/// <returns>結(jié)果集的DataReader</returns> 
public static SQLiteDataReader ExecuteReader(string connString, string cmdText, params SQLiteParameter[] parameters) 
{ 
SQLiteConnection conn = new SQLiteConnection(connString); 
SQLiteCommand cmd = new SQLiteCommand(); 
try 
{ 
PrepareCommand(cmd, conn, null, cmdText, parameters); 
SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
cmd.Parameters.Clear(); 
return rdr; 
} 
catch 
{ 
conn.Close(); 
throw; 
} 
} 
/**//// <summary> 
/// 預(yù)處理Command對象,數(shù)據(jù)庫鏈接,事務(wù),需要執(zhí)行的對象,參數(shù)等的初始化 
/// </summary> 
/// <param name="cmd">Command對象</param> 
/// <param name="conn">Connection對象</param> 
/// <param name="trans">Transcation對象</param> 
/// <param name="cmdText">SQL Text</param> 
/// <param name="parameters">參數(shù)實(shí)例</param> 
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, params SQLiteParameter[] parameters) 
{ 
if (conn.State != ConnectionState.Open) 
conn.Open(); 
cmd.Connection = conn; 
cmd.CommandText = cmdText; 
if (trans != null) 
cmd.Transaction = trans; 
if (null != parameters && parameters.Length > 0) 
{ 
cmd.Parameters.AddRange(parameters); 
} 
} 
} 
} 

　　3. 增刪改查:

復(fù)制代碼代碼如下:

 
Insert,Delete,Update 
const string s_AddressTreeIntoSQLite = "Insert into [AddressTree] ([Id],[ItemType],[ParentId],[Name]) values (@Id,@ItemType,@ParentId,@Name);"; 
SqliteHelper.ExecuteNonQuery(tran, s_AddressTreeIntoSQLite, 
//new SQLiteParameter[] here 
); 
Select 
const string s_AddresTreeFromSqlCE = "Select [Id],[ItemType],[ParentId],[Name] From [AddressTree];"; 
using (SqlCeDataReader rdr = SqlCeHelper.ExecuteReader(s_AddresTreeFromSqlCE )) 
{ 
while (rdr.Read()) 
{ 
//Read Data Here 
} 
} 

　　4. 其他
　　SQLite性能絕對強(qiáng)悍.四表連接查詢,查詢200次,SQL CE需要44秒(with index),SQLite只需要3-6秒(with index).
插入,刪除更新性能參見http://www.cnblogs.com/egmkang/archive/2009/06/06/1497678.html
　　PS:最近發(fā)現(xiàn)執(zhí)行sql的時(shí)候,最好使用單一的長連接,而不是ConnectionString.原因很簡單,嵌入式數(shù)據(jù)庫沒有連接池技術(shù),
在進(jìn)行數(shù)據(jù)庫查詢中的鏈接的打開關(guān)閉費(fèi)用相對來說比較高昂.這一點(diǎn)在寫程序的時(shí)候注意以下.
　　還有,有關(guān)二進(jìn)制資源,需要及時(shí)釋放,例如SqlCommand,這些在寫Web 程序的時(shí)候體驗(yàn)不是很大,畢竟那種環(huán)境擁有大量
的內(nèi)存,GC的效率又是比較高.