C# SQLite数据库 访问封装类
2023-09-14 09:02:10 时间
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); 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); 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); 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); 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); 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); 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); 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); 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); 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); 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); 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); 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;
/// 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,下载地址:点击打开链接
相关文章
- Visual C#之核心语言
- rsa互通密钥对生成及互通加解密(c#,java,php)
- 在C#代码中应用Log4Net(一)简单使用Log4Net
- C# winform key value型数据如何绑定ComBox (hashtable,keyvaluepair,dictionary )
- 在C#代码中应用Log4Net(一)简单使用Log4Net
- c# .net缓存(旧)
- C# 枚举操作工具类
- C#隐式运行CMD命令(隐藏命令窗口)
- C# 关于XML遍历新增节点,修改属性小例
- 【开源】分享2011-2015年全国城市历史天气数据库【Sqlite+C#访问程序】
- c# - 常量定义与赋值
- (六十六)c#Winform自定义控件-图标-HZHControls
- (四十)c#Winform自定义控件-开关-HZHControls
- (27)C#访问SQLite数据库
- c# 并发与异步 二、变量 锁 前后台线程 优先级 信号
- C#图像处理