C# SQLite庫使用技巧
1、SQLite介紹
SQLite,是一款輕型的數據庫,是遵守的ACID關系型數據庫管理系統,它包含在一個相對小的C庫中。它的設計目標嵌入式是的,而且已經在很多中使用瞭它,它占用資源非常的低,在嵌入式設備中,可能隻需要幾百K的內存就夠瞭。它能夠支持Windows/Linux/Unix等等主流的操作系統,同時能夠跟很多程序語言相結合,比如 Tcl、C#、PHP、Java等。
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite是一個開源、免費的小型RDBMS(關系型數據庫),能獨立運行、無服務器、零配置、支持事物,用C實現,內存占用較小,支持絕大數的SQL92標準。
SQLite數據庫官方主頁:http://www.sqlite.org/index.html
2、C#下調用SQLite數據庫
在NuGet程序包內,搜索System.Data.Sqlite,安裝Sqlite類庫
3、在C#程序內添加SqliteHelper
sqliteHelper中主要用到2個方法:
a、ExecuteNonQuery 執行Insert,Update、Delete、創建庫等操作,返回值是數據庫影響的行數
b、ExecuteDataSet執行Select操作,返回查詢數據集
public class SQLiteHelper { public static string ConnectionString = "Data Source =" + Environment.CurrentDirectory + @"\database.db" + ";Pooling = true; FailIfMissing = true"; /// <summary> /// 執行數據庫操作(新增、更新或刪除) /// </summary> /// <param name="cmdstr">連接字符串</param> /// <param name="cmdParms">SqlCommand對象</param> /// <returns>受影響的行數</returns> public int ExecuteNonQuery(string cmdstr, params SQLiteParameter[] cmdParms) { int result = 0; using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { SQLiteTransaction trans = null; SQLiteCommand cmd = new SQLiteCommand(cmdstr); PrepareCommand(cmd, conn, ref trans, true, cmd.CommandType, cmd.CommandText, cmdParms); try { result = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } } return result; } /// <summary> /// 預處理Command對象,數據庫鏈接,事務,需要執行的對象,參數等的初始化 /// </summary> /// <param name="cmd">Command對象</param> /// <param name="conn">Connection對象</param> /// <param name="trans">Transcation對象</param> /// <param name="useTrans">是否使用事務</param> /// <param name="cmdType">SQL字符串執行類型</param> /// <param name="cmdText">SQL Text</param> /// <param name="cmdParms">SQLiteParameters to use in the command</param> private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) { try { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (useTrans) { trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = trans; } cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SQLiteParameter parm in cmdParms) cmd.Parameters.Add(parm); } } catch { } } /// <summary> /// 數據庫查詢 /// </summary> /// <param name="cmdstr">sql語句</param> /// <param name="tableName">表名</param> /// <returns>DataSet對象</returns> public DataSet ExecuteDataSet(string cmdstr) { DataSet ds = new DataSet(); SQLiteConnection conn = new SQLiteConnection(ConnectionString); SQLiteTransaction trans = null; SQLiteCommand cmd = new SQLiteCommand(cmdstr); PrepareCommand(cmd, conn, ref trans, false, cmd.CommandType, cmd.CommandText); try { SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd); sda.Fill(ds); } catch (Exception ex) { throw ex; } finally { if (cmd.Connection != null) { if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } } } return ds; }
4、Sqlite部分技巧
a、SQLiteConnection類的CreateFile方法,在程序內動態創建數據庫文件,通過下面的方法即可創建出Analysis.db名稱的數據庫
/// <summary> /// 數據庫路徑 /// </summary> private static string databasepath = AppDomain.CurrentDomain.BaseDirectory + "DataBase\\"; /// <summary> /// 數據庫名稱 /// </summary> private const string databasename = "Analysis.db"; /// <summary> /// 創建數據庫 /// </summary> public static void CreateDataBase() { try { if (!File.Exists(databasepath + databasename)) { if (!Directory.Exists(databasepath)) Directory.CreateDirectory(databasepath); SQLiteConnection.CreateFile(databasepath + databasename); LogHelper.Info("創建數據庫:" + databasename + "成功!"); } } catch (Exception ex) { LogHelper.Debug(ex); } }
b、在寫入高頻數據的時候,需要使用事務,如果反復進行(打開->插入>關閉)操作,sqlite效率1秒鐘插入也就2條,使用程序進行插入就會發現輸入的頻率遠低於獲取到的數據,大量的數據被緩存到內存中,為瞭處理入庫的速度慢,就要用到事務,事務流程:
①打開連接
②開始事務
③循環在內存中執行插入命令
④提交事務寫入本地文件,如果出錯回滾事務
⑤關閉連接
代碼見下圖,開始事務後通過SQLiteCommand的ExecuteNonQuery()方法進行內存提交
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand()) { DbTransaction trans = null; try { cmd.Connection = conn; conn.Open(); //開啟事務 using (trans = conn.BeginTransaction()) { while (_list.Count > 0) { GpsDataClass _gps = _list[0]; try { if (_gps != null) { SQLiteHelper sh = new SQLiteHelper(cmd); var dic = new Dictionary<string, object>(); dic["CarPlate"] = _gps.CarPlate; dic["CarIpAddress"] = _gps.CarIpAddress; dic["PosX1"] = _gps.PosX1; dic["PosY1"] = _gps.PosY1; dic["PosZ1"] = _gps.PosZ1; dic["Heading1"] = _gps.Heading1; dic["PosStatus1"] = _gps.PosStatus1; dic["NumF1"] = _gps.NumF1; dic["NumB1"] = _gps.NumB1; dic["PosX2"] = _gps.PosX2; dic["PosY2"] = _gps.PosY2; dic["PosZ2"] = _gps.PosZ2; dic["Heading2"] = _gps.Heading2; dic["PosStatus2"] = _gps.PosStatus2; dic["NumF2"] = _gps.NumF2; dic["NumB2"] = _gps.NumB2; dic["Speed"] = _gps.Speed; dic["Signal"] = _gps.Signal; dic["NowTime"] = _gps.NowTime; sh.Insert("GpsRecord", dic); _list.RemoveAt(0); } } catch (Exception ex) { LogHelper.Debug(ex); } } trans.Commit(); } } catch (Exception ex) { trans.Rollback(); LogHelper.Debug(ex); } conn.Close(); } }
到此這篇關於C# SQLite庫使用的文章就介紹到這瞭,更多相關C# SQLite庫使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- C#操作SQLite實現數據的增刪改查
- 關於Java 項目封裝sqlite連接池操作持久化數據的方法
- C#中Dapper的使用教程
- C#從前面或後面按指定數量刪除字符串
- Unity實現紅酒識別的示例代碼