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!

推薦閱讀: