SQLite简单类库

发布时间：2020-12-12 20:05:03 所属栏目：百科来源：网络整理

导读：最近我在做一些小程序,小项目时,有使用到轻量级的数据库SQLite,于是便将访问数据库SQLite的底层代码封装在一起,做了一个简单的并不复杂的SQLiteHelper封装类来访问SQLite数据库,记得要在项目中添加引用＂System.Data.SQLite＂的程序集. //===================

最近我在做一些小程序,小项目时,有使用到轻量级的数据库SQLite,于是便将访问数据库SQLite的底层代码封装在一起,做了一个简单的并不复杂的SQLiteHelper封装类来访问SQLite数据库,记得要在项目中添加引用＂System.Data.SQLite＂的程序集. //============================================== // // Copyright(C) 2009-2010 连林SoftWare工作室 // All Rights Reserved // // FileName: SQLiteHelper // Description: // // Author: Wang Lian Lin(王连林) // CLR版本: 2.0.50727.42 // MachineName: WLL // 注册组织名: WLL // Created By Wang Lian Lin(王连林) at 2009-3-12 0:57:49 // Email: LianLin.Wang@163.com // http://chnboy.cnblogs.com // //============================================== using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SQLite; /// <summary> /// SQLiteHelper 的摘要说明 /// </summary> public class SQLiteHelper { public static string SQLiteConnectionString = ConfigurationManager.ConnectionStrings["SQLiteConnectString"].ConnectionString; //轻量级数据库SQLite的连接字符串写法："Data Source=D:databasetest.s3db" //轻量级数据库SQLite的加密后的连接字符串写法："Data Source=D:"databasetest.s3db;Version=3;Password=你的SQLite数据库密码;" public SQLiteHelper() { // // TODO: 在此处添加构造函数逻辑 // } #region ExecuteNonQuery /// <summary> /// 对连接执行Transact-SQL语句并返回受影响的行数 /// </summary> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static int ExecuteNonQuery(string commandText,bool isProcedure,params SQLiteParameter[] paras) { SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString); SQLiteCommand cmd = new SQLiteCommand(commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } cmd.Parameters.Clear(); foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } try { con.Open(); return cmd.ExecuteNonQuery(); } finally { con.Close(); } } /// <summary> /// 对连接执行Transact-SQL语句并返回受影响的行数 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static int ExecuteNonQuery(string commandText,params SQLiteParameter[] paras) { return ExecuteNonQuery(commandText,false,paras); } /// <summary> /// 对连接执行Transact-SQL语句并返回受影响的行数 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第二个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static int ExecuteNonQuery(SQLiteTransaction trans,string commandText,params SQLiteParameter[] paras) { SQLiteConnection con = trans.Connection; SQLiteCommand cmd = new SQLiteCommand(commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } cmd.Parameters.Clear(); foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } if (trans != null) { cmd.Transaction = trans; } try { if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteNonQuery(); } finally { if (trans == null) { con.Close(); } } } /// <summary> /// 对连接执行Transact-SQL语句并返回受影响的行数 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static int ExecuteNonQuery(SQLiteTransaction trans,params SQLiteParameter[] paras) { return ExecuteNonQuery(trans,commandText,paras); } #endregion #region ExecuteQueryScalar /// <summary> /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列 /// </summary> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第一个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static object ExecuteQueryScalar(string commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } try { con.Open(); return cmd.ExecuteScalar(); } finally { con.Close(); } } /// <summary> /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static object ExecuteQueryScalar(string commandText,params SQLiteParameter[] paras) { return ExecuteQueryScalar(commandText,paras); } /// <summary> /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第二个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static object ExecuteQueryScalar(SQLiteTransaction trans,params SQLiteParameter[] paras) { SQLiteConnection con = trans.Connection; SQLiteCommand cmd = new SQLiteCommand(commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } cmd.Parameters.Clear(); foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } if (trans != null) { cmd.Transaction = trans; } try { if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteScalar(); } finally { if (trans == null) { con.Close(); } } } /// <summary> /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static object ExecuteQueryScalar(SQLiteTransaction trans,params SQLiteParameter[] paras) { return ExecuteQueryScalar(trans,paras); } #endregion #region ExecuteDataReader /// <summary> /// 执行SQL，并返回结果集的只前进数据读取器 /// </summary> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第一个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static SQLiteDataReader ExecuteDataReader(string commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } try { if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { con.Close(); throw; } } /// <summary> /// 执行SQL，并返回结果集的只前进数据读取器 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static SQLiteDataReader ExecuteDataReader(string commandText,params SQLiteParameter[] paras) { return ExecuteDataReader(commandText,paras); } /// <summary> /// 执行SQL，并返回结果集的只前进数据读取器 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第二个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } cmd.Parameters.Clear(); foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } if (trans != null) { cmd.Transaction = trans; } try { if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { if (trans == null) { con.Close(); } throw; } } /// <summary> /// 执行SQL，并返回结果集的只前进数据读取器 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans,params SQLiteParameter[] paras) { return ExecuteDataReader(trans,paras); } #endregion #region ExecuteDataSet /// <summary> /// 执行SQL，并返回DataSet结果集 /// </summary> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第一个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandText,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } try { con.Open(); SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } finally { con.Close(); } } /// <summary> /// 执行SQL，并返回DataSet结果集 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandText,params SQLiteParameter[] paras) { return ExecuteDataSet(commandText,paras); } /// <summary> /// 执行SQL，并返回DataSet结果集 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句或存储过程名</param> /// <param name="isProcedure">第二个参数是否为存储过程名,false为否</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static DataSet ExecuteDataSet(SQLiteTransaction trans,con); if (isProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } cmd.Parameters.Clear(); foreach (SQLiteParameter para in paras) { cmd.Parameters.Add(para); } if (trans != null) { cmd.Transaction = trans; } try { if (con.State != ConnectionState.Open) { con.Open(); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } finally { if (trans == null) { con.Close(); } } } /// <summary> /// 执行SQL，并返回DataSet结果集 /// </summary> /// <param name="trans">传递事务对象</param> /// <param name="commandText">SQL语句</param> /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param> /// <returns></returns> public static DataSet ExecuteDataSet(SQLiteTransaction trans,params SQLiteParameter[] paras) { return ExecuteDataSet(trans,paras); } #endregion }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!