示例#1
0
        public async Task <ExecutionDataContext> GetContextAsync(DatabaseConfiguration dbConfig, CancellationToken cancellation)
        {
            _logger.Trace($"Getting a connection for database: '{dbConfig.Name}'");
            if (dbConfig == null)
            {
                throw new ArgumentNullException(nameof(dbConfig));
            }
            if (Pool.ContainsKey(dbConfig.Name))
            {
                return(Pool[dbConfig.Name]);
            }
            var build = new NpgsqlConnectionStringBuilder(dbConfig.ConnectionString)
            {
                Password            = dbConfig.Password,
                IncludeErrorDetails = true
            };
            var connection = new NpgsqlConnection(build.ConnectionString);

            connection.Open();
            var transaction = await connection.BeginTransactionAsync(cancellation);

            var context = new ExecutionDataContext(connection, transaction);

            Pool.Add(dbConfig.Name, context);
            return(context);
        }
示例#2
0
        public async Task ActivityCompensateAborted(string activityId, string sagaId, string errors)
        {
            using (var connection = new NpgsqlConnection(poleSagasStoragePostgreSqlOption.ConnectionString))
            {
                await connection.OpenAsync();

                using (var tansaction = await connection.BeginTransactionAsync())
                {
                    var updateActivitySql =
                        $"UPDATE {activityTableName} SET \"Status\"=@Status,\"CompensateErrors\"=@Errors, \"CompensateTimes\"=\"CompensateTimes\"+1 WHERE \"Id\" = @Id";
                    await connection.ExecuteAsync(updateActivitySql, new
                    {
                        Id     = activityId,
                        Errors = errors,
                        Status = nameof(ActivityStatus.CompensateAborted)
                    }, tansaction);

                    if (!string.IsNullOrEmpty(sagaId))
                    {
                        var updateSagaSql =
                            $"UPDATE {sagaTableName} SET \"Status\"=@Status WHERE \"Id\" = @Id";
                        await connection.ExecuteAsync(updateSagaSql, new
                        {
                            Id     = sagaId,
                            Status = nameof(SagaStatus.Error)
                        }, tansaction);
                    }
                    await tansaction.CommitAsync();
                }
            }
        }
示例#3
0
        public async ValueTask <bool?> RemoveUserRoles(long userId, string[] roles)
        {
            using (var conn = new NpgsqlConnection(_connectionString)) {
                await conn.OpenAsync().NoSync();

                var transaction = await conn.BeginTransactionAsync().NoSync();

                await using (transaction.NoSync()) {
                    bool allRemoved = true;
                    bool allFailed  = true;
                    foreach (var role in roles)
                    {
                        using (var cmd = new NpgsqlCommand("DELETE FROM \"UserRole\" WHERE \"UserId\" = @userId AND \"Role\" = @role;", conn, transaction)) {
                            cmd.Parameters.AddWithValue("@userId", userId);
                            cmd.Parameters.AddWithValue("@role", role);
                            int x = await cmd.ExecuteNonQueryAsync().NoSync();

                            if (x == 0)
                            {
                                allRemoved = false;
                            }
                            else
                            {
                                allFailed = false;
                            }
                        }
                    }

                    await transaction.CommitAsync().NoSync();

                    return(allRemoved ? true : (allFailed ? false : null));
                }
            }
        }
        public async Task Save(ShoppingCart shoppingCart)
        {
            await using var conn = new NpgsqlConnection(ConnectionString);
            await conn.OpenAsync();

            await using var tx = await conn.BeginTransactionAsync();

            await conn.ExecuteAsync(DeleteAllForShoppingCartSql, new { shoppingCart.Id }, tx)
            .ConfigureAwait(false);

            foreach (var item in shoppingCart.Items)
            {
                item.Price ??= new Money("none", 1m);
                // Console.WriteLine("Item: {0}, {1}, {2}, {3}, {4}, {5}", shoppingCart.Id, item.ProductCatalogId,
                //     item.ProductName, item.ProductDescription, item.Price.Amount, item.Price.Currency);
            }

            var items = shoppingCart.Items.Select(item => new
            {
                shoppingCartId = shoppingCart.Id,
                item.ProductCatalogId,
                item.ProductName,
                item.ProductDescription,
                item.Price.Amount,
                item.Price.Currency
            });

            await conn.ExecuteAsync(AddAllForShoppingCartSql, items, tx)
            .ConfigureAwait(false);

            await tx.CommitAsync();
        }
