最近我在做一些小程序,小项目时,有使用到轻量级的数据库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:\database\test.s3db" //轻量级数据库sqlite的加密后的连接字符串写法："Data Source=D:"database\test.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 } 原文链接：https://www.f2er.com/sqlite/200675.html