/// <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 存储过程
}
分享到:
相关推荐
//数据库连接字符串(web.config来配置),可以动态更改SQLString支持多数据库. public static string connectionString = "Data Source=" + System.Web.HttpContext.Current.Server.MapPath( Configuration...
本文实例讲述了C#解决SQlite并发异常问题的方法。分享给大家供大家参考,具体如下: 使用C#访问sqlite时,常会遇到多线程并发导致SQLITE数据库损坏的问题。 SQLite是文件级别的数据库,其锁也是文件级别的:多个线程...
C#操作SQLite代码类 一个朋友写的,拿来分享给大家,希望有用
用C#实现了对sqlite数据库的操作,包括动态连接库的引用,数据库操作类,增删改查,操作方便。
本文实例讲述了C#操作SQLite数据库之读写数据库的方法。分享给大家供大家参考,具体如下: 这里演示读写数据库并在窗体(Form)中显示其数据,其方式为: 读: Database(SQLite) -> DataAdapter -> DataSet -> ...
主要介绍了C#操作SQLite数据库帮助类,详细分析了C#针对sqlite数据库的连接、查询、分页等各种常见操作的实现与封装技巧,需要的朋友可以参考下
C#操作sqlite
这是一个C#.NET示例程序, 简单演示了如何使用C#操作SQLite数据库。同时演示了如何用C#讲图片二进制数据存储到数据库和从数据库读取图片二进制数据并显示在PictureBox.
c# SqLite操作类库,可以直接使用的
C#应用SQLite的示例,在网络资料的基础上,用VS2008生成一个应用程序,包含了DLL文件
c#对SQLite创建自定义函数
下载来的资源,自己改了一下,共享出来,供小白使用,本想免费的,无奈最少是2分,再次吐槽一下csdn的下载规则变更,越来越商业化了。
Sqlite 操作类 C# .net,嵌入式SQL //创建一个数据库文件 string datasource = @"d:/ChinaPower.s3db"; // System.Data.SQLite.SQLiteConnection.CreateFile(datasource); //连接数据库 System.Data....
C#代码生成sqlite.db文件,可筛选表。
这是一个使用C#开发的Sqlite数据库创建、操作的源码工程,关于Sqlite的所有操作已经单独创建了专门的跨平台【.NETCore3.1】类库包含相应的帮助类,可以直接生成后拿到任何项目中直接使用,高效简单,省去了从头开发...
在VS2019中,使用C#编写的完整的操作SQLite数据库示例,其中包括常用数据库操作类。上载即可使用。
C# sqlite 批量更新及性能测试
1.源码:C#访问Sqlite数据库并显示在listBox中,并且支持对数据库的插入和删除。 2. 教程文档:该项目开发的详细步骤,并添加了必要的说明。 3. 使用说明文档:该源码项目的使用说明和注意事项。 4. sqlite.Net。...
sqlite操作实例c#完整版,实现动态创建数据库,根据类库,自定义创建表结构,实现数据添加等功能
C#操作SQLite的示例代码,适合初学者。高手就不要下载了