示例#5
0
        public async ValueTask ReplaceUserRoles(long userId, string[] roles)
        {
            using (var conn = new NpgsqlConnection(_connectionString)) {
                await conn.OpenAsync().NoSync();

                var transaction = await conn.BeginTransactionAsync().NoSync();

                await using (transaction.NoSync()) {
                    using (var cmd = new NpgsqlCommand("DELETE FROM \"UserRole\" WHERE \"UserId\" = @userId;", conn, transaction)) {
                        cmd.Parameters.AddWithValue("@userId", userId);
                        await cmd.ExecuteNonQueryAsync().NoSync();
                    }

                    foreach (var role in roles)
                    {
                        using (var cmd = new NpgsqlCommand("INSERT INTO \"UserRole\" (\"UserId\", \"Role\") VALUES (@userId, @role);", conn, transaction)) {
                            cmd.Parameters.AddWithValue("@userId", userId);
                            cmd.Parameters.AddWithValue("@role", role);
                            await cmd.ExecuteNonQueryAsync().NoSync();
                        }
                    }

                    await transaction.CommitAsync().NoSync();
                }
            }
        }
        private static async Task <string> Run(Server server, string query, Func <NpgsqlConnection, Task <string> > action)
        {
            await using var connection = new NpgsqlConnection(server.AsConnectionString());
            await connection.OpenAsync();

            await using var transaction = await connection.BeginTransactionAsync();

            try
            {
                var result = await action(connection);

                await transaction.CommitAsync();

                return(result);
            }
            catch (PostgresException)
            {
                throw;
            }
            catch (Exception e)
            {
                await transaction.RollbackAsync();

                throw new Exception($"Failed to execute query {query} for server {server.Host}: {e.Message}");
            }
            finally
            {
                await connection.CloseAsync();
            }
        }
        private async Task FlushUpdates(NpgsqlConnection conn, List <EntityUpdate> streamUpdates, List <GameUpdate> gameUpdates, List <EntityUpdate> miscUpdates)
        {
            var sw = new Stopwatch();

            sw.Start();
            await using (var tx = await conn.BeginTransactionAsync())
            {
                var streamRes = await _updateStore.SaveUpdates(conn, streamUpdates, false);

                await _gameUpdateStore.SaveGameUpdates(conn, gameUpdates, false, false);

                var miscRes = await _updateStore.SaveUpdates(conn, miscUpdates, false);

                await tx.CommitAsync();

                sw.Stop();

                if (streamUpdates.Count > 0)
                {
                    var currentTime = streamUpdates.Min(su => su.Timestamp);
                    _logger.Information(
                        "- @ {CurrentTime}: Imported {StreamUpdates} stream updates, {GameUpdates} game updates, {MiscUpdates} misc updates (in {Duration})",
                        currentTime, streamRes, gameUpdates.Count, miscRes, sw.Elapsed);
                }
            }

            streamUpdates.Clear();
            gameUpdates.Clear();
            miscUpdates.Clear();
        }
示例#8
0
        public async ValueTask <long> CreateSecret(long userId, string name, string description)
        {
            using (var conn = new NpgsqlConnection(_connectionString)) {
                await conn.OpenAsync().NoSync();

                var transaction = await conn.BeginTransactionAsync().NoSync();

                await using (transaction.NoSync()) {
                    long secretId;
                    using (var cmd = new NpgsqlCommand("INSERT INTO \"Secret\" (\"Name\", \"Description\", \"CreateDate\", \"CreatorUserId\") VALUES (@name, @description, @createDate, @creatorUserId) RETURNING \"Id\";", conn, transaction)) {
                        cmd.Parameters.AddWithValue("@name", name);
                        cmd.Parameters.AddWithValue("@description", string.IsNullOrEmpty(description) ? DBNull.Value : description);
                        cmd.Parameters.AddWithValue("@createDate", DateTime.UtcNow);
                        cmd.Parameters.AddWithValue("@creatorUserId", userId);
                        secretId = (long)await cmd.ExecuteScalarAsync().NoSync();
                    }

                    using (var cmd = new NpgsqlCommand("INSERT INTO \"SecretAccess\" (\"SecretId\", \"UserId\", \"Read\", \"Write\", \"Assign\") VALUES (@secretId, @userId, @read, @write, @assign);", conn, transaction)) {
                        cmd.Parameters.AddWithValue("@secretId", secretId);
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@read", true);
                        cmd.Parameters.AddWithValue("@write", true);
                        cmd.Parameters.AddWithValue("@assign", true);
                        await cmd.ExecuteNonQueryAsync().NoSync();
                    }

                    await transaction.CommitAsync().NoSync();

                    return(secretId);
                }
            }
        }
    async Task InitSchema()
    {
        const string schemaSetup = @"
                    CREATE SCHEMA topos;
                    CREATE TABLE topos.position_manager (
                        consumer_group varchar(255),
                        topic varchar(255),
                        partition integer,
                        position bigint,
                        PRIMARY KEY(consumer_group, topic, partition)
                     );";

        await using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();

        await using var transaction = await connection.BeginTransactionAsync();

        await using var cmd = new NpgsqlCommand(schemaSetup, connection, transaction);

        var result = await cmd.ExecuteNonQueryAsync();

        if (result == 0)
        {
            await transaction.RollbackAsync();

            throw new Exception("Failed setting up schema and table");
        }

        await transaction.CommitAsync();
    }
