C# SQLite数据库 访问封装类资料

C# SQLite数据库访问封装类C# 异常处理（Catch Throw）IL分析在客户端配置文件<configuration>节点下，添加：[html] view plain copy 在CODE上查看代码片派生到我的代码片<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数据库访问封装类代码：[csharp] view plain copy 在CODE上查看代码片派生到我的代码片/// <summary>/// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化/// </summary>public static class SQLiteHelper{// Application.StartupPathpublic 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, mandType, mandText);try{result = cmd.ExecuteNonQuery();mit();}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();mit();}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();mit();}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, mandType, mandText);try{result = cmd.ExecuteScalar();mit();}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();mit();}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();mit();}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, mandType, mandText);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 || mandText.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, mandType, mandText);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;mandText = cmdText;if (useTrans){trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);cmd.Transaction = trans;}mandType = cmdType;if (cmdParms != null){foreach (SQLiteParameter parm in cmdParms)cmd.Parameters.Add(parm);}}#endregion}使用demo：[csharp] view plain copy 在CODE上查看代码片派生到我的代码片/// <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);}。