在客戶端配置文件
其中【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); }