示例#10
0
        public new async Task Delete(Guid id)
        {
            await using var conn = new NpgsqlConnection(ConnectionString);
            await conn.OpenAsync().ConfigureAwait(false);

            var transaction = await conn.BeginTransactionAsync().ConfigureAwait(false);

            await using (transaction)
            {
                await conn.ExecuteAsync($@"delete from {PgSchema.course} where id = @Id", new { id }).ConfigureAwait(false);

                await conn.ExecuteAsync($@"delete from {PgSchema.course_index} where id = @Id", new { id }).ConfigureAwait(false);

                var courseTaskIds = await conn.QueryAsync <Guid>(
                    $@"select task_id
                           from {PgSchema.course_tasks}
                           where course_id = @Id", new { id }).ConfigureAwait(false);

                foreach (var taskId in courseTaskIds)
                {
                    await taskService.Delete(taskId).ConfigureAwait(false);
                }

                await conn.ExecuteAsync($@"delete from {PgSchema.course_tasks} where course_id = @Id", new { id }).ConfigureAwait(false);

                await transaction.CommitAsync().ConfigureAwait(false);
            }
        }
示例#11
0
        public async ValueTask <bool> DeleteSecretData(long userId, long secretId, string name)
        {
            using (var conn = new NpgsqlConnection(_connectionString)) {
                await conn.OpenAsync().NoSync();

                var transaction = await conn.BeginTransactionAsync().NoSync();

                await using (transaction.NoSync()) {
                    bool deleted = false;
                    var  result  = new List <(long secretId, string name)>();
                    using (var cmd = new NpgsqlCommand("DELETE FROM \"SecretData\" WHERE \"SecretId\" = @secretId AND \"Name\" " + (string.IsNullOrEmpty(name) ? "IS NULL" : "= @name") + ";", conn, transaction)) {
                        cmd.Parameters.AddWithValue("@secretId", secretId);
                        if (!string.IsNullOrEmpty(name))
                        {
                            cmd.Parameters.AddWithValue("@name", name);
                        }
                        deleted = await cmd.ExecuteNonQueryAsync().NoSync() > 0;
                    }

                    if (deleted)
                    {
                        using (var cmd = new NpgsqlCommand("UPDATE \"Secret\" SET \"LastUpdateDate\" = @lastUpdateData, \"LastUpdateUserId\" = @lastUpdateUserId WHERE \"id\" = @secretId;", conn, transaction)) {
                            cmd.Parameters.AddWithValue("@secretId", secretId);
                            cmd.Parameters.AddWithValue("@lastUpdateData", DateTime.UtcNow);
                            cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
                        }

                        await transaction.CommitAsync().NoSync();
                    }
                    return(deleted);
                }
            }
        }
        public async Task TestPostgresCommandAutomaticallyParticipatesInTransaction()
        {
            using var connection = new NpgsqlConnection(TestingPostgresDb.DefaultConnectionString);
            await connection.OpenAsync();

            using var transaction =
#if NETCOREAPP3_1
                      await connection.BeginTransactionAsync();
#elif NET471
                      connection.BeginTransaction();
#endif

            using var commandInTransaction   = connection.CreateCommand();
            commandInTransaction.Transaction = transaction;
            commandInTransaction.CommandText = @"SHOW statement_timeout; CREATE TABLE foo (id INT); SET LOCAL statement_timeout = 2020;";
            (await commandInTransaction.ExecuteScalarAsync()).ShouldEqual("0");

            using var commandOutsideTransaction = connection.CreateCommand();
            Assert.IsNull(commandOutsideTransaction.Transaction);
            commandOutsideTransaction.CommandText = "SELECT COUNT(*) FROM foo";
            (await commandOutsideTransaction.ExecuteScalarAsync()).ShouldEqual(0);

            commandOutsideTransaction.CommandText = "SHOW statement_timeout";
            (await commandOutsideTransaction.ExecuteScalarAsync()).ShouldEqual("2020ms");

            commandInTransaction.CommandText = "SELECT COUNT(*) FROM foo";
            (await commandInTransaction.ExecuteScalarAsync()).ShouldEqual(0);

            commandInTransaction.CommandText = "SHOW statement_timeout";
            (await commandInTransaction.ExecuteScalarAsync()).ShouldEqual("2020ms");
        }
示例#13
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="connection"></param>
        /// <param name="executeAsync"></param>
        /// <param name="transaction"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        private static async Task <TResult> TransactionalExecuteAsync <TResult>(this NpgsqlConnection connection,
                                                                                Func <Task <TResult> > executeAsync,
                                                                                NpgsqlTransaction transaction,
                                                                                CancellationToken cancellationToken = default)
        {
            // Variables
            var result         = default(TResult);
            var hasTransaction = transaction != null;

            // Open
            await connection.EnsureOpenAsync(cancellationToken);

            // Ensure transaction
            if (hasTransaction == false)
            {
#if NET5_0
                transaction = await connection.BeginTransactionAsync(cancellationToken);
#else
                transaction = connection.BeginTransaction();
#endif
            }

            try
            {
                // Execute
                if (executeAsync != null)
                {
                    result = await executeAsync();
                }

                // Commit
                if (hasTransaction == false)
                {
                    await transaction.CommitAsync(cancellationToken);
                }
            }
            catch
            {
                // Rollback
                if (hasTransaction == false)
                {
                    await transaction.RollbackAsync(cancellationToken);
                }

                // Throw
                throw;
            }
            finally
            {
                // Dispose
                if (hasTransaction == false)
                {
                    await transaction.DisposeAsync();
                }
            }

            // Return
            return(result);
        }
