日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無先后，達(dá)者為師

網(wǎng)站首頁 編程語言 正文

C#?SQLite庫使用技巧_C#教程

作者：程序猿evint ? 更新時(shí)間： 2022-03-25 編程語言

1、SQLite介紹

SQLite，是一款輕型的數(shù)據(jù)庫，是遵守的ACID關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，它包含在一個(gè)相對(duì)小的C庫中。它的設(shè)計(jì)目標(biāo)嵌入式是的，而且已經(jīng)在很多中使用了它，它占用資源非常的低，在嵌入式設(shè)備中，可能只需要幾百K的內(nèi)存就夠了。它能夠支持Windows/Linux/Unix等等主流的操作系統(tǒng)，同時(shí)能夠跟很多程序語言相結(jié)合，比如 Tcl、C#、PHP、Java等。

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite是一個(gè)開源、免費(fèi)的小型RDBMS(關(guān)系型數(shù)據(jù)庫)，能獨(dú)立運(yùn)行、無服務(wù)器、零配置、支持事物，用C實(shí)現(xiàn)，內(nèi)存占用較小，支持絕大數(shù)的SQL92標(biāo)準(zhǔn)。

SQLite數(shù)據(jù)庫官方主頁：http://www.sqlite.org/index.html

2、C#下調(diào)用SQLite數(shù)據(jù)庫

在NuGet程序包內(nèi)，搜索System.Data.Sqlite,安裝Sqlite類庫

3、在C#程序內(nèi)添加SqliteHelper

sqliteHelper中主要用到2個(gè)方法：

? a、ExecuteNonQuery 執(zhí)行Insert，Update、Delete、創(chuàng)建庫等操作，返回值是數(shù)據(jù)庫影響的行數(shù)

? b、ExecuteDataSet執(zhí)行Select操作，返回查詢數(shù)據(jù)集

    public class SQLiteHelper
    {
        public static string ConnectionString = "Data Source =" + Environment.CurrentDirectory + @"\database.db" + ";Pooling = true; FailIfMissing = true";
 
        /// <summary>
        /// 執(zhí)行數(shù)據(jù)庫操作(新增、更新或刪除)
        /// </summary>
        /// <param name="cmdstr">連接字符串</param>
        /// <param name="cmdParms">SqlCommand對(duì)象</param>
        /// <returns>受影響的行數(shù)</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>
        /// 預(yù)處理Command對(duì)象,數(shù)據(jù)庫鏈接,事務(wù),需要執(zhí)行的對(duì)象,參數(shù)等的初始化
        /// </summary>
        /// <param name="cmd">Command對(duì)象</param>
        /// <param name="conn">Connection對(duì)象</param>
        /// <param name="trans">Transcation對(duì)象</param>
        /// <param name="useTrans">是否使用事務(wù)</param>
        /// <param name="cmdType">SQL字符串執(zhí)行類型</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>
        /// 數(shù)據(jù)庫查詢
        /// </summary>
        /// <param name="cmdstr">sql語句</param>
        /// <param name="tableName">表名</param>
        /// <returns>DataSet對(duì)象</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方法，在程序內(nèi)動(dòng)態(tài)創(chuàng)建數(shù)據(jù)庫文件，通過下面的方法即可創(chuàng)建出Analysis.db名稱的數(shù)據(jù)庫

        /// <summary>
        /// 數(shù)據(jù)庫路徑
        /// </summary>
        private static string databasepath = AppDomain.CurrentDomain.BaseDirectory + "DataBase\\";
        /// <summary>
        /// 數(shù)據(jù)庫名稱
        /// </summary>
        private const string databasename = "Analysis.db";
        /// <summary>
        /// 創(chuàng)建數(shù)據(jù)庫
        /// </summary>
        public static void CreateDataBase()
        {
            try
            {
                if (!File.Exists(databasepath + databasename))
                {
                    if (!Directory.Exists(databasepath))
                        Directory.CreateDirectory(databasepath);
                    SQLiteConnection.CreateFile(databasepath + databasename);
                    LogHelper.Info("創(chuàng)建數(shù)據(jù)庫:" + databasename + "成功！");
                }
            }
            catch (Exception ex)
            {
                LogHelper.Debug(ex);
            }
        }

? b、在寫入高頻數(shù)據(jù)的時(shí)候，需要使用事務(wù)，如果反復(fù)進(jìn)行（打開->插入>關(guān)閉）操作，sqlite效率1秒鐘插入也就2條，使用程序進(jìn)行插入就會(huì)發(fā)現(xiàn)輸入的頻率遠(yuǎn)低于獲取到的數(shù)據(jù)，大量的數(shù)據(jù)被緩存到內(nèi)存中，為了處理入庫的速度慢，就要用到事務(wù)，事務(wù)流程：

? ? ①打開連接

? ? ②開始事務(wù)

? ? ③循環(huán)在內(nèi)存中執(zhí)行插入命令

? ? ④提交事務(wù)寫入本地文件，如果出錯(cuò)回滾事務(wù)

? ? ⑤關(guān)閉連接

代碼見下圖，開始事務(wù)后通過SQLiteCommand的ExecuteNonQuery()方法進(jìn)行內(nèi)存提交

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    DbTransaction trans = null;
                    try
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        //開啟事務(wù)
                        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();
                }
            }

原文鏈接：https://blog.csdn.net/evint888/article/details/122331724

欄目分類
最近更新