sqlite数据库加锁提交_SQLite数据库在多线程写锁文件的解决办法

参考了很多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 = "";

///

/// 获得连接对象

///

///

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;

}

///

/// COMMAND初始化

///

///

///

///

///

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)

{

}

}

///

/// 批量执行带参数的SQL语句 事务 并返回执行结果

///

///

///

///

public string ExecuteSqlTran(List SQLStringList, List 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;

}

///

/// 批量执行带参数的SQL语句 事务 并返回执行结果

///

///

///

///

public string ExecuteSqlTran(List 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;

}

///

/// COMMAND初始化

///

///

///

///

///

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)

{

}

}

///

/// 返回DataSet

///

///

///

///

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;

}

///

/// 返回DataTable

///

///

///

///

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];

}

///

/// 返回DataTable

///

///

///

///

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];

}

///

/// 返回执行数据的行数

///

///

///

///

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;

}

///

/// 返回受影响的行数

///

/// a

/// 传入的参数

///

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();

}

}

///

/// 返回受影响的行数

///

/// a

/// 传入的参数

///

public int ExecuteNonQuery(string cmdText)

{

AcquireWriteLock();

SQLiteCommand command = new SQLiteCommand();

using (SQLiteConnection connection = GetSQLiteConnection())

{

PrepareCommandNoParameters(command, connection, cmdText);

return command.ExecuteNonQuery();

}

}

///

/// 返回SqlDataReader对象

///

///

/// 传入的参数

///

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;

}

}

///

/// 返回结果集中的第一行第一列，忽略其他行或列

///

///

/// 传入的参数

///

public object ExecuteScalar(string cmdText, params object[] p)

{

SQLiteCommand cmd = new SQLiteCommand();

using (SQLiteConnection connection = GetSQLiteConnection())

{

PrepareCommand(cmd, connection, cmdText, p);

return cmd.ExecuteScalar();

}

}

///

/// 分页

///

///

///

///

///

///

///

///

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 _dbThreadIdDict = new Dictionary();

///

/// 获得写操作的超时时间，单位为毫秒，可以通过配置文件appSettings节中添加设置 SQLiteWriteLockTimeout 的value值控制锁等待的超时时间，该值必须为正整数数字，单位为毫秒

/// 默认的超时时间是1000ms

///

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

}

}