参考了很多SQLITE数据库多线程的解决办法 我自己写了一个SQLITEHELPER 来解决这个问题
希望大家多多指教
调用的时候
SQLLiteDBHelper _SQLLiteDBHelper = new SQLLiteDBHelper();
_SQLLiteDBHelper.Dispose();
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Threading;
namespace DAL
{
public class SQLLiteDBHelper : IDisposable
{
public static string Connectionstring = "";
/// <summary>
/// 获得连接对象
/// </summary>
/// <returns></returns>
public SQLiteConnection GetSQLiteConnection()
{
//return new SQLiteConnection(Connectionstring);
SQLiteConnection conn = new SQLiteConnection();
try
{
conn.ConnectionString = Connectionstring;
if (conn.State.Equals(ConnectionState.Closed))
{
conn.DefaultTimeout = 5000;
conn.Open();
}
}
catch (Exception ee)
{
//OAFile.ErrorLog(OAFile.GetCurrUrl(), "链接字符串错误，请检查,详细错误：" + ee.Message);
}
return conn;
}
/// <summary>
/// COMMAND初始化
/// </summary>
/// <param name="cmd"></param>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="p"></param>
private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (p != null)
{
foreach (object parm in p)
cmd.Parameters.Add(parm);
}
}
catch (Exception)
{
}
}
/// <summary>
/// 批量执行带参数的SQL语句 事务 并返回执行结果
/// </summary>
/// <param name="SQLStringList"></param>
/// <param name="PramsList"></param>
/// <returns></returns>
public string ExecuteSqlTran(List<string> SQLStringList, List<SQLiteParameter[]> PramsList)
{
AcquireWriteLock();
string result = "执行成功";
using (SQLiteConnection conn = GetSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
cmd.Connection = conn;
SQLiteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
if (SQLStringList[i].Trim().Length > 1)
{
PrepareCommand(cmd, conn, SQLStringList[i].Trim(), PramsList[i]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
result = ex.Message.Trim();
}
}
catch (Exception)
{
result = "lock";
}
}
}
return result;
}
/// <summary>
/// 批量执行带参数的SQL语句 事务 并返回执行结果
/// </summary>
/// <param name="SQLStringList"></param>
/// <param name="PramsList"></param>
/// <returns></returns>
public string ExecuteSqlTran(List<string> SQLStringList)
{
AcquireWriteLock();
string result = "执行成功";
using (SQLiteConnection conn = GetSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
SQLiteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
if (SQLStringList[i].Trim().Length > 1)
{
PrepareCommandNoParameters(cmd, conn, SQLStringList[i].Trim());
cmd.ExecuteNonQuery();
// cmd.Parameters.Clear();
}
}
tx.Commit();
}
catch (Exception ex)
{
if (conn.State!=ConnectionState.Closed)
{
tx.Rollback();
}
result = ex.Message.Trim();
}
}
}
return result;
}
/// <summary>
/// COMMAND初始化
/// </summary>
/// <param name="cmd"></param>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="p"></param>
private void PrepareCommandNoParameters(SQLiteCommand cmd, SQLiteConnection conn, string cmdText)
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
}
catch (Exception)
{
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText"></param>
/// <param name="p"></param>
/// <returns></returns>
public DataSet DS(string cmdText, params object[] p)
{
DataSet ds = new DataSet();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommand(command, connection, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds;
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="cmdText"></param>
/// <param name="p"></param>
/// <returns></returns>
public DataTable DT(string cmdText, params object[] p)
{
DataSet ds = new DataSet();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommand(command, connection, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds.Tables[0];
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="cmdText"></param>
/// <param name="p"></param>
/// <returns></returns>
public DataTable DT(string cmdText)
{
DataSet ds = new DataSet();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommandNoParameters(command, connection, cmdText);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds.Tables[0];
}
/// <summary>
/// 返回执行数据的行数
/// </summary>
/// <param name="cmdText"></param>
/// <param name="p"></param>
/// <returns></returns>
public DataRow ExecuteDataRow(string cmdText, params object[] p)
{
DataSet ds = DS(cmdText, p);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0];
return null;
}
/// <summary>
/// 返回受影响的行数
/// </summary>
/// <param name="cmdText">a</param>
/// <param name="commandParameters">传入的参数</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, params object[] p)
{
AcquireWriteLock();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommand(command, connection, cmdText, p);
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 返回受影响的行数
/// </summary>
/// <param name="cmdText">a</param>
/// <param name="commandParameters">传入的参数</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText)
{
AcquireWriteLock();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommandNoParameters(command, connection, cmdText);
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 返回SqlDataReader对象
/// </summary>
/// <param name="cmdText"></param>
/// <param name="commandParameters">传入的参数</param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
{
SQLiteCommand command = new SQLiteCommand();
SQLiteConnection connection = GetSQLiteConnection();
try
{
PrepareCommand(command, connection, cmdText, p);
SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch
{
connection.Close();
throw;
}
}
/// <summary>
/// 返回结果集中的第一行第一列，忽略其他行或列
/// </summary>
/// <param name="cmdText"></param>
/// <param name="commandParameters">传入的参数</param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, params object[] p)
{
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommand(cmd, connection, cmdText, p);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 分页
/// </summary>
/// <param name="recordCount"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="cmdText"></param>
/// <param name="countText"></param>
/// <param name="p"></param>
/// <returns></returns>
public DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
{
if (recordCount < 0)
recordCount = int.Parse(ExecuteScalar(countText, p).ToString());
DataSet ds = new DataSet();
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection connection = GetSQLiteConnection())
{
PrepareCommand(command, connection, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
}
return ds;
}
#region 静态字段和属性
const short WAIT_TIME = 5;
static readonly object locker = new object();
static Dictionary<string, int> _dbThreadIdDict = new Dictionary<string, int>();
/// <summary>
/// 获得写操作的超时时间，单位为毫秒，可以通过配置文件appSettings节中添加设置 SQLiteWriteLockTimeout 的value值控制锁等待的超时时间，该值必须为正整数数字，单位为毫秒
/// 默认的超时时间是1000ms
/// </summary>
public static int SQLiteWriteLockTimeout
{
get
{
string configValule = "300000";
if (!string.IsNullOrEmpty(configValule))
{
return int.Parse(configValule);
}
return 1000;
}
}
#endregion
private string _connString;
public SQLLiteDBHelper()
{
}
#region 私有方法
private void AcquireWriteLock()
{
if (Connectionstring=="")
{
return;
}
_connString = Connectionstring;
int threadId = Thread.CurrentThread.ManagedThreadId;
int waitTimes = 0;
while (_dbThreadIdDict.ContainsKey(_connString) && _dbThreadIdDict[_connString] != threadId)
{
Thread.Sleep(WAIT_TIME);
waitTimes += WAIT_TIME;
#if DEBUG
Console.WriteLine(_connString + " wait for " + waitTimes + " ms");
#endif
if (waitTimes > SQLiteWriteLockTimeout)
{
//throw new TimeoutException("SQLite等待写操作超时");
}
}
lock (locker)
{
if (!_dbThreadIdDict.ContainsKey(_connString))
_dbThreadIdDict.Add(_connString, threadId);
}
}
private void ReleaseWriteLock()
{
lock (locker)
{
if (_connString!=null)
{
if (_dbThreadIdDict.ContainsKey(_connString))
{
_dbThreadIdDict.Remove(_connString);
}
}
}
}
#endregion
#region IDisposable 成员
public void Dispose()
{
ReleaseWriteLock();
}
#endregion
}
}
