/// <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(); } }
/// <summary> /// Execute Insert Query - Async /// </summary> /// <param name="query">Query</param> /// <param name="parameters">Optional. Parameters</param> /// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param> /// <returns>Inseted Id</returns> public static async Task <int> ExecuteInsertQueryAsync(string query, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false) { int insertedId = -1; await Task.Run(() => { using (SQLiteConnection conn = GetConnection) { using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.CommandText = query; if (parameters != null && parameters.Count() > 0) { foreach (KeyValuePair <string, object> parameter in parameters) { cmd.Parameters.AddWithValue(parameter.Key, parameter.Value); } } cmd.Prepare(); lock (readWriteLock) { conn.Open(); SQLiteTransaction transaction = null; try { if (isNeedTransactionBlock) { transaction = conn.BeginTransaction(); } int changesCount = cmd.ExecuteNonQuery(); if (changesCount > 0) { cmd.CommandText = LastInsertedIdQuery; cmd.Prepare(); insertedId = int.Parse(cmd.ExecuteScalar().ToString()); } transaction?.Commit(); } catch { transaction?.Rollback(); applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error"); throw; } finally { conn.Close(); } } } } }); return(insertedId); }
/// <summary> /// Metodo Comunes para Insertar, Actualizar y Eliminar /// </summary> /// <param name="SQL"></param> /// <returns></returns> private bool Execute(string SQL, SQLiteParameter[] param, string nameConsulta) { bool error = true; if (this.Conectar()) { SQLiteTransaction trans = Cx.BeginTransaction(); try { Cmd.CommandText = SQL; if (param != null) { Cmd.Parameters.AddRange(param); } if (Cmd.ExecuteNonQuery() == -1) { this.Log.Write("* " + nameConsulta + " 0 rows afected"); this.Mensaje = "No hay Columnas Afectadas"; error = false; } trans.Commit(); this.Log.Write("* " + nameConsulta + " Ok"); } catch (SQLiteException ex) { trans.Rollback(); this.Log.Write("* " + nameConsulta + "\n" + ex.ToString()); this.Mensaje = ex.ToString(); error = false; } catch (NullReferenceException ex) { trans.Rollback(); this.Log.Write("* " + nameConsulta + "\n" + ex.ToString()); this.Mensaje = ex.ToString(); error = false; } finally { Desconectar(); } } return(error); }
/// <summary> /// 新增一星走势数据 /// </summary> /// <param name="type"></param> /// <param name="tendencys"></param> public static void SaveTendency1(Tendency1Enum type, List <TendencyModel> tendencys) { string tableName = getTendency1Table(type); using (SQLiteConnection conn = CreateConnection()) { conn.Open(); SQLiteTransaction trans = conn.BeginTransaction(); string sqlCount = string.Format("SELECT count(1) FROM {0} where Ymd = @Ymd and Sno=@Sno", tableName); try { string sql = null; for (int i = 0; i < tendencys.Count; i++) { TendencyModel lm = tendencys[i]; int count = conn.Query <int>(sqlCount, lm).FirstOrDefault(); if (count == 0) { ///新增 sql = string.Format("insert into {0}(Ymd ," + @"Sno ," + @"Lottery ," + @"Big ," + @"Small ," + @"Odd ," + @"Pair ," + @"Dtime )" + @" VALUES (" + @"@Ymd ," + @"@Sno ," + @"@Lottery ," + @"@Big ," + @"@Small ," + @"@Odd ," + @"@Pair ," + @"@Dtime " + @")", tableName); } else { ///修改 sql = string.Format("Update {0} set Big =@Big ," + "Small =@Small ," + "Odd =@Odd ," + "Pair =@Pair " + "where Ymd = @Ymd and Sno=@Sno ", tableName); } conn.Execute(sql, lm, trans); } trans.Commit(); } catch (Exception) { trans.Rollback(); } } }
/// <summary> /// Execute Query Async /// </summary> /// <typeparam name="T">Type of list need to returned</typeparam> /// <param name="query">Query</param> /// <param name="readerFunc">Reader function</param> /// <param name="parameters">Optional. Parameters</param> /// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param> /// <returns>List of T</returns> public static async Task <IEnumerable <T> > ExecuteQueryAsync <T>(string query, Func <SQLiteDataReader, T> readerFunc, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false) { IList <T> items = new List <T>(); await Task.Run(() => { using (SQLiteConnection conn = GetConnection) { using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.CommandText = query; if (parameters != null && parameters.Count() > 0) { foreach (KeyValuePair <string, object> parameter in parameters) { cmd.Parameters.AddWithValue(parameter.Key, parameter.Value); } } cmd.Prepare(); lock (readWriteLock) { conn.Open(); SQLiteTransaction transaction = null; try { if (isNeedTransactionBlock) { transaction = conn.BeginTransaction(); } using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { items.Add(readerFunc(reader)); } } transaction?.Commit(); } catch { transaction?.Rollback(); applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error"); throw; } finally { conn.Close(); } } } } }); return(items); }
private void btnEdit_Click(object sender, EventArgs e) { Customer customer = new Customer(); customer.LastName = this.TxtBoxLastName.Text; customer.FirstName = this.TxtBoxFirstName.Text; customer.Cnp = this.TxtBoxCnp.Text; customer.BirthDate = this.DpBirthdate.Text; Dictionary <String, String> errors = Validations.validateCustInfo(customer); if (errors.Count != 0) { String errorMessage = null; errors.TryGetValue(errors.Keys.First <string>(), out errorMessage); MessageBox.Show(errorMessage); return; } customer.Phone = this.TxtBoxPhone.Text; customer.Email = this.TxtBoxEmail.Text; customer.Country = this.TxtBoxCountry.Text; customer.County = this.TxtBoxCounty.Text; customer.City = this.TxtBoxCity.Text; customer.Locality = this.TxtBoxLocality.Text; customer.Street = this.TxtBoxStreet.Text; customer.StreetNo = Int32.Parse(this.NudStreetNo.Value.ToString()); errors = Validations.validateCustDetails(customer); if (errors.Count != 0) { String errorMessage = null; errors.TryGetValue(errors.Keys.First <string>(), out errorMessage); MessageBox.Show(errorMessage); return; } if (errors.Count == 0) { SQLiteConnection connection = DatabaseConnection.getConnection(); connection.Open(); SQLiteTransaction transaction = connection.BeginTransaction(); ICustomersDAO customersDao = new CustomersDAO(); try { customersDao.updateCustomer(customer, connection); transaction.Commit(); MessageBox.Show("The Customer was successfully added!"); } catch (SQLiteException exception) { Console.WriteLine(exception.Message); transaction.Rollback(); MessageBox.Show("The Customer can't be update!"); } connection.Close(); } }
public void Rollback() { if (_transaction != null) { _transaction.Rollback(); } _transaction = null; }
public override void RollbackTransaction() { if (_transaction != null) { _transaction.Rollback(); } GetConnectionBase().EndUse(); }
public int EndTransaction(bool success) //-1 error, 1 - success { if (trans == null) { return(-1); } if (success) { trans.Commit(); } else { trans.Rollback(); } trans = null; return(1); }
public void Rollback() { if (_IsRunTrans) { _SQLiteTrans.Rollback(); _IsRunTrans = false; } }
public void Rollback() { _isRunningWithinTransaction = false; _sqLiteTransaction.Rollback(); _sqLiteTransaction.Dispose(); _sqliteConnection.Close(); _sqliteConnection.Dispose(); }
/// <summary> /// 回滚事务 /// </summary> public void RollbackTran() { if (_tran != null) { _tran.Rollback(); _tran = null; } }
public void RollbackTrans() { if (isStartTrans) { trans.Rollback(); } isStartTrans = false; }
public static void Rollback() { if (ts != null) { ts.Rollback(); ts = null; } }
public void Rollback() { if (null != _Transaction) { _Transaction.Rollback(); _Transaction = null; } }
private object ReadItem(string itemName, object defaultValue) { SQLiteConnection cnn = null; SQLiteTransaction trn = null; SQLiteCommand cmd = null; try { cnn = new SQLiteConnection(_connectionString); cnn.Open(); trn = cnn.BeginTransaction(); cmd = new SQLiteCommand(); cmd.Connection = cnn; cmd.Transaction = trn; cmd.CommandText = "select count(name) from scalars where key = ? and name = ?"; cmd.Parameters.AddWithValue("key", _sectionKey); cmd.Parameters.AddWithValue("name", itemName); long count = (long)cmd.ExecuteScalar(); if (count < 1) { cmd.CommandText = "insert into scalars (key, name, value) values (?, ?, ?)"; cmd.Parameters.AddWithValue("value", defaultValue); cmd.ExecuteNonQuery(); } cmd.CommandText = "select value from scalars where key = ? and name = ?"; object val = cmd.ExecuteScalar(); trn.Commit(); return(val); } catch { trn.Rollback(); throw; } finally { if (cmd != null) { cmd.Dispose(); } if (trn != null) { trn.Dispose(); } if (cnn != null) { cnn.Dispose(); } } }
private static void createDatabase() { string executableLocation = Path.GetDirectoryName( Assembly.GetExecutingAssembly().Location); string dbLocation = Path.Combine(executableLocation, dbFileName); Dictionary <string, string> neededTables = new Dictionary <string, string> { { "character", db_createCharacterTable }, { "round", db_createRoundTable }, { "match", db_createMatchTable }, { "player", db_createPlayerTable }, { "alias", db_createAliasTable } }; if (!File.Exists(dbLocation)) { SQLiteConnection.CreateFile(dbFileName); SQLiteTransaction transaction = BeginTransaction(); try { foreach (var nt in neededTables) { NonQuery(nt.Value); } transaction.Commit(); } catch (Exception) { transaction.Rollback(); } Program.Log("DB file created"); } else { Program.Log("DB file already exists"); List <string> tables = GetTableList(); SQLiteTransaction transaction = BeginTransaction(); try { foreach (var nt in neededTables) { bool f = false; foreach (string t in tables) { if (nt.Key == t) { f = true; break; } } // create new table if not found if (!f) { NonQuery(nt.Value); } } transaction.Commit(); } catch (Exception) { transaction.Rollback(); } } }
static void runUpdateOperation() { try { using (SQLiteConnection connection = new SQLiteConnection(connectionStr_)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { try { using (SQLiteCommand command = new SQLiteCommand(connection)) { string sql = string.Format("UPDATE {0} SET hexchars = '0F0F0F' WHERE name = 'Color #01'", colorsTable_); command.CommandText = sql; command.ExecuteNonQuery(); } transaction.Commit(); } catch (System.Data.SQLite.SQLiteException e) { Console.WriteLine("SQLite error: {0}", e.Message); transaction.Rollback(); //throw; } } using (SQLiteTransaction transaction = connection.BeginTransaction()) { try { using (SQLiteCommand command = new SQLiteCommand(connection)) { string sql = string.Format("UPDATE {0} SET hexchars = '0F0F0F' WHERE name = 'Color #20'", colorsTable_); // a new record is not inserted. command.CommandText = sql; command.ExecuteNonQuery(); } transaction.Commit(); } catch (System.Data.SQLite.SQLiteException e) { Console.WriteLine("SQLite error: {0}", e.Message); transaction.Rollback(); //throw; } } } } catch (System.Data.SQLite.SQLiteException e) { Console.WriteLine("SQLite error: {0}", e.Message); } }
//////////////////////////////////////////////////////// // Delete Profiles // //----------------------------------------------------// public override int DeleteProfiles(string[] usernames) { //SecUtility.CheckArrayParameter(ref usernames, true,true,true,255,"usernames"); SQLiteConnection holder = new SQLiteConnection(_connectionString); SQLiteTransaction trans = null; try { int numDeleted = 0; bool fBeginTransCalled = false; try { holder.Open(); trans = holder.BeginTransaction(); fBeginTransCalled = true; int appId = GetApplicationId(holder); foreach (string username in usernames) { if (DeleteProfile(holder, username, appId)) { numDeleted++; } } trans.Commit(); fBeginTransCalled = false; } catch (Exception e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "Delete Profiles"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (fBeginTransCalled) { try { trans.Rollback(); } catch { } } holder.Close(); } return(numDeleted); } catch { throw; } }
public DataSet DeleteForm1(string FormID, string spName, string fldvalue, string visitid) { SQLiteCommand cmd = null; CConnection cn = null; SQLiteDataAdapter da = null; DataSet ds = null; SQLiteTransaction trans = null; try { cn = new CConnection(); cn.MConnOpen(); trans = cn.cn.BeginTransaction(); cmd = new SQLiteCommand(); cmd.Connection = cn.cn; cmd.CommandText = spName; cmd.Transaction = trans; cmd.CommandType = CommandType.StoredProcedure; if (FormID == "") { cmd.Parameters.AddWithValue("FormID", "NULL"); } else { cmd.Parameters.AddWithValue("FormID", FormID); } cmd.Parameters.AddWithValue("fldvalue", fldvalue); cmd.Parameters.AddWithValue("visitid", visitid); da = new SQLiteDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); trans.Commit(); } catch (Exception ex) { trans.Rollback(); MessageBox.Show(ex.Message); } finally { cn.MConnClose(); cmd = null; cn = null; } return(ds); }
/// <summary> /// ロールバック /// </summary> /// <remarks></remarks> public void Rollback() { if (_trans != null) { _trans.Rollback(); _logger.Info("Rollback:" + _trans.ToString()); _trans = null; } }
public override bool TransactionRollback() { if (_transaction == null) { return(false); } _transaction.Rollback(); return(true); }
public static int Delete(string deleteQuery, params object[] parameters) { using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { try { SQLiteCommand cmd = new SQLiteCommand(deleteQuery, connection); cmd.Transaction = transaction; int i = 0; object name = null; object value = null; foreach (object o in parameters) { if (i % 2 == 0) { name = o; } else { value = o; cmd.Parameters.AddWithValue(name.ToString(), value); } i++; } cmd.ExecuteNonQuery(); int rowsAffected = connection.Changes; transaction.Commit(); return(rowsAffected); } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } throw ex; } finally { if (connection != null) { connection.Close(); } } } } }
public int ExecuteNonQuery() { int returnResult; try { using (connection = new SQLiteConnection(ConnectionString)) { if (connection.State == ConnectionState.Closed) { connection.Open(); } if (command == null) { connection.Close(); return(0); } if (QueryParameters.Count > 0 && BindParametersToQuery == true) { command.Parameters.AddRange(QueryParameters.ToArray()); } command.Connection = connection; if (TransActional) { transaction = connection.BeginTransaction(transactionIsolation); command.Transaction = transaction; } returnResult = command.ExecuteNonQuery(); if (TransActional) { transaction.Commit(); } } } catch (SQLiteException ex) { //Will be a good idea to add fault logging here; returnResult = 0; try { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. transaction.Rollback(); } catch (Exception) { } } finally { command.Dispose(); CleanProvider(); } return(returnResult); }
public void Rollback() { if (tran != null) { tran.Rollback(); tran.Dispose(); tran = null; } }
public void UninstallGame(Game game) { if (!_cachedGames.Any(g => g.Id == game.Id)) { return; } SQLiteTransaction trans = null; try { trans = DatabaseConnection.BeginTransaction(); #region remove the custom properties from the database using (SQLiteCommand com = DatabaseConnection.CreateCommand()) { //Build Query com.CommandText = @"DELETE FROM [custom_properties] WHERE [game_id]=@id;"; com.Parameters.AddWithValue("@id", game.Id.ToString()); com.ExecuteNonQuery(); } #endregion #region remove the game from the database using (SQLiteCommand com = DatabaseConnection.CreateCommand()) { //Build Query com.CommandText = @"DELETE FROM [games] WHERE [id]=@id;"; com.Parameters.AddWithValue("@id", game.Id.ToString()); com.ExecuteNonQuery(); } #endregion //remove obsolete columns from the cards table DatabaseHandler.RebuildCardTable(DatabaseConnection); trans.Commit(); } catch (Exception ex) { if (trans != null) { trans.Rollback(); } if (Debugger.IsAttached) { Debugger.Break(); } return; } var existingGame = _cachedGames.FirstOrDefault(g => g.Id == game.Id); if (existingGame != null) { _cachedGames.Remove(existingGame); } }
public void Rollback() { lock (transactionLock) { Transaction.Rollback(); Transaction.Dispose(); Transaction = null; } }
public void Rollback() { if (_dbConnection == null || _transaction == null) { return; } _transaction.Rollback(); _transaction = null; }
private void RollBackTransaction() { if (transaction != null) { transaction.Rollback(); } isTransactionStart = false; CloseConnection(); }
public void rollback() { if (currTrans == null) { return; } currTrans.Rollback(); currTrans = null; }