程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C# SQLite數據庫訪問封裝類

C# SQLite數據庫訪問封裝類

編輯：C#入門知識

C# SQLite數據庫訪問封裝類

在客戶端配置文件節點下，添加：

其中【localdb】是本地SQLite數據庫的名稱，【config/local.db】是在當前程序運行目錄下SQLite數據庫位置

C# SQLite數據庫訪問封裝類代碼：

 /// 
    /// 本類為SQLite數據庫幫助靜態類,使用時只需直接調用即可,無需實例化
    /// 
    public static class SQLiteHelper
    {
        // Application.StartupPath
        public static string LocalDbConnectionString = ConfigurationManager.ConnectionStrings[localdb].ConnectionString;

        #region ExecuteNonQuery
        /// 
        /// 執行數據庫操作(新增、更新或刪除)
        /// 
        ///連接字符串
        ///SqlCommand對象
        /// 所受影響的行數
        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;
        }

        /// 
        /// 執行數據庫操作(新增、更新或刪除)
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        /// 所受影響的行數
        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;
        }

        /// 
        /// 執行數據庫操作(新增、更新或刪除)
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        ///SQL參數對象
        /// 所受影響的行數
        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
        /// 
        /// 執行數據庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列數據
        /// 
        ///連接字符串
        ///SqlCommand對象
        /// 查詢所得的第1行第1列數據
        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;
        }

        /// 
        /// 執行數據庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列數據
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        /// 查詢所得的第1行第1列數據
        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;
        }

        /// 
        /// 執行數據庫操作(新增、更新或刪除)同時返回執行後查詢所得的第1行第1列數據
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        ///SQL參數對象
        /// 查詢所得的第1行第1列數據
        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
        /// 
        /// 執行數據庫查詢，返回SqlDataReader對象
        /// 
        ///連接字符串
        ///SqlCommand對象
        /// SqlDataReader對象
        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;
        }

        /// 
        /// 執行數據庫查詢，返回SqlDataReader對象
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        /// SqlDataReader對象
        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;
        }

        /// 
        /// 執行數據庫查詢，返回SqlDataReader對象
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        ///SQL參數對象
        /// SqlDataReader對象
        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
        /// 
        /// 執行數據庫查詢，返回DataSet對象
        /// 
        ///連接字符串
        ///SqlCommand對象
        /// DataSet對象
        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;
        }

        /// 
        /// 執行數據庫查詢，返回DataSet對象
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        /// DataSet對象
        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;
        }

        /// 
        /// 執行數據庫查詢，返回DataSet對象
        /// 
        ///連接字符串
        ///執行語句或存儲過程名
        ///執行類型
        ///SQL參數對象
        /// DataSet對象
        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 通用分頁查詢方法
        /// 
        /// 通用分頁查詢方法
        /// 
        ///連接字符串
        ///表名
        ///查詢字段名
        ///where條件
        ///排序條件
        ///每頁數據數量
        ///當前頁數
        ///數據總量
        /// DataTable數據表
        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對象,數據庫鏈接,事務,需要執行的對象,參數等的初始化
        /// 
        /// 預處理Command對象,數據庫鏈接,事務,需要執行的對象,參數等的初始化
        /// 
        ///Command對象
        ///Connection對象
        ///Transcation對象
        ///是否使用事務
        ///SQL字符串執行類型
        ///SQL Text
        ///SQLiteParameters to use in the command
        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：

 /// 
        /// 獲取數據庫關鍵字信息
        /// 
        ///分類
        ///版本
        /// 
        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);
        }