示例#14
0
        public async Task <IOutboxUnitOfWork> Begin(CancellationToken cancellationToken)
        {
            var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var transaction = await connection.BeginTransactionAsync(cancellationToken);

            return(new OutboxUnitOfWork(connection, transaction));
        }
示例#15
0
        public async Task FinishRound()
        {
            var activeRound = await GetCurrentActiveRound();

            if (!activeRound.HasValue)
            {
                throw new Exception("Attempting to finish non-active round.");
            }

            var(round, _, __, ___) = activeRound.Value;

            var prevRound = await RecoverRoundData();

            {
                var tx = await conn.BeginTransactionAsync();

                {
                    await using var cmd = new NpgsqlCommand("UPDATE Round SET elo_gain = @elogain, elo = @elo, games = @games WHERE team = @team AND round = @round", conn);
                    cmd.Parameters.Add("elogain", NpgsqlTypes.NpgsqlDbType.Double);
                    cmd.Parameters.Add("elo", NpgsqlTypes.NpgsqlDbType.Double);
                    cmd.Parameters.Add("games", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer);
                    cmd.Parameters.Add("team", NpgsqlTypes.NpgsqlDbType.Integer);
                    cmd.Parameters.Add("round", NpgsqlTypes.NpgsqlDbType.Integer);
                    foreach (var team in prevRound.Teams.Values)
                    {
                        cmd.Parameters[0].Value = team.PendingElo - team.StartingElo;
                        cmd.Parameters[1].Value = team.PendingElo;
                        cmd.Parameters[2].Value = team.Games.ToArray();
                        cmd.Parameters[3].Value = team.Id;
                        cmd.Parameters[4].Value = round;
                        await cmd.ExecuteNonQueryAsync();
                    }
                }

                {
                    await using var cmd = new NpgsqlCommand("UPDATE Tournament SET actual_end_time = @time WHERE round = @round", conn);
                    cmd.Parameters.AddWithValue("time", DateTime.Now.ToUnixMillis());
                    cmd.Parameters.AddWithValue("round", round);
                    await cmd.ExecuteNonQueryAsync();
                }

                await tx.CommitAsync();
            }
        }
        async Task ExecuteTransation(NpgsqlConnection c, string sql, IsolationLevel level)
        {
            await using var transaction =
                            await c.BeginTransactionAsync(level, _cancel.Token);

            await c.ExecuteAsync(
                new CommandDefinition(sql, transaction, cancellationToken : _cancel.Token));

            await transaction.CommitAsync(_cancel.Token);
        }
