public async Task AddRoomParticipantAsync(MultiplayerRoom room, MultiplayerRoomUser user) { try { using (var transaction = await connection.BeginTransactionAsync()) { // the user may have previously been in the room and set some scores, so need to update their presence if existing. await connection.ExecuteAsync("INSERT INTO multiplayer_rooms_high (room_id, user_id, in_room) VALUES (@RoomID, @UserID, 1) ON DUPLICATE KEY UPDATE in_room = 1", new { RoomID = room.RoomID, UserID = user.UserID }, transaction); await connection.ExecuteAsync("UPDATE multiplayer_rooms SET participant_count = @Count WHERE id = @RoomID", new { RoomID = room.RoomID, Count = room.Users.Count }, transaction); await transaction.CommitAsync(); } } catch (MySqlException) { // for now we really don't care about failures in this. it's updating display information each time a user joins/quits and doesn't need to be perfect. } }
public async Task BeginTransactionAsync() { if (_transaction != null) { throw new Exception("已经开始了一个事物!"); } _transaction = await _connection.BeginTransactionAsync(); }
public async Task LoadTableAsync(IDataReader reader, string tableName, CancellationToken cc) { using var deleteCommand = _connection.CreateCommand(); deleteCommand.CommandText = $"delete from {tableName}"; await deleteCommand.ExecuteNonQueryAsync(cc); var t = await _connection.BeginTransactionAsync(cc); try { var commands = SplitIntoBatches(reader).Select(x => CreateCommand(x, GetColumnNames(reader), tableName, t)); await Task.WhenAll(commands.Where(x => x != null).Select(x => x.ExecuteNonQueryAsync(cc))); foreach (var c in commands.Where(x => x != null)) { await c.DisposeAsync(); } await t.CommitAsync(cc); } catch (Exception) { await t.RollbackAsync(); throw; } }
public async Task <bool> ExecuteTransAsync(string sql, object param = null, int?second = null) { if (CloseWrite) { return(false); } using (var con = new MySqlConnection(_connectionString)) { con.Open(); var trans = await con.BeginTransactionAsync(); try { await con.ExecuteAsync(sql, param, trans, second); trans.Commit(); return(true); } catch (Exception e) { trans.Rollback(); Log.Error(e); } finally { if (con.State == ConnectionState.Open) { con.Close(); } trans.Dispose(); } return(false); } }
/// <summary> /// Begin a transaction /// </summary> /// <param name="isolationLevel"></param> /// <returns></returns> public async Task <bool> BeginTransactionAsync(IsolationLevel isolationLevel = IsolationLevel.Unspecified) { bool result = true; try { await OpenConnectionAsync(); if (_transaction == null) { _transaction = await Db.BeginTransactionAsync(isolationLevel, _cancellationToken == null?default(CancellationToken) : _cancellationToken); } if (DebugOn) { debugger.WriteLine("Transactino started on:" + Db.Database); } } catch (Exception exc) { if (DebugOn) { debugger.WriteLine("Exception with begin transactino connection:" + exc.Message); } result = false; } return(result); }
public async Task <IActionResult> InsertUserAsync([FromBody] User user) { if (!ModelState.IsValid) { return(BadRequest()); } using var conn = new MySqlConnection(_connStr); await conn.OpenAsync(); MySqlTransaction dbt = await conn.BeginTransactionAsync(); try { await dbt.Connection.ExecuteAsync( "insert into user(`account`, `password`, `email`, `createTime`) values(@account, @password, @email, now());", new { user.Account, user.Password, user.Email } ); } catch (Exception ex) { _logger.LogError(ex.StackTrace); await dbt.RollbackAsync(); await conn.CloseAsync(); return(StatusCode(StatusCodes.Status500InternalServerError)); } return(Ok()); }
public async Task <int> ExecuteAsync(Dictionary <string, object> commands) { await using var conn = new MySqlConnection(_connectionString); await conn.OpenAsync(); await using var transaction = await conn.BeginTransactionAsync(); try { using var batch = conn.CreateBatch(); batch.Transaction = transaction; foreach (var(key, value) in commands) { var command = new MySqlBatchCommand(key); command.SetParameters(value); batch.BatchCommands.Add(command); } var result = await batch.ExecuteNonQueryAsync(); await transaction.CommitAsync(); return(result); } catch (Exception) { await transaction.RollbackAsync(); return(0); } }
public async Task UpdatePatronInfo(IReadOnlyList <PatronInfo> patronInfo) { List <FantasyCriticUserHasRoleEntity> roleEntities = patronInfo .Where(x => x.IsPlusUser) .Select(x => new FantasyCriticUserHasRoleEntity(x.User.Id, 4, true)) .ToList(); List <FantasyCriticUserDonorEntity> donorEntities = patronInfo .Where(x => x.DonorName is not null) .Select(x => new FantasyCriticUserDonorEntity(x.User, x.DonorName !)) .ToList(); await using (var connection = new MySqlConnection(_connectionString)) { await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(); await connection.ExecuteAsync("DELETE FROM tbl_user_hasrole WHERE ProgrammaticallyAssigned = 1;", transaction : transaction); await connection.ExecuteAsync("DELETE FROM tbl_user_donorname;", transaction : transaction); await connection.BulkInsertAsync(roleEntities, "tbl_user_hasrole", 500, transaction, insertIgnore : true); await connection.BulkInsertAsync(donorEntities, "tbl_user_donorname", 500, transaction); await transaction.CommitAsync(); } _userCache = null; }
public override async UniTask UpdateStorageItems(StorageType storageType, string storageOwnerId, IList <CharacterItem> characterItems) { MySqlConnection connection = NewConnection(); await OpenConnection(connection); MySqlTransaction transaction = await connection.BeginTransactionAsync(); try { await DeleteStorageItems(connection, transaction, storageType, storageOwnerId); int i; for (i = 0; i < characterItems.Count; ++i) { await CreateStorageItem(connection, transaction, i, storageType, storageOwnerId, characterItems[i]); } await transaction.CommitAsync(); } catch (System.Exception ex) { Logging.LogError(ToString(), "Transaction, Error occurs while replacing storage items"); Logging.LogException(ToString(), ex); await transaction.RollbackAsync(); } await transaction.DisposeAsync(); await connection.CloseAsync(); }
public async Task UpdateCodeBasedTags(IReadOnlyDictionary <MasterGame, IReadOnlyList <MasterGameTag> > tagsToAdd) { string deleteExistingTagsSQL = "DELETE tbl_mastergame_hastag FROM tbl_mastergame_hastag " + "JOIN tbl_mastergame_tag ON tbl_mastergame_hastag.TagName = tbl_mastergame_tag.Name " + "JOIN tbl_mastergame ON tbl_mastergame_hastag.MasterGameID = tbl_mastergame.MasterGameID " + "WHERE tbl_mastergame_tag.HasCustomCode " + "AND (EarlyAccessReleaseDate IS NOT NULL OR InternationalReleaseDate IS NOT NULL)"; var tagEntities = tagsToAdd .SelectMany(masterGame => masterGame.Value, (masterGame, tag) => new MasterGameHasTagEntity(masterGame.Key, tag)) .ToList(); var excludeFields = new List <string>() { "TimeAdded" }; await using var connection = new MySqlConnection(_connectionString); await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(); await connection.ExecuteAsync(deleteExistingTagsSQL, transaction: transaction); await connection.BulkInsertAsync <MasterGameHasTagEntity>(tagEntities, "tbl_mastergame_hastag", 500, transaction, excludeFields); await transaction.CommitAsync(); }
public async Task <IEnumerable <Models.ItemDL> > InsertListAsync(IEnumerable <Models.ItemDL> items) { using var c = new MySqlConnection(DataFactory.DBConnectionString); c.Open(); var t = await c.BeginTransactionAsync(); try { foreach (var item in items) { using var cmd = c.CreateCommand(); cmd.CommandText = "Item_Insert"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange( DataFactory.CreateParametersWithValue(cmd, new List <(DbType dbType, ParameterDirection direction, string parameterName, object value)> { (DbType.Binary, ParameterDirection.Input, "p" + nameof(item.ItemId), item.ItemId), (DbType.String, ParameterDirection.Input, "p" + nameof(item.Name), item.Name), (DbType.String, ParameterDirection.Input, "p" + nameof(item.Description), item.Description), (DbType.Int16, ParameterDirection.Input, "p" + nameof(item.ShelfLife), item.ShelfLife), (DbType.Decimal, ParameterDirection.Input, "p" + nameof(item.BuyPrice), item.BuyPrice), (DbType.Decimal, ParameterDirection.Input, "p" + nameof(item.SellPrice), item.SellPrice), })); await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); } t.Commit(); return(items); } catch (Exception e) { t.Rollback(); throw; } }
public async Task CleanEmailsAndPasswords(IEnumerable <FantasyCriticUser> allUsers, IEnumerable <FantasyCriticUser> betaUsers) { var nonBetaUsers = allUsers.Except(betaUsers).ToList(); List <string> updateStatements = new List <string>(); foreach (var nonBetaUser in nonBetaUsers) { var fakedEmailAddress = Guid.NewGuid() + "@example.com"; var fakedNormalizedEmailAddress = fakedEmailAddress.ToUpper(); string sql = $"UPDATE tbl_user SET EmailAddress = '{fakedEmailAddress}', NormalizedEmailAddress = '{fakedNormalizedEmailAddress}', PasswordHash = null, AuthenticatorKey = null, IsDeleted = 1 WHERE UserID = '{nonBetaUser.Id}';"; updateStatements.Add(sql); } var batches = updateStatements.Chunk(500).ToList(); await using var connection = new MySqlConnection(_connectionString); await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(); for (var index = 0; index < batches.Count; index++) { _logger.Info($"Running user clean batch {index + 1}/{batches.Count}"); var batch = batches[index]; var joinedSQL = string.Join('\n', batch); await connection.ExecuteAsync(joinedSQL, transaction : transaction); } await transaction.CommitAsync(); }
public async Task CreateMasterGame(MasterGame masterGame) { string masterGameCreateSQL = "insert into tbl_mastergame" + "(MasterGameID,GameName,EstimatedReleaseDate,MinimumReleaseDate,MaximumReleaseDate,EarlyAccessReleaseDate,InternationalReleaseDate,AnnouncementDate," + "ReleaseDate,OpenCriticID,GGToken,CriticScore,Notes,BoxartFileName,GGCoverArtFileName," + "FirstCriticScoreTimestamp,DoNotRefreshDate,DoNotRefreshAnything,EligibilityChanged,DelayContention,AddedTimestamp) VALUES " + "(@MasterGameID,@GameName,@EstimatedReleaseDate,@MinimumReleaseDate,@MaximumReleaseDate,@EarlyAccessReleaseDate,@InternationalReleaseDate,@AnnouncementDate," + "@ReleaseDate,@OpenCriticID,@GGToken,@CriticScore,@Notes,@BoxartFileName,@GGCoverArtFileName," + "@FirstCriticScoreTimestamp,@DoNotRefreshDate,@DoNotRefreshAnything,@EligibilityChanged,@DelayContention,@AddedTimestamp);"; var entity = new MasterGameEntity(masterGame); var tagEntities = masterGame.Tags.Select(x => new MasterGameHasTagEntity(masterGame, x)); var excludeFields = new List <string>() { "TimeAdded" }; await using var connection = new MySqlConnection(_connectionString); await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(); await connection.ExecuteAsync(masterGameCreateSQL, entity, transaction); await connection.BulkInsertAsync <MasterGameHasTagEntity>(tagEntities, "tbl_mastergame_hastag", 500, transaction, excludeFields); await transaction.CommitAsync(); }
/// <summary> /// 入库翻译结果 /// </summary> /// <param name="kvp"></param> static void Result2Db(KeyValuePair <string, string> kvp) { MySqlConnection conn = ConnectionPool.GetPool().GetConnection(); Console.WriteLine("database Connected!"); string update = string.Format("update tb_record set name_uniformed = '{0}' where RECORDID={1} and RECORD_VALID=1", kvp.Value, kvp.Key); MySqlCommand comm = new MySqlCommand(update, conn) { CommandTimeout = 1000000000 }; Task <MySqlTransaction> transaction = conn.BeginTransactionAsync(); comm.Transaction = transaction.Result; try { comm.ExecuteNonQuery(); transaction.Result.Commit(); } catch { transaction.Result.Rollback(); } }
public async Task <IDbTransaction> BeginTransactionAsync(string dbName, IsolationLevel?isolationLevel = null) { MySqlConnection conn = new MySqlConnection(GetConnectionString(dbName, true)); await conn.OpenAsync().ConfigureAwait(false); return(await conn.BeginTransactionAsync(isolationLevel ?? IsolationLevel.RepeatableRead).ConfigureAwait(false)); }
public async Task <string> Test2() { var connectionString = "Server=localhost;Port=63307;Database=test; User=root;Password=123456;pooling=True;minpoolsize=1;maxpoolsize=100;connectiontimeout=180"; using (var sqlConnection = new MySqlConnection(connectionString)) { await sqlConnection.OpenAsync(); var sqlTran = await sqlConnection.BeginTransactionAsync(); var events = new List <EventLogEntry>() { new EventLogEntry("TestEvent", new Events.TestEvent() { EventType = "Test2" }), new EventLogEntry("TestEvent", new { EventType = "Test2" }), }; //保存消息至业务数据库,保证写消息和业务操作在一个事务 await eventLogger.SaveEventAsync(events, sqlTran); var ret = await sqlConnection.ExecuteAsync("you sql code"); return(ret.ToString()); } }
public async Task ReplaceCodesAsync(FantasyCriticUser user, IEnumerable <string> recoveryCodes, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); List <RecoveryCodeEntity> codeEntities = recoveryCodes.Select(x => new RecoveryCodeEntity(user.Id, x)).ToList(); var paramsObject = new { userID = user.Id }; await using (var connection = new MySqlConnection(_connectionString)) { await connection.OpenAsync(cancellationToken); await using var transaction = await connection.BeginTransactionAsync(cancellationToken); await connection.ExecuteAsync("DELETE FROM tbl_user_recoverycode WHERE UserID = @userID;", paramsObject, transaction); await connection.BulkInsertAsync(codeEntities, "tbl_user_recoverycode", 500, transaction); await transaction.CommitAsync(cancellationToken); } _userCache = null; }
public async Task <(string, DateTime)> ValidateRefreshToken(string account, string refreshToken, DateTime refreshTokenLimitTime) { if (refreshToken == null || refreshTokenLimitTime < DateTime.UtcNow) { refreshToken = CreateRefreshToken(); refreshTokenLimitTime = DateTime.UtcNow.AddDays(14); using var conn = new MySqlConnection(_connStr); await conn.OpenAsync(); MySqlTransaction dbt = await conn.BeginTransactionAsync(); try { await dbt.Connection.ExecuteAsync( "update user set refreshToken = @refreshToken, refreshTokenLimitTime = @refreshTokenLimitTime where account = @account;", new { account }); } catch (Exception ex) { _logger.LogError(ex.StackTrace); await dbt.RollbackAsync(); await conn.CloseAsync(); throw new Exception("RefreshToken is not saved."); } _logger.LogInformation($"Refresh token re-issued | Reason: Refresh token expired | Target: {account}"); } return(refreshToken, refreshTokenLimitTime); }
public async Task SetEmailSettings(FantasyCriticUser user, bool sendPublicBidEmails) { var settingsEntities = new List <FantasyCriticUserEmailSettingEntity>(); if (sendPublicBidEmails) { settingsEntities.Add(new FantasyCriticUserEmailSettingEntity(user, EmailType.PublicBids)); } var parameters = new { userID = user.Id }; await using var connection = new MySqlConnection(_connectionString); await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(); await connection.ExecuteAsync("DELETE FROM tbl_user_emailsettings WHERE UserID = @userID;", parameters, transaction : transaction); await connection.BulkInsertAsync(settingsEntities, "tbl_user_emailsettings", 500, transaction); await transaction.CommitAsync(); }
public async Task <IEnumerable <Models.InventoryDL> > InsertAsync(IEnumerable <Models.InventoryDL> inventories) { using var c = new MySqlConnection(DataFactory.DBConnectionString); c.Open(); var t = await c.BeginTransactionAsync(); try { foreach (var inventory in inventories) { using var cmd = c.CreateCommand(); cmd.CommandText = "Inventory_Insert"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange( DataFactory.CreateParametersWithValue(cmd, new List <(DbType dbType, ParameterDirection direction, string parameterName, object value)> { (DbType.Binary, ParameterDirection.Input, "p" + nameof(inventory.InventoryId), inventory.InventoryId), (DbType.Binary, ParameterDirection.Input, "p" + nameof(inventory.ItemId), inventory.ItemId), (DbType.Int16, ParameterDirection.Input, "p" + nameof(inventory.Quantity), inventory.Quantity), (DbType.DateTime, ParameterDirection.Input, "p" + nameof(inventory.Time), inventory.Time), (DbType.Boolean, ParameterDirection.Input, "p" + nameof(inventory.Export), inventory.Export), (DbType.Decimal, ParameterDirection.Input, "p" + nameof(inventory.Monies), inventory.Monies), })); await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); } t.Commit(); return(inventories); } catch (Exception e) { t.Rollback(); throw; } }
/// <inheritdoc /> public override async Task <List <NonQueryResult> > ExecuteTransaction(IsolationLevel isolationLevel, params Query[] queries) { List <NonQueryResult> results = new List <NonQueryResult>(); MySqlTransaction transaction = await conn.BeginTransactionAsync(isolationLevel); foreach (Query query in queries) { try { MySqlCommand cmd = GetCommand(query); cmd.Connection = conn; cmd.Transaction = transaction; int rowsAffected = await cmd.ExecuteNonQueryAsync(); NonQueryResult res = new NonQueryResult(rowsAffected, cmd.LastInsertedId); results.Add(res); } catch (Exception ex) { await transaction.RollbackAsync(); throw ex; } } await transaction.CommitAsync(); return(results); }
public async Task <IDbTransaction> BeginTransactionAsync(string dbName, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted) { MySqlConnection conn = new MySqlConnection(GetConnectionString(dbName, true)); await conn.OpenAsync().ConfigureAwait(false); return(await conn.BeginTransactionAsync(isolationLevel).ConfigureAwait(false)); }
public async Task <int> InsertReturnIdAsync(string sql, object parameter = null) { await using var conn = new MySqlConnection(_connectionString); await conn.OpenAsync(); var transaction = await conn.BeginTransactionAsync(); try { var result = await transaction.ExecuteAsync(sql, parameter); if (result <= 0) { throw new BlogException(500, BlogConstants.CreationError); } var id = await transaction.ExecuteScalarAsync <int>(SqlConstants.GetLastId); await transaction.CommitAsync(); return(id); } catch (Exception e) { await transaction.RollbackAsync(); throw new BlogException(500, "操作执行失败", e); } }
public async Task UpdateRoomParticipantsAsync(MultiplayerRoom room) { try { using (var transaction = await connection.BeginTransactionAsync()) { // This should be considered *very* temporary, and for display purposes only! await connection.ExecuteAsync("DELETE FROM multiplayer_rooms_high WHERE room_id = @RoomID", new { RoomID = room.RoomID }, transaction); foreach (var u in room.Users) { await connection.ExecuteAsync("INSERT INTO multiplayer_rooms_high (room_id, user_id) VALUES (@RoomID, @UserID)", new { RoomID = room.RoomID, UserID = u.UserID }, transaction); } await transaction.CommitAsync(); } await connection.ExecuteAsync("UPDATE multiplayer_rooms SET participant_count = @Count WHERE id = @RoomID", new { RoomID = room.RoomID, Count = room.Users.Count }); } catch (MySqlException) { // for now we really don't care about failures in this. it's updating display information each time a user joins/quits and doesn't need to be perfect. } }
public async Task CreateTransaction(Action <MySqlTransaction> transaction) { using (MySqlTransaction asyncTransaction = await _connection.BeginTransactionAsync()) { transaction(asyncTransaction); } }
public static async Task doInTrasaction(Func <MySqlCommand, Task> action) { using (MySqlConnection conn = newConnection()) using (MySqlCommand cmd = conn.CreateCommand()) { await conn.OpenAsync(); var transaction = await conn.BeginTransactionAsync(); cmd.Transaction = transaction; cmd.Connection = conn; try { await action.Invoke(cmd); await transaction.CommitAsync(); } catch (Exception e) { await transaction.RollbackAsync(); throw e; } } }
public async Task <DbOperationResult> SubmitAsync() { if (_connection.State != ConnectionState.Open) { _connection.Open(); } var tran = await _connection.BeginTransactionAsync(); try { foreach (var command in _commands) { await _connection.ExecuteAsync(command.Sql, command.Parameters, tran, _commandTimeout, command.CommandType); } tran.Commit(); return(new SuccessDbOperationResult()); } catch (Exception ex) { tran.Rollback(); return(new ErrorDbOperationResult(ex)); } }
static async Task DumpTableToStream(string table, bool skipSchema, bool truncate, OutputFormatEnum outputFormat, string selectStatement, MySqlConnection connection, Stream stream) { BaseDumper dumper = outputFormat switch { OutputFormatEnum.mysql => new MySqlDumper(connection), OutputFormatEnum.postgres => new PostgresDumper(connection), OutputFormatEnum.csv => new CsvDumper(connection), _ => throw new ArgumentOutOfRangeException(nameof(outputFormat), outputFormat, null) }; if (!skipSchema) { await dumper.WriteTableSchemaAsync(table, stream); } await dumper.WriteAutoIncrementAsync(table, stream); await stream.WriteToStreamAsync("SET SESSION time_zone = \"+00:00\";\n"); await stream.WriteToStreamAsync("SET SESSION FOREIGN_KEY_CHECKS = 0;\n"); if (truncate) { await dumper.WriteTruncateAsync(table, stream); } await using (var transaction = await connection.BeginTransactionAsync(IsolationLevel.RepeatableRead)) { await dumper.WriteInsertQueries(table, selectStatement, stream, transaction); } }
private async UniTask FillCharacterHotkeys(IPlayerCharacterData characterData) { MySqlConnection connection = NewConnection(); await OpenConnection(connection); MySqlTransaction transaction = await connection.BeginTransactionAsync(); try { await DeleteCharacterHotkeys(connection, transaction, characterData.Id); int i; for (i = 0; i < characterData.Hotkeys.Count; ++i) { await CreateCharacterHotkey(connection, transaction, characterData.Id, characterData.Hotkeys[i]); } await transaction.CommitAsync(); } catch (System.Exception ex) { Logging.LogError(ToString(), "Transaction, Error occurs while replacing hotkeys of character: " + characterData.Id); Logging.LogException(ToString(), ex); await transaction.RollbackAsync(); } await transaction.DisposeAsync(); await connection.CloseAsync(); }
protected async ValueTask <DataTable> SelectTable(MySqlCommand cmd, IsolationLevel IsolationLevel = IsolationLevel.ReadCommitted) { try { DataTable ret; using (MySqlConnection conn = NewConnection()) { await conn.OpenAsync().ConfigureAwait(false); using (MySqlTransaction tran = await conn.BeginTransactionAsync(IsolationLevel).ConfigureAwait(false)) { cmd.Connection = conn; cmd.Transaction = tran; using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd)) { ret = new DataTable(); adapter.Fill(ret); } await tran.CommitAsync().ConfigureAwait(false); } } return(ret); } catch { return(null); } }