最近我在做一些
小程序,小项目时,有使用到轻量级的
数据库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 }
