public bool AddInstitute(InstituteInfo instituteInfo)
{
if ((instituteInfo.major == null) || (instituteInfo.major.Length == 0))
{
MessageBox.Show("专业为空！");
return(false);
}
SQLiteConnection conn = new SQLiteConnection(dbPath);/* 创建数据库实例，指定文件位置 */
SQLiteCommand cmdQ = new SQLiteCommand();
try
{
conn.Open(); /* 打开数据库，若文件不存在会自动创建 */
SQLiteTransaction tran = conn.BeginTransaction();
cmdQ = new SQLiteCommand(conn); /* 实例化SQL命令 */
cmdQ.Transaction = tran;
cmdQ.CommandText = "insert into institute values(@num, @name)"; /* 设置带参SQL语句 */
cmdQ.Parameters.AddRange(new[] { /* 添加参数 */
new SQLiteParameter("@num", instituteInfo.num),
new SQLiteParameter("@name", instituteInfo.name)
});
cmdQ.ExecuteNonQuery(); /* 执行查询 */
tran.Commit(); /* 提交 */
cmdQ.Dispose(); /* 释放资源 */
tran.Dispose(); /* 释放资源 */
tran.Dispose(); /* 释放资源 */
string sql = "CREATE TABLE IF NOT EXISTS " + instituteInfo.name + "(" + /* 建表语句 */
"name VARCHAR(30));"; /* 学院名称 */
cmdQ = new SQLiteCommand(sql, conn);
cmdQ.ExecuteNonQuery(); /* 如果表不存在，创建单元表 */
for (int idx = 0; idx < instituteInfo.major.Length; idx++)
{
tran = conn.BeginTransaction();
cmdQ = new SQLiteCommand(conn); /* 实例化SQL命令 */
cmdQ.Transaction = tran;
cmdQ.CommandText = "insert into " + instituteInfo.name + " values(@name)"; /* 设置带参SQL语句 */
cmdQ.Parameters.AddRange(new[] { /* 添加参数 */
new SQLiteParameter("@name", instituteInfo.major[idx])
});
cmdQ.ExecuteNonQuery(); /* 执行查询 */
tran.Commit(); /* 提交 */
cmdQ.Dispose(); /* 释放资源 */
tran.Dispose(); /* 释放资源 */
tran.Dispose(); /* 释放资源 */
}
InitInstituteList(); /* 更新列表 */
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
cmdQ.Dispose(); /* 释放资源 */
conn.Close();
return(false);
}
cmdQ.Dispose(); /* 释放资源 */
conn.Close();
return(true);
}
/// <summary>
/// 释放连接
/// </summary>
/// <param name="disposing">是否释放</param>
protected virtual void Dispose(bool disposing)
{
lock (this)
{
if (disposing && !m_disposed)
{
if (ST != null)
{
try
{
ST.Rollback();
ST.Dispose();
ST = null;
}
catch
{ }
}
if (STConn != null)
{
try
{
if (STConn.State != ConnectionState.Closed)
{
STConn.Close();
}
STConn = null;
}
catch
{ }
}
m_disposed = true;
}
}
}
public void Commit()
{
_isRunningWithinTransaction = false;
_sqLiteTransaction.Commit();
_sqLiteTransaction.Dispose();
_sqliteConnection.Close();
_sqliteConnection.Dispose();
}
public void Commit()
{
lock (_lock)
{
_transaction?.Commit();
_transaction?.Dispose();
_transaction = null;
}
}
public void Commit()
{
if (tran != null)
{
tran.Commit();
tran.Dispose();
tran = null;
}
}
public void Commit()
{
if (tx == null)
{
return;
}
tx.Commit();
tx.Dispose();
tx = null;
}
public void Commit()
{
if (Txn == null)
{
throw new InvalidOperationException("Transaction is not open");
}
Txn.Commit();
Txn.Dispose();
Txn = null;
}
public static void CommitTran()
{
if (_useSqlTran)
{
dbTransaction.Commit();
dbTransaction.Dispose();
dbTransaction = null;
_useSqlTran = false;
}
}
public bool Commit()
{
if (!IsBeginTran)
{
return(false);
}
sQLiteTransaction.Commit();
sQLiteTransaction.Dispose();
IsBeginTran = false;
return(true);
}
public void Commit()
{
lock (this)
{
if (isTrans && sqliteTransaction != null)
{
sqliteTransaction.Commit();
sqliteTransaction.Dispose();
isTrans = false;
}
}
}
private int dbExecuteTransactionCommands(List <SQLiteCommand> commands)
{
SQLiteTransaction transaction = null;
int modifiedRows = 0;
try
{
transaction = dbConnection.BeginTransaction();
foreach (SQLiteCommand command in commands)
{
command.Transaction = transaction;
modifiedRows += command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (SQLiteException e)
{
Console.WriteLine("server:DBmanager:dbExecuteTransactionCommands:Exception >> " + e.Message);
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (SQLiteException e2)
{
Console.WriteLine("server:DBmanager:dbExecuteTransactionCommands:Exception >> Transaction rollback failed: " + e2.Message);
}
finally
{
transaction.Dispose();
}
}
}
finally
{
foreach (SQLiteCommand command in commands)
{
if (command != null)
{
command.Dispose();
}
}
if (transaction != null)
{
transaction.Dispose();
}
}
return(modifiedRows);
}
public void StartTransactionSession()
{
// Protect this section with our ReaderWriterLockSlim that handles concurrency issues for us
try
{
// Use a write lock for this operation
_SQLiteDBReadWriteLocker.EnterWriteLock();
// Create connection if we don't already have one
if (_Session_sqlConnection == null)
{
_Session_sqlConnection = new SQLiteConnection(_DBConnectStr);
}
// Open DB connection if necessary
if (_Session_sqlConnection.State == ConnectionState.Closed)
{
_Session_sqlConnection.Open();
try
{
System.Diagnostics.Debug.WriteLine("SqLite Server Version: " + _Session_sqlConnection.ServerVersion);
}
catch { }
}
// We will only start a new transaction if one doesn't already exist, or if existing one doesn't have a DB connection
if ((_Session_sqlTransaction == null) || (_Session_sqlTransaction.Connection == null))
{
// Do we have left-over transaction that needs to be released first?
if ((_Session_sqlTransaction != null) && (_Session_sqlTransaction.Connection == null))
{
_Session_sqlTransaction.Dispose();
_Session_sqlTransaction = null;
}
// Start transaction
_Session_sqlTransaction = _Session_sqlConnection.BeginTransaction();
// Reset the amount of transacted data waiting to be committed
this._TransactedSizeOfData = 0;
}
}
finally
{
// Release the write lock
if (_SQLiteDBReadWriteLocker.IsWriteLockHeld)
{
_SQLiteDBReadWriteLocker.ExitWriteLock();
}
}
}
// Multiple queries (write)
public static void MultiQueries(string[] queries)
{
SQLiteTransaction trans = null;
SQLiteCommand cmd = null;
try
{
sqlc.Open();
trans = sqlc.BeginTransaction();
cmd = sqlc.CreateCommand();
foreach (string query in queries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
trans.Commit();
sqlc.Close();
}
catch (SQLiteException ex)
{
utils.Log(ex.ToString());
utils.ShowError("Error inserting records in the database");
if (trans != null)
{
try
{
trans.Rollback();
}
catch (Exception rex)
{
utils.Log("Rollback failed: " + rex.ToString());
}
finally
{
trans.Dispose();
}
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
}
}
//-----------------------------------------------------------------------------------------------------
/// <inheritdoc/>
public override void Dispose()
{
if (tr != null)
{
tr.Dispose();
tr = null;
}
if (db != null)
{
db.Dispose();
db = null;
}
}
public void CleanUp()
{
if (_sqLiteTransaction != null)
{
_sqLiteTransaction.Commit();
_sqLiteTransaction.Dispose();
_sqLiteTransaction = null;
}
CloseConnection();
if (dataLoopThread != null)
{
dataLoopThread.Abort();
}
}
public bool InsertData(List <string> sqlList)
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
SQLiteCommand cmd = new SQLiteCommand(); //声明命令对象
SQLiteTransaction _Tx = conn.BeginTransaction(); //实例化事务
cmd.Connection = conn;
cmd.Transaction = _Tx;
int int_i = 0;
try
{
DateTime startTime = DateTime.Now;
for (int i = 0; i < sqlList.Count; i++)
{
string _sqlString = "";
_sqlString = sqlList[i].ToString();
cmd.CommandText = _sqlString;
if (cmd.ExecuteNonQuery() < 1 && _sqlString.ToUpper().IndexOf("DELETE") == -1)
{
}
}
_Tx.Commit();
_Tx.Dispose();
cmd.Dispose();
return(true);
}
catch (SQLiteException e)
{
_Tx.Rollback();
_Tx.Dispose();
cmd.Dispose();
return(false);
}
catch (Exception ex)
{
_Tx.Rollback();
_Tx.Dispose();
cmd.Dispose();
return(false);
}
finally
{
}
}
private static void ConsumerMethod()
{
// Consumer method takes data from the blocking collection and writes it to a SQLite Database
DataRecord data;
SQLiteCommand cmd = conn.CreateCommand();
// Using transactions dramatically speeds up writing to SQLite
SQLiteTransaction tr = conn.BeginTransaction();
cmd.Transaction = tr;
while (true)
{
if (dataCollection.TryTake(out data))
{
SQLite_Methods.InsertResultItem(cmd, data, TABLE_NAME);
recordsWritten++;
}
else
{
Thread.Sleep(1);
}
if (recordsWritten % RECORDS_PER_TRANSACTION == 0)
{
tr.Commit();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
}
if (recordsWritten == TOTAL_RECORDS)
{
tr.Commit();
tr.Dispose();
cmd.Dispose();
break;
}
}
}
public void Dispose()
{
if (_io_thread != null)
{
_io_thread_flags = _IO_THREAD_ABORT_REQUEST;
_io_wait.Set();
_io_thread.Join();
_io_thread = null;
}
if (_sql_trs != null)
{
_sql_trs.Commit();
_sql_trs.Dispose();
_sql_trs = null;
}
if (_sql_cmd != null)
{
_sql_cmd.Dispose();
_sql_cmd = null;
}
if (_sql_con != null)
{
_sql_con.Close();
_sql_con.Dispose();
_sql_con = null;
}
}
public override int ExecuteProcedureTran(string storedProcName, params DbParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
SQLiteTransaction tran = conn.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand();
try
{
PrepareCommand(cmd, conn, storedProcName, tran, CommandType.StoredProcedure, parameters);
int i = cmd.ExecuteNonQuery();
tran.Commit();
return(i);
}
catch (Exception ex)
{
tran.Rollback();
if (Debugger.IsAttached)
{
throw new Exception(ex.Message);
}
else
{
LogHelper.Error(ex, "SQLiteHelper.ExecuteProcedureTran");
}
return(-1);
}
finally
{
tran.Dispose();
cmd.Dispose();
}
}
}
/// <summary>
/// 使用事物执行一组sql语句
/// </summary>
/// <param name="connectionString"></param>
/// <param name="_dicParameters"></param>
/// <returns></returns>
public int ExecuteSqlsByTrans(String connectionString, List <SqlParaEntity> _dicParameters, ref String errorMessage)
{
int result = 1;
SQLiteConnection conn = null;
SQLiteCommand cmd = null;
SQLiteTransaction trans = null;
try
{
conn = new SQLiteConnection(connectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
trans = conn.BeginTransaction();
foreach (SqlParaEntity kv in _dicParameters)
{
cmd = new SQLiteCommand(kv.Sql, conn);
cmd.Parameters.Clear();
if (kv.parameters != null && kv.parameters.Length > 0)
{
cmd.Parameters.AddRange(kv.parameters);
}
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SQLiteException sqlite)
{
errorMessage = sqlite.ToString();
trans.Rollback();
result = 0;
}
catch (Exception ex)
{
errorMessage = ex.ToString();
trans.Rollback();
result = 0;
}
finally
{
if (trans != null)
{
trans.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
return(result);
}
private int batchExecuteSQL(string[] sql)
{
int buffer = 0;
SQLiteConnection _connection = new SQLiteConnection(this._connectionString);
_connection.Open();
SQLiteTransaction trans = _connection.BeginTransaction();
SQLiteCommand cmd = _connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
try
{
for (int i = 0; i < sql.Length; i++)
{
cmd.CommandText = sql[i];
cmd.ExecuteNonQuery();
buffer++;
//触发事件
if (buffer == NotifyAfter)
{
if (RowsCopied != null)
{
RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length));
}
buffer = 0;
}
}
if (buffer != 0)
{
if (RowsCopied != null)
{
RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length));
}
buffer = 0;
}
//提交事务,只有所有的数据都没有问题才提交事务.
trans.Commit();
//异步压缩,分析数据库,确保所得的分析是最佳的.
ThreadPool.QueueUserWorkItem(new WaitCallback((object o) =>
{
StaticSQLiteHelper.ExecuteNonQuery("VACUUM ANALYZE");
}));
}
catch (SQLiteException)
{
trans.Rollback();
return(0);
}
finally
{
trans.Dispose();
cmd.Dispose();
}
return(sql.Length);
}
/// <summary>
/// 事务执行
/// </summary>
/// <param name="SQLString">SQLString</param>
/// <param name="Parameter">参数</param>
/// <param name="Count">执行条数</param>
/// <returns></returns>
public bool BsTranExecute(string SQLString, object Parameter, int Count = 0)
{
ConnectionOpen();
SQLiteTransaction transaction = Connection.BeginTransaction();
try
{
using (Comd = new SQLiteCommand(SQLString))
{
Comd.Parameters.AddRange(Parameter as SQLiteParameter[]);
bool Ruslt = Comd.ExecuteNonQuery() > Count;
if (Ruslt)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
return(Ruslt);
}
}
catch (Exception ex)
{
ReadError(ex.Message);
transaction.Rollback();
return(false);
}
finally
{
transaction.Dispose();
ConnectionClose();
}
}
/// <summary>
/// 方法说明：批量插入大数据
/// </summary>
/// <param name="sql">执行sql</param>
/// <param name="comoanyCode">公司编码</param>
/// <param name="userId">用户ID</param>
/// <returns>是否成功插入数据</returns>
public static bool InsertBigData(string sql, string comoanyCode, string userId)
{
SQLiteTransaction transaction = null;
var conn = new SQLiteConnection(ConnStr(comoanyCode, userId));
var cmd = new SQLiteCommand(sql, conn);
conn.Open();
try
{
lock (Obj)
{
transaction = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
transaction.Commit();
return(true);
}
}
catch (Exception ex)
{
LogHelper.WriteError("[AntSdkSqliteHelper_InsertBigData]:" + ex.Message);
transaction?.Rollback();
return(false);
}
finally
{
conn.Close();
transaction?.Dispose();
conn.Dispose();
}
}