示例#17
0
        public async Task <IEnumerable <TransactionRecord> > GetTransactionsAsync(PagedTransactionRequest request)
        {
            using (var connection = new NpgsqlConnection(_connectionString))
            {
                await connection.OpenAsync();

                List <TransactionRecord> results = new List <TransactionRecord>();
                try
                {
                    using (var trx = await connection.BeginTransactionAsync())
                    {
                        using (var cmd = connection.CreateCommand())
                        {
                            if (request.IgnorePayments)
                            {
                                cmd.CommandText = SELECT_TRX_NO_PAYMENTS;
                            }
                            else
                            {
                                cmd.CommandText = SELECT_TRX_PAYMENTS;
                            }
                            cmd.AddParam("take", request.Take);
                            cmd.AddParam("skip_trx", request.Skip);
                            cmd.Transaction = trx;
                            using (var dr = await cmd.ExecuteReaderAsync())
                            {
                                while (await dr.ReadAsync())
                                {
                                    results.Add(new TransactionRecord()
                                    {
                                        TransactionDate = dr.GetDateTime(1),
                                        PostDate        = dr.GetDateTime(2),
                                        ReferenceId     = dr.GetString(3),
                                        Description     = dr.GetString(4),
                                        Amount          = dr.GetDecimal(5),
                                        AccountNumber   = dr.GetString(6),
                                        CardNumber      = dr.GetString(7),
                                        CardholderName  = dr.GetString(8),
                                        Mcc             = dr.GetString(9),
                                        MccDescription  = dr.GetString(10),
                                        MccGroup        = dr.GetString(11)
                                    });
                                }
                            }
                        }
                    }
                }
                finally
                {
                    await connection.CloseAsync();
                }
                return(results);
            }
        }
示例#18
0
        private async ValueTask ExecuteWithTransactionAsync(string statement, object param)
        {
            if (_connection.State is ConnectionState.Closed)
            {
                await _connection.OpenAsync();
            }
            await using NpgsqlTransaction transaction = await _connection.BeginTransactionAsync(IsolationLevel.ReadCommitted);

            await _connection.ExecuteAsync(statement, param, transaction);

            await transaction.CommitAsync();
        }
        private async Task CopyDataFromBackuptoOriginalTable(NpgsqlConnection conn)
        {
            using var tran = await conn.BeginTransactionAsync();

            using var truncateTableCommand = new NpgsqlCommand("TRUNCATE TABLE \"QualifiedTeachers\";", conn);
            await truncateTableCommand.ExecuteNonQueryAsync();

            using var revertBackupCommand = new NpgsqlCommand("INSERT INTO \"QualifiedTeachers\" ( \"Id\", \"Trn\", \"Name\", \"DoB\", \"NINumber\", \"QTSAwardDate\", \"ITTSubject1Code\", \"ITTSubject2Code\", \"ITTSubject3Code\", \"ActiveAlert\", \"ITTStartDate\", \"QualificationName\") SELECT * from \"QualifiedTeachersBackup\"", conn);
            await revertBackupCommand.ExecuteNonQueryAsync();

            await tran.CommitAsync();
        }
示例#20
0
        public async Task <bool> BloquearUsuarios(List <Usuario> usuarios)
        {
            if (!usuarios.Any())
            {
                return(false);
            }

            _db.Open();

            using (var transaccion = await _db.BeginTransactionAsync())
            {
                try
                {
                    foreach (var item in usuarios)
                    {
                        string query = "UPDATE Usuario SET EstaBloqueado = @Bloqueado WHERE Id = @ID";

                        var parametros = new DynamicParameters();
                        parametros.Add("@Bloqueado", item.EstaBloqueado ? false : true);
                        parametros.Add("@ID", item.Id);

                        await _db.ExecuteAsync(query, param : parametros, transaction : transaccion,
                                               commandType : System.Data.CommandType.Text);
                    }

                    await transaccion.CommitAsync();

                    _db.Close();

                    return(true);
                }
                catch (Exception e)
                {
                    await transaccion.RollbackAsync();

                    _db.Close();
                    throw new Exception($"{e.Message}");
                }
            }
        }
示例#21
0
        public async System.Threading.Tasks.Task for_transaction()
        {
            using var conn = new NpgsqlConnection(ConnectionSource.ConnectionString);
            await conn.OpenAsync().ConfigureAwait(false);

            using var tx = await conn.BeginTransactionAsync();

            var options = SessionOptions.ForTransaction(tx);

            options.Transaction.ShouldBe(tx);
            options.Connection.ShouldBe(conn);
            options.OwnsConnection.ShouldBeFalse();
            options.OwnsTransactionLifecycle.ShouldBeFalse();
        }
