Sqlite 操作類代碼

1. ADO.NET Provider For SQLite.
　　ADO.NET 提供者是香港(貌似)一個公司提供的.項目地址見:http://sourceforge.net/projects/sqlite-dotnet2
　　2. 對SQLite.NET的封裝,提供一個簡單的操作協助類.
SQLiteHelper
複製代碼 代碼如下:
/**//**
* 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>
/// 返回資料庫連結字串
/// </summary>
public static string ConnString
{
get { return connString; }
}
/**//// <summary>
/// 執行SQL語句,返回受影響的行數
/// </summary>
/// <param name="cmdText">需要被執行的SQL語句</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string cmdText)
{
return ExecuteNonQuery(ConnString, cmdText);
}
/**//// <summary>
/// 執行帶有事務的SQL語句
/// </summary>
/// <param name="trans">事務</param>
/// <param name="cmdText">SQL語句</param>
/// <returns>受影響的行數</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>
/// 執行SQL語句,返回受影響的行數
/// </summary>
/// <param name="connString">連接字串</param>
/// <param name="cmdText">SQL語句</param>
/// <param name="parameters">SQL的參數</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string connString, string cmdText, params SQLiteParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
return ExecuteNonQuery(conn, cmdText, parameters);
}
}
/**//// <summary>
/// 執行SQL語句,返回受影響的行數
/// </summary>
/// <param name="connection">資料庫連結</param>
/// <param name="cmdText">SQL語句</param>
/// <param name="parameters">參數</param>
/// <returns>受影響的行數</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>
/// 執行查詢,並返回結果集的第一行的第一列.其他所有的行和列被忽略.
/// </summary>
/// <param name="cmdText">SQL 陳述式</param>
/// <returns>第一行的第一列的值</returns>
public static object ExecuteScalar(string cmdText)
{
return ExecuteScalar(ConnString, cmdText);
}
/**//// <summary>
/// 執行查詢,並返回結果集的第一行的第一列.其他所有的行和列被忽略.
/// </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>
/// 執行查詢,並返回結果集的第一行的第一列.其他所有的行和列被忽略.
/// </summary>
/// <param name="connection">資料庫連結</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>
/// 執行SQL語句,返回結果集的DataReader
/// </summary>
/// <param name="cmdText">SQL語句</param>
/// <param name="parameters">參數</param>
/// <returns>結果集的DataReader</returns>
public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] parameters)
{
return ExecuteReader(ConnString, cmdText, parameters);
}
/**//// <summary>
/// 執行SQL語句,返回結果集的DataReader
/// </summary>
/// <param name="connString">連接字串</param>
/// <param name="cmdText">SQL語句</param>
/// <param name="parameters">參數</param>
/// <returns>結果集的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>
/// 預先處理Command對象,資料庫連結,事務,需要執行的對象,參數等的初始化
/// </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">參數執行個體</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. 增刪改查:
複製代碼 代碼如下:
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效能絕對強悍.四表串連查詢,查詢200次,SQL CE需要44秒(with index),SQLite只需要3-6秒(with index).
插入,刪除更新效能參見http://www.cnblogs.com/egmkang/archive/2009/06/06/1497678.html
　　PS:最近發現執行sql的時候,最好使用單一的長串連,而不是ConnectionString.原因很簡單,嵌入式資料庫沒有串連池技術,
在進行資料庫查詢中的連結的開啟關閉費用相對來說比較高昂.這一點在寫程式的時候注意以下.
　　還有,有關二進位資源,需要及時釋放,例如SqlCommand,這些在寫Web 程式的時候體驗不是很大,畢竟那種環境擁有大量
的記憶體,GC的效率又是比較高.