这是一个.NET类库，实现了SQLite数据库的连接、事务处理、执行SQL语句（包括存储过程）以及数据读取等功能。主要方法包括ExecuteNonQuery用于执行非查询操作，ExecuteReader用于获取数据读取器，ExecuteSql用于执行SQL并返回影响的记录数，以及Query方法用于返回DataSet。类中还包含了连接字符串的静态获取和资源的释放。
public classSQLiteBase : IDisposable
{
SQLiteConnection conn;
SQLiteTransaction trans= null;///
///连接字符串///
private static stringConnectionString
{get{string strConnection = @"Data Source=" + Application.StartupPath + @"\EPMSData.db" + ";Version=3;";returnstrConnection;
}
}///
///新建数据库连接,构造函数///
publicSQLiteBase()
{
conn= newSQLiteConnection(ConnectionString);
conn.Open();
}#region 释放资源
///
///释放资源///
public voidDispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}///
///释放资源///
///
protected virtual void Dispose(booldisposing)
{if (!disposing)
{return;
}if (trans != null)
{try{
trans.Commit();
}catch{ }
}this.conn.Close();
}#endregion
#region 执行ExecuteNonQuery
///
///执行查询语句///
/// SQL语句
///
public int ExecuteNonQuery(stringstrSQL)
{return ExecuteNonQuery(strSQL, (SQLiteParameter[])null, CommandType.Text);
}///
///执行查询语句///
/// SQL语句
///
///
public int ExecuteNonQuery(stringstrSQL, CommandType CT)
{return ExecuteNonQuery(strSQL, (SQLiteParameter[])null, CT);
}///
///执行查询语句或者存储过程///
/// SQL语句
///
///
public int ExecuteNonQuery(stringstrSQL, SQLiteParameter[] arrParameter)
{returnExecuteNonQuery(strSQL, arrParameter, CommandType.Text);
}///
///执行查询语句或者存储过程///
///
///
///
public int ExecuteNonQuery(string strSQL, ListarrParameter)
{returnExecuteNonQuery(strSQL, arrParameter.ToArray(), CommandType.Text);
}///
///执行查询语句或者存储过程///
///
///
///
///
public int ExecuteNonQuery(string strSQL, ListarrParameter, CommandType CT)
{returnExecuteNonQuery(strSQL, arrParameter.ToArray(), CT);
}///
///执行查询语句///
///
///
///
///
public int ExecuteNonQuery(stringstrSQL, SQLiteParameter[] arrParameter, CommandType CT)
{using (SQLiteCommand cmd = newSQLiteCommand(strSQL, conn))
{if (trans == null && CT !=CommandType.StoredProcedure)
{
trans=conn.BeginTransaction(IsolationLevel.ReadCommitted);
}if (CT !=CommandType.StoredProcedure)
{
cmd.Transaction=trans;
}
cmd.CommandType=CT;if (arrParameter != null)
cmd.Parameters.AddRange(arrParameter);try{int val =cmd.ExecuteNonQuery();
cmd.Parameters.Clear();returnval;
}catch(SqlException SE)
{if (CT !=CommandType.StoredProcedure)
{
trans.Rollback();
trans.Dispose();
}throw new Exception("执行" + strSQL + "发生错误，错误信息是：" +SE.Message);
}
}
}#endregion
#region 返回 SqlDataReader
///
///执行SQL命令///
///
///
public static SQLiteDataReader ExecuteReader(stringstrSQL)
{return ExecuteReader(strSQL, (SQLiteParameter[])null, CommandType.Text);
}///
///执行SQL命令和没有参数的存储过程///
///
///
///
public static SQLiteDataReader ExecuteReader(stringstrSQL, CommandType CT)
{return ExecuteReader(strSQL, (SQLiteParameter[])null, CT);
}///
///执行SQL命令///
///
///
///
public static SQLiteDataReader ExecuteReader(stringstrSQL, SQLiteParameter[] arrParameter)
{returnExecuteReader(strSQL, arrParameter, CommandType.Text);
}///
///执行SQL命令或没有参数的或者存储过程///
///
///
///
public static SQLiteDataReader ExecuteReader(stringstrSQL, SQLiteParameter[] arrParameter, CommandType CT)
{
SQLiteConnection conn= newSQLiteConnection(ConnectionString);try{using (SQLiteCommand command = newSQLiteCommand(strSQL, conn))
{
command.CommandType=CT;if (arrParameter != null)
command.Parameters.AddRange(arrParameter);
conn.Open();
SQLiteDataReader reader=command.ExecuteReader();returnreader;
}
}catch { conn.Dispose(); throw; }
}#endregion
#region 执行ExecuteSql
///
///执行SQL语句，返回影响的记录数///
/// SQL语句
/// 影响的记录数
public static int ExecuteSql(stringSQLString)
{using (SQLiteConnection connection = newSQLiteConnection(ConnectionString))
{using (SQLiteCommand cmd = newSQLiteCommand(SQLString, connection))
{try{
connection.Open();int rows =cmd.ExecuteNonQuery();returnrows;
}catch(System.Data.SQLite.SQLiteException E)
{
connection.Close();throw newException(E.Message);
}
}
}
}///
///执行SQL语句，返回影响的记录数///
/// SQL语句
/// 影响的记录数
public static int ExecuteSql(string SQLString, paramsSQLiteParameter[] cmdParms)
{using (SQLiteConnection connection = newSQLiteConnection(ConnectionString))
{using (SQLiteCommand cmd = newSQLiteCommand())
{try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);int rows =cmd.ExecuteNonQuery();
cmd.Parameters.Clear();returnrows;
}catch(System.Data.SQLite.SQLiteException E)
{throw newException(E.Message);
}
}
}
}///
///执行查询语句，返回DataSet///
/// 查询语句
/// DataSet
public static DataSet Query(stringSQLString)
{using (SQLiteConnection connection = newSQLiteConnection(ConnectionString))
{
DataSet ds= newDataSet();try{
connection.Open();
SQLiteDataAdapter command= newSQLiteDataAdapter(SQLString, connection);
command.Fill(ds,"ds");
}catch(System.Data.SQLite.SQLiteException ex)
{throw newException(ex.Message);
}returnds;
}
}///
///执行查询语句，返回DataSet///
/// 查询语句
/// DataSet
public static DataSet Query(string SQLString, paramsSQLiteParameter[] cmdParms)
{using (SQLiteConnection connection = newSQLiteConnection(ConnectionString))
{
SQLiteCommand cmd= newSQLiteCommand();
PrepareCommand(cmd, connection,null, SQLString, cmdParms);using (SQLiteDataAdapter da = newSQLiteDataAdapter(cmd))
{
DataSet ds= newDataSet();try{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}catch(System.Data.SQLite.SQLiteException ex)
{throw newException(ex.Message);
}returnds;
}
}
}private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, stringcmdText, SQLiteParameter[] cmdParms)
{if (conn.State !=ConnectionState.Open)
conn.Open();
cmd.Connection=conn;
cmd.CommandText=cmdText;if (trans != null)
cmd.Transaction=trans;
cmd.CommandType= CommandType.Text;//cmdType;
if (cmdParms != null)
{foreach (SQLiteParameter parm incmdParms)
cmd.Parameters.Add(parm);
}
}#endregion}
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