示例#22
0
        public async Task <bool> ActivarDesactivarCuenta(List <ClienteDto> clientes)
        {
            using (var _db = new NpgsqlConnection("Server=localhost; User Id=postgres;Password=panchito; DataBase=SistemaVentas"))
            {
                _db.Open();

                using (var t = await _db.BeginTransactionAsync())
                {
                    try
                    {
                        foreach (var item in clientes)
                        {
                            string query     = "SELECT * FROM Persona WHERE Id = @ID AND Discriminador = @Discriminador";
                            var    parametro = new DynamicParameters();

                            parametro.Add("@ID", item.Id);
                            parametro.Add("@Discriminador", "Cliente");

                            var cliente = _db.QueryFirstOrDefault <ClienteDto>(query, param: parametro,
                                                                               transaction: t, commandType: System.Data.CommandType.Text);

                            string actualizarEstado = "UPDATE Persona SET ActivarCtaCte = @NuevoEstado WHERE Id = @ID " +
                                                      "AND Discriminador = @Discriminador";
                            var parametroActualizar = new DynamicParameters();

                            parametroActualizar.Add("@NuevoEstado", cliente.ActivarCtaCte ? false : true);
                            parametroActualizar.Add("@ID", cliente.Id);
                            parametroActualizar.Add("@Discriminador", "Cliente");

                            _db.Execute(actualizarEstado, param: parametroActualizar,
                                        transaction: t, commandType: System.Data.CommandType.Text);
                        }

                        await t.CommitAsync();

                        _db.Close();

                        return(true);
                    }
                    catch (Exception e)
                    {
                        await t.RollbackAsync();

                        _db.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        public async Task TestConcurrentSelectWithTransaction()
        {
            await _connection.ExecuteAsync(
                "INSERT INTO public.test (id, name) values (1, 'test1');INSERT INTO public.test (id, name) values (2, 'test2');",
                _cancel.Token);

            await using var transaction =
                            await _connection.BeginTransactionAsync(IsolationLevel.RepeatableRead, _cancel.Token);

            await using var transaction2 =
                            await _connection2.BeginTransactionAsync(IsolationLevel.RepeatableRead, _cancel.Token);


            await Task.WhenAll(
                _connection.ExecuteAsync(new CommandDefinition(
                                             "select name from public.test where id = 1;select pg_sleep(0.2);select name from public.test where id = 2;",
                                             transaction,
                                             cancellationToken: _cancel.Token)),
                _connection2.ExecuteAsync(new CommandDefinition(
                                              "select name from public.test where id = 2;select pg_sleep(0.2);select name from public.test where id = 1;",
                                              transaction2,
                                              cancellationToken: _cancel.Token)))
            .CheckAnswerConcurrentSelectWithTransaction();
        }
示例#24
0
        //TODO: Изучить транзакции, возможно запись в Setting делать тут же, в пределах транзакции
        public async Task <Result> ExecuteMigration(string commandText)
        {
            Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

            string[] subCommands = regex.Split(commandText);

            await using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            var transaction = await connection.BeginTransactionAsync();

            await using (var cmd = connection.CreateCommand())
            {
                cmd.Connection  = connection;
                cmd.Transaction = transaction;

                foreach (var command in subCommands)
                {
                    if (command.Length <= 0)
                    {
                        continue;
                    }

                    cmd.CommandText = command;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException)
                    {
                        await transaction.RollbackAsync();

                        throw;
                    }
                }
            }
            try
            {
                await transaction.CommitAsync();

                return(Result.Success());
            }
            catch (Exception e)
            {
                return(Result.Failure($"Commit transaction Exception: '{e}'"));
            }
        }
        public async Task <Guid> RegisterOrganizationAsync(Organization organization)
        {
            await _connection.OpenAsync();

            await using var transaction = await _connection.BeginTransactionAsync();

            await _connection.ExecuteAsync(@"INSERT INTO ""Contacts"" 
(""Guid"", ""Email"", ""PhoneNumber"", ""Site"", ""Vk"", ""Telegram"", ""Instagram"")
VALUES (@Guid, @Email, @PhoneNumber, @Site, @Vk, @Telegram, @Instagram)", organization.Contacts);

            if (organization.Logo != null)
            {
                await _connection.ExecuteAsync(@"INSERT INTO ""OrganizationLogo""
(""Guid"", ""Content"") VALUES (@Guid, @Content)", organization.Logo);
            }
            await _connection.ExecuteAsync(@"INSERT INTO ""Organization""
(""Guid"", ""OrganizationTypeId"", ""ContactsGuid"", ""LegalName"", ""LegalAddress"",
    ""ActualName"", ""TIN"", ""OrganizationLogoGuid"") 
VALUES (@Guid, @OrganizationTypeId, @ContactsGuid, @LegalName, @LegalAddress, @ActualName,
  @TIN, @OrganizationLogoGuid)", new
            {
                organization.Guid,
                OrganizationTypeId = organization.Type.Id,
                ContactsGuid       = organization.Contacts.Guid,
                organization.LegalName,
                organization.LegalAddress,
                organization.ActualName,
                organization.TIN,
                OrganizationLogoGuid = organization.Logo?.Guid
            });

            foreach (var image in organization.Images)
            {
                await _connection.ExecuteAsync(@"INSERT INTO ""OrganizationImage""
(""Guid"", ""Content"") VALUES (@Guid, @Content)", image);

                await _connection.ExecuteAsync(@"INSERT INTO ""JOrganizationImage""
(""OrganizationGuid"", ""OrganizationImageGuid"") VALUES (@Guid, ImageGuid)",
                                               new { organization.Guid, ImageGuid = image.Guid });
            }

            await transaction.CommitAsync();

            await _connection.CloseAsync();

            return(organization.Guid);
        }
示例#26
0
        public async Task <IEnumerable <TransactionRecord> > GetAllExpensesAsync()
        {
            using (var connection = new NpgsqlConnection(_connectionString))
            {
                await connection.OpenAsync();

                List <TransactionRecord> results = new List <TransactionRecord>();
                try
                {
                    using (var trx = await connection.BeginTransactionAsync())
                    {
                        using (var cmd = connection.CreateCommand())
                        {
                            cmd.CommandText = SELECT_ALL_EXPENSES;
                            using (var dr = await cmd.ExecuteReaderAsync())
                            {
                                while (await dr.ReadAsync())
                                {
                                    results.Add(new TransactionRecord()
                                    {
                                        TransactionDate = dr.GetDateTime(1),
                                        PostDate        = dr.GetDateTime(2),
                                        ReferenceId     = dr.GetString(3),
                                        Description     = dr.GetString(4),
                                        Amount          = dr.GetDecimal(5),
                                        AccountNumber   = dr.GetString(6),
                                        CardNumber      = dr.GetString(7),
                                        CardholderName  = dr.GetString(8),
                                        Mcc             = dr.GetString(9),
                                        MccDescription  = dr.GetString(10),
                                        MccGroup        = dr.GetString(11)
                                    });
                                }
                            }
                        }
                    }
                    return(results);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
        }
示例#27
0
        public async Task <bool> ActivarDesactivarCuenta(List <Cliente> clientes)
        {
            _db.Open();

            using (var t = await _db.BeginTransactionAsync())
            {
                try
                {
                    foreach (var item in clientes)
                    {
                        string query     = "SELECT * FROM Persona WHERE Id = @ID";
                        var    parametro = new DynamicParameters();

                        parametro.Add("@ID", item.Id);

                        var cliente = _db.QueryFirstOrDefault <Cliente>(query, param: parametro,
                                                                        transaction: t, commandType: System.Data.CommandType.Text);

                        string actualizarEstado    = "UPDATE Cliente SET ActivarCtaCte = @NuevoEstado WHERE Id = @ID";
                        var    parametroActualizar = new DynamicParameters();

                        parametroActualizar.Add("@NuevoEstado", cliente.ActivarCtaCte ? false : true);
                        parametroActualizar.Add("@ID", cliente.Id);

                        _db.Execute(actualizarEstado, param: parametroActualizar,
                                    transaction: t, commandType: System.Data.CommandType.Text);
                    }

                    await t.CommitAsync();

                    _db.Close();

                    return(true);
                }
                catch (Exception e)
                {
                    await t.RollbackAsync();

                    _db.Close();
                    throw new Exception(e.Message);
                }
            }
        }
示例#28
0
        public async Task SaveLogsAsync(string logId, IAsyncEnumerable <LogEntry> entries)
        {
            Contract.Requires(entries != null);
            Console.WriteLine("Start of saving entries");
            var transaction = await connection.BeginTransactionAsync().ConfigureAwait(false);

            using var command = new NpgsqlCommand("INSERT INTO \"LogEntries\"" +
                                                  "(\"Id\", \"LogId\", \"ClientIp\", \"RequestTime\", \"Method\"," +
                                                  " \"Resource\", \"ResponseCode\", \"UserAgent\", \"RawEntry\")" +
                                                  " VALUES (@Id, @LogId, @ClientIp, @RequestTime," +
                                                  " @Method, @Resource, @ResponseCode, @UserAgent, @RawEntry);", connection);
            command.Parameters.Add("@Id", NpgsqlTypes.NpgsqlDbType.Text);
            command.Parameters.Add("@LogId", NpgsqlTypes.NpgsqlDbType.Text);
            command.Parameters.Add("@ClientIp", NpgsqlTypes.NpgsqlDbType.Text);
            command.Parameters.Add("@RequestTime", NpgsqlTypes.NpgsqlDbType.Timestamp);
            command.Parameters.Add("@Method", NpgsqlTypes.NpgsqlDbType.Integer);
            command.Parameters.Add("@Resource", NpgsqlTypes.NpgsqlDbType.Text);
            command.Parameters.Add("@ResponseCode", NpgsqlTypes.NpgsqlDbType.Integer);
            command.Parameters.Add("@UserAgent", NpgsqlTypes.NpgsqlDbType.Text);
            command.Parameters.Add("@RawEntry", NpgsqlTypes.NpgsqlDbType.Text);
            await foreach (var entry in entries)
            {
                if (entry == null)
                {
                    Console.WriteLine("Invalid entry"); continue;
                }
                command.Parameters["@Id"].NpgsqlValue           = Guid.NewGuid().ToString();
                command.Parameters["@LogId"].NpgsqlValue        = logId;
                command.Parameters["@ClientIp"].NpgsqlValue     = entry.ClientIp;
                command.Parameters["@RequestTime"].NpgsqlValue  = entry.RequestTime;
                command.Parameters["@Method"].NpgsqlValue       = (int)entry.Method;
                command.Parameters["@Resource"].NpgsqlValue     = entry.Resource;
                command.Parameters["@ResponseCode"].NpgsqlValue = entry.ResponseCode;
                command.Parameters["@UserAgent"].NpgsqlValue    = (object)entry.UserAgent ?? DBNull.Value;
                command.Parameters["@RawEntry"].NpgsqlValue     = entry.RawEntry;
                await command.PrepareAsync().ConfigureAwait(false);

                await command.ExecuteNonQueryAsync().ConfigureAwait(false);
            }
            await transaction.CommitAsync().ConfigureAwait(false);

            Console.WriteLine("End of saving entries");
        }
示例#29
0
        public async Task <OperationStatus <Guid> > SignUp(RegistrationData registrationData)
        {
            // TODO
            var profile = new Profile {
                Surname = registrationData.Surname, FirstName = registrationData.FirstName, SecondName = registrationData.SecondName, Group = registrationData.Group
            };
            var authData = new AuthData {
                Email = registrationData.Email, Password = registrationData.Password
            };

            try
            {
                profile.InitAsFresh();

                await using var conn = new NpgsqlConnection(ConnectionString);
                await conn.OpenAsync().ConfigureAwait(false);

                await using var transaction = await conn.BeginTransactionAsync().ConfigureAwait(false);

                await conn.ExecuteAsync(
                    @"insert into auth_data (email, password_hash, user_id)
                          values (@Email, digest(@Password, 'sha256'), @UserId)",
                    new { authData.Email, authData.Password, UserId = profile.Id }).ConfigureAwait(false);

                await conn.ExecuteAsync(
                    @"insert into profile (id, deleted, version, data)
                          values (@Id, false, 0, @Profile::jsonb)", new { profile.Id, profile }).ConfigureAwait(false);

                await transaction.CommitAsync().ConfigureAwait(false);

                return(OperationStatus <Guid> .Success(profile.Id));
            }
            catch (PostgresException e) when(e.SqlState == "23505")
            {
                return(OperationStatus <Guid> .Fail(OperationStatusCode.Conflict, "User with such email already exists"));
            }
            catch (Exception e)
            {
                Console.WriteLine(e);  // TODO logging
                return(OperationStatus <Guid> .Fail(OperationStatusCode.InternalServerError));
            }
        }
        public async Task UpdateAsync(string tableName, IEnumerable <EventEntity> eventEntities)
        {
            await npgsqlConnection.OpenAsync();

            using (var transaction = await npgsqlConnection.BeginTransactionAsync())
            {
                var tempTableName = GetUniqueName("_temp_");

                // 1. Create temp table
                var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS SELECT \"Retries\" , \"ExpiresAt\" , \"StatusName\" , \"Id\" FROM {tableName} LIMIT 0";
                await npgsqlConnection.ExecuteAsync(sql);

                // 2. Import into temp table
                using (var importer = npgsqlConnection.BeginBinaryImport($"COPY {tempTableName} (\"Retries\" , \"ExpiresAt\" , \"StatusName\" , \"Id\") FROM STDIN (FORMAT BINARY)"))
                {
                    foreach (var item in eventEntities)
                    {
                        importer.StartRow();
                        importer.Write(item.Retries);
                        if (item.ExpiresAt.HasValue)
                        {
                            importer.Write(item.ExpiresAt.Value, NpgsqlDbType.Timestamp);
                        }
                        else
                        {
                            importer.Write(DBNull.Value);
                        }

                        importer.Write(item.StatusName, NpgsqlDbType.Varchar);
                        importer.Write(item.Id, NpgsqlDbType.Varchar);
                    }
                    importer.Complete();
                }

                // 3. Insert into real table from temp one
                sql = $"UPDATE {tableName} target  SET \"Retries\" = \"source\".\"Retries\" , \"ExpiresAt\" = \"source\".\"ExpiresAt\" , \"StatusName\" = \"source\".\"StatusName\"  FROM {tempTableName} as source WHERE \"target\".\"Id\" = \"source\".\"Id\"";
                await npgsqlConnection.ExecuteAsync(sql);

                // 5. Commit
                transaction?.Commit();
            }
        }