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