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