C# SQLite資料庫訪問封裝類

衣舞晨風發表於2015-10-26

在客戶端配置檔案<configuration>節點下，新增：

<connectionStrings>
	<add name="localdb" connectionString="Data Source=config/local.db;Version=3;UseUTF16Encoding=True;" providerName="System.Data.SQLite.SQLiteFactory"/>
</connectionStrings>

其中【localdb】是本地SQLite資料庫的名稱，【config/local.db】是在當前程式執行目錄下SQLite資料庫位置

C# SQLite資料庫訪問封裝類程式碼：

 /// <summary>
    /// 本類為SQLite資料庫幫助靜態類,使用時只需直接呼叫即可,無需例項化
    /// </summary>
    public static class SQLiteHelper
    {
        // Application.StartupPath
        public static string LocalDbConnectionString = ConfigurationManager.ConnectionStrings["localdb"].ConnectionString;

        #region ExecuteNonQuery
        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="cmd">SqlCommand物件</param>
        /// <returns>所受影響的行數</returns>
        public static int ExecuteNonQuery(string connectionString, SQLiteCommand cmd)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <returns>所受影響的行數</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <param name="cmdParms">SQL引數物件</param>
        /// <returns>所受影響的行數</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列資料
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="cmd">SqlCommand物件</param>
        /// <returns>查詢所得的第1行第1列資料</returns>
        public static object ExecuteScalar(string connectionString, SQLiteCommand cmd)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列資料
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <returns>查詢所得的第1行第1列資料</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 執行資料庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列資料
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <param name="cmdParms">SQL引數物件</param>
        /// <returns>查詢所得的第1行第1列資料</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 執行資料庫查詢，返回SqlDataReader物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="cmd">SqlCommand物件</param>
        /// <returns>SqlDataReader物件</returns>
        public static DbDataReader ExecuteReader(string connectionString, SQLiteCommand cmd)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }

        /// <summary>
        /// 執行資料庫查詢，返回SqlDataReader物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <returns>SqlDataReader物件</returns>
        public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }

        /// <summary>
        /// 執行資料庫查詢，返回SqlDataReader物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <param name="cmdParms">SQL引數物件</param>
        /// <returns>SqlDataReader物件</returns>
        public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 執行資料庫查詢，返回DataSet物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="cmd">SqlCommand物件</param>
        /// <returns>DataSet物件</returns>
        public static DataSet ExecuteDataSet(string connectionString, SQLiteCommand cmd)
        {
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, 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;
        }

        /// <summary>
        /// 執行資料庫查詢，返回DataSet物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <returns>DataSet物件</returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType)
        {
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 執行資料庫查詢，返回DataSet物件
        /// </summary>
        /// <param name="connectionString">連線字串</param>
        /// <param name="commandText">執行語句或儲存過程名</param>
        /// <param name="commandType">執行型別</param>
        /// <param name="cmdParms">SQL引數物件</param>
        /// <returns>DataSet物件</returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
            return ds;
        }
        #endregion

        #region 通用分頁查詢方法
        /// <summary>
        /// 通用分頁查詢方法
        /// </summary>
        /// <param name="connString">連線字串</param>
        /// <param name="tableName">表名</param>
        /// <param name="strColumns">查詢欄位名</param>
        /// <param name="strWhere">where條件</param>
        /// <param name="strOrder">排序條件</param>
        /// <param name="pageSize">每頁資料數量</param>
        /// <param name="currentIndex">當前頁數</param>
        /// <param name="recordOut">資料總量</param>
        /// <returns>DataTable資料表</returns>
        public static DataTable SelectPaging(string connString, string tableName, string strColumns, string strWhere, string strOrder, int pageSize, int currentIndex, out int recordOut)
        {
            DataTable dt = new DataTable();
            recordOut = Convert.ToInt32(ExecuteScalar(connString, "select count(*) from " + tableName, CommandType.Text));
            string pagingTemplate = "select {0} from {1} where {2} order by {3} limit {4} offset {5} ";
            int offsetCount = (currentIndex - 1) * pageSize;
            string commandText = String.Format(pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize.ToString(), offsetCount.ToString());
            using (DbDataReader reader = ExecuteReader(connString, commandText, CommandType.Text))
            {
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            return dt;
        }
        #endregion

        #region  預處理Command物件,資料庫連結,事務,需要執行的物件,引數等的初始化
        /// <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)
        {

            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);
            }
        }

        #endregion
    }

使用demo：

 /// <summary>
        /// 獲取資料庫關鍵字資訊
        /// </summary>
        /// <param name="category">分類</param>
        /// <param name="versions">版本</param>
        /// <returns></returns>
        private DataSet GetSystemDataBaseKeyWords(string category, string versions)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Keywords , Versions , Type , Description , Category  , Id , Extends ");
            sql.Append(" FROM A_DataBaseKeyWords ");
            sql.AppendFormat(" WHERE 1={0} ", "1");
            if (!String.IsNullOrEmpty(category))
            {
                sql.AppendFormat(" AND Category='{0}'", category);
            }
            if (!String.IsNullOrEmpty(versions))
            {
                sql.AppendFormat(" AND Versions='{0}'", versions);
            }
            return SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sql.ToString(), CommandType.Text);
        }

小注：

為了遮蔽32與64位系統問題，請使用改SQLite的dll，下載地址：點選開啟連結

作者：jiankunking 出處：http://blog.csdn.net/jiankunking

C#訪問SQLite資料庫
2009-03-10
C#SQLite資料庫
封裝ADO訪問資料庫的兩個類 (轉)
2007-12-08
封裝資料庫
【tronic】為ASP.NET封裝的SQL資料庫訪問類
2008-06-02
ASP.NET封裝SQL資料庫
SQLite Helper類，基於.net c#的SQLite資料庫操作類
2012-10-27
SQLiteC#資料庫
Android 封裝AsyncTask操作Sqlite資料庫
2018-04-10
Android封裝SQLite資料庫
Xamarin SQLite教程資料庫訪問與生成
2018-06-20
SQLite資料庫
C# SQLiteHelper類似SqlHelper類實現存取Sqlite資料庫
2012-10-30
C#SQLite資料庫
C#訪問MySQL資料庫
2015-06-09
C#MySql資料庫
c#封裝、訪問修飾符
2020-09-25
C#封裝
sqlite封裝
2020-12-04
SQLite封裝
c#封裝DBHelper類
2018-09-27
C#封裝
Mysql資料庫學習（四）：常用Mysql C API 介紹和使用、封裝一個訪問Mysql資料庫的類MysqlDB
2014-02-28
MySql資料庫API封裝
Android 原生 SQLite 資料庫的一次封裝實踐
2020-08-10
AndroidSQLite資料庫封裝
使用C++/CLI呼叫C#封裝類庫
2020-09-29
C++C#封裝
初探資料庫通用程式碼庫的封裝（C#版）
2014-08-12
資料庫封裝C#
php封裝db 類連線sqlite3
2019-02-16
PHP封裝SQLite
c#資料操作：資料庫訪問和檔案操作
2012-07-28
C#資料庫
c# Lambda操作類封裝
2018-10-01
C#封裝
釋出一個.NET資料庫訪問類
2006-05-10
資料庫
C#快速搭建模型資料庫SQLite操作
2019-07-22
C#模型資料庫SQLite
Sqlite封裝1-基本封裝-SqliteTool
2017-08-02
SQLite封裝
FMDB 二次封裝工具類，讓你快速學會封裝，整合資料庫
2016-09-29
封裝資料庫
C#與資料庫訪問技術總結（十七）
2014-12-01
C#資料庫
C#與資料庫訪問技術總結（十八）
2014-12-01
C#資料庫
C++ 訪問說明符詳解：封裝資料，控制訪問，提升安全性
2024-04-23
C++封裝
如何封裝資料庫操作
2004-09-21
封裝資料庫
基於.NET C#的 sqlite 資料庫 ORM 【Easyliter】
2015-05-13
C#SQLite資料庫ORM
Java類的設計和封裝及類成員的訪問控制
2021-01-09
Java封裝
Android實用的SQLite資料庫工具類
2017-10-19
AndroidSQLite資料庫
JDBC資料庫訪問
2020-11-01
JDBC資料庫
資料庫訪問抽象類實現專案資料庫靈活切換
2013-06-18
資料庫抽象
清除快取資料--工具類封裝
2016-10-19
快取封裝
Oracle,SqlServer,Access資料庫通用訪問類設計(轉)
2008-01-05
OracleSQLServer資料庫
jsp呼叫javabean封裝資料庫的問題，急！
2003-07-15
JSJavaBean封裝資料庫
jsp呼叫javabean，javabean封裝資料庫的問題
2003-07-14
JSJavaBean封裝資料庫
Oracle資料庫訪問控制
2011-03-23
Oracle資料庫
異構資料庫訪問
2003-11-25
資料庫
C#與資料庫訪問技術總結（七）綜合示例
2014-10-31
C#資料庫

C# SQLite資料庫 訪問封裝類

相關文章

C# SQLite資料庫訪問封裝類