/// <summary>
/// SQLite 操作类
/// Author Light
/// </summary>
public class SQLite
{
/// <summary>
/// 数据库
/// </summary>
public static string dataBasePath;
public static string dataBasePasssord;
/// <summary>
/// 获取连接
/// </summary>
/// <returns></returns>
private static SQLiteConnection getSQLiteConnection()
{
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
connStr.DataSource = dataBasePath;
connStr.Password = dataBasePasssord; //设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connStr.ToString();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("连接数据库异常:" + ex.Message);
}
return conn;
}
#region 执行查询
/// <summary>
/// 执行SQL，返回影响的记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSql(string sql)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
try
{
conn.Open();
iResult = cmd.ExecuteNonQuery();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行SQL，返回影响的记录数异常:" + ex.Message);
}
}
}
return iResult;
}
/// <summary>
/// 执行带一个存储过程参数的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <param name="content"></param>
/// <returns></returns>
public static int ExecuteSql(string sql, string content)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
try
{
SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
parameter.Value = content;
cmd.Parameters.Add(parameter);
conn.Open();
iResult = cmd.ExecuteNonQuery();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行带一个存储过程参数的SQL语句异常:" + ex.Message);
}
}
}
return iResult;
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static int ExecuteSql(string sql, params SqlParameter[] sqlParams)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(conn, cmd, null, sql, sqlParams);
iResult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行SQL语句，返回影响的记录数异常:" + ex.Message);
}
}
}
return iResult;
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet Query(string sql)
{
DataSet dsResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
{
try
{
conn.Open();
dsResult = new DataSet();
da.Fill(dsResult, "ds");
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行查询异常:" + ex.Message);
}
}
}
return dsResult;
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataSet Query(string sql, params SqlParameter[] sqlParams)
{
DataSet dsResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
{
try
{
PrepareCommand(conn, cmd, null, sql, sqlParams);
dsResult = new DataSet();
da.Fill(dsResult, "ds");
cmd.Parameters.Clear();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行查询返回DataSet异常:" + ex.Message);
}
}
}
}
return dsResult;
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）
/// 第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingle(string sql)
{
object oResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
try
{
conn.Open();
oResult = cmd.ExecuteScalar();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行一条计算查询结果语句，返回查询结果（object）异常:" + ex.Message);
}
}
}
return oResult;
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static object GetSingle(string sql, params SqlParameter[] sqlParams)
{
object oResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(conn, cmd, null, sql, sqlParams);
oResult = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行查询异常:" + ex.Message);
}
}
}
return oResult;
}
/// <summary>
/// 执行查询，返回sqliteDataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sql)
{
SQLiteDataReader rResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
try
{
conn.Open();
rResult = cmd.ExecuteReader();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行查询返回sqliteDataReader异常:" + ex.Message);
}
}
}
return rResult;
}
/// <summary>
/// 执行查询语句，返回SqliteDataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sql, params SqlParameter[] sqlParams)
{
SQLiteDataReader rResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(conn, cmd, null, sql, sqlParams);
rResult = cmd.ExecuteReader();
cmd.Parameters.Clear();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行查询返回SqliteDataReader异常:" + ex.Message);
}
}
}
return rResult;
}
#endregion 执行查询
#region 执行事务
/// <summary>
/// 执行SQL事务操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSqlTran(string sql)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteTransaction tran = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn, tran))
{
try
{
if (!string.IsNullOrEmpty(sql))
{
conn.Open();
iResult = cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (ExceptionUtility ex)
{
tran.Rollback();
throw new ExceptionUtility("执行SQL事务操作异常:" + ex.Message);
}
}
}
}
return iResult;
}
/// <summary>
/// 执行多条SQL事务操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSqlTran(List<string> sqlList)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteTransaction tran = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
conn.Open();
cmd.Connection = conn;
cmd.Transaction = tran;
foreach (string sql in sqlList)
{
if (!string.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
iResult += cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (ExceptionUtility ex)
{
tran.Rollback();
throw new ExceptionUtility("执行多条SQL事务操作异常:" + ex.Message);
}
}
}
}
return iResult;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务
/// </summary>
/// <param name="sqlHashTable"></param>
/// <returns></returns>
public static int ExecuteSqlTran(Hashtable sqlHashTable)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteTransaction tran = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
conn.Open();
foreach (DictionaryEntry de in sqlHashTable)
{
string cmdSql = de.Key.ToString();
SqlParameter[] cmdParams = (SqlParameter[])de.Value;
PrepareCommand(conn, cmd, tran, cmdSql, cmdParams);
iResult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();
}
}
catch (ExceptionUtility ex)
{
tran.Rollback();
throw new ExceptionUtility("执行多条SQL事务异常:" + ex.Message);
}
}
}
}
return iResult;
}
/// <summary>
/// 向数据库中插入图像格式字段
/// </summary>
/// <param name="sql"></param>
/// <param name="fs"></param>
/// <returns></returns>
public static int ExecuteSqlInsertImg(string sql, byte[] fs)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
try
{
SqlParameter sqlParam = new SqlParameter("@fs", SqlDbType.Image);
sqlParam.Value = fs;
cmd.Parameters.Add(sqlParam);
conn.Open();
iResult = cmd.ExecuteNonQuery();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("插入图像字段异常:" + ex.Message);
}
}
}
return iResult;
}
#endregion 执行事务
#region 私有公共方法
private static void PrepareCommand(SQLiteConnection conn, SQLiteCommand cmd, SQLiteTransaction tran, string sql, SqlParameter[] sqlParams)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = sql;
if (tran != null)
{
cmd.Transaction = tran;
}
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (SqlParameter param in sqlParams)
{
cmd.Parameters.Add(param);
}
}
}
#endregion 私有公共方法
#region 存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="dataParams"></param>
/// <returns></returns>
public static SQLiteDataReader RunProcedure(string storedProcName, IDataParameter[] dataParams)
{
SQLiteDataReader rResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteCommand cmd = BuildQueryCommand(conn, storedProcName, dataParams))
{
try
{
rResult = cmd.ExecuteReader();
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
}
}
}
return rResult;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="dataParams"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] dataParams, string tableName)
{
DataSet dsResult = null;
using (SQLiteConnection conn = getSQLiteConnection())
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter())
{
try
{
dsResult = new DataSet();
da.SelectCommand = BuildQueryCommand(conn, storedProcName, dataParams);
da.Fill(dsResult, tableName);
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
}
}
}
return dsResult;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="dataParams"></param>
/// <param name="rowsAffected"></param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] dataParams, out int rowsAffected)
{
int iResult = -1;
using (SQLiteConnection conn = getSQLiteConnection())
{
try
{
SQLiteCommand cmd = BuildIntCommand(conn, storedProcName, dataParams);
rowsAffected = cmd.ExecuteNonQuery();
iResult = (int)cmd.Parameters["ReturnValue"].Value;
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
}
}
return iResult;
}
/// <summary>
/// 创建SQLiteCommand对象实例（用来返回一个整数值）
/// </summary>
/// <param name="conn"></param>
/// <param name="storedProcName"></param>
/// <param name="dataParams"></param>
/// <returns></returns>
private static SQLiteCommand BuildIntCommand(SQLiteConnection conn, string storedProcName, IDataParameter[] dataParams)
{
SQLiteCommand cmd = BuildQueryCommand(conn, storedProcName, dataParams);
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return cmd;
}
/// <summary>
/// 构建SqliteCommand对象（用来返回一个结果集，而不是一个整数值）
/// </summary>
/// <param name="conn"></param>
/// <param name="storedProcName"></param>
/// <param name="dataParams"></param>
/// <returns></returns>
private static SQLiteCommand BuildQueryCommand(SQLiteConnection conn, string storedProcName, IDataParameter[] dataParams)
{
SQLiteCommand cmd = new SQLiteCommand(storedProcName, conn);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in dataParams)
{
cmd.Parameters.Add(param);
}
}
catch (ExceptionUtility ex)
{
throw new ExceptionUtility("构建SQLiteCommand异常:" + ex.Message);
}
return cmd;
}
#endregion 存储过程
}
