public async Task Save(ISagaData sagaData, Dictionary <string, string> sagaAuditMetadata) { using (var connection = await _connectionHelper.GetConnection()) { using (var command = connection.CreateCommand()) { command.CommandText = $@" INSERT INTO ""{_tableName}"" (""id"", ""revision"", ""data"", ""metadata"") VALUES (@id, @revision, @data, @metadata); "; command.Parameters.Add("id", NpgsqlDbType.Uuid).Value = sagaData.Id; command.Parameters.Add("revision", NpgsqlDbType.Integer).Value = sagaData.Revision; command.Parameters.Add("data", NpgsqlDbType.Bytea).Value = _objectSerializer.Serialize(sagaData); command.Parameters.Add("metadata", NpgsqlDbType.Jsonb).Value = _dictionarySerializer.SerializeToString(sagaAuditMetadata); await command.ExecuteNonQueryAsync(); } connection.Complete(); } }
/// <summary> /// Creates the subscriptions table if no table with the specified name exists /// </summary> public void EnsureTableIsCreated() { using (var connection = _connectionHelper.GetConnection().Result) { var tableNames = connection.GetTableNames().ToHashSet(); if (tableNames.Contains(_tableName)) { return; } _log.Info("Table {tableName} does not exist - it will be created now", _tableName); using (var command = connection.CreateCommand()) { command.CommandText = $@" CREATE TABLE ""{_tableName }"" ( ""topic"" VARCHAR(200) NOT NULL, ""address"" VARCHAR(200) NOT NULL, PRIMARY KEY (""topic"", ""address"") ); "; command.ExecuteNonQuery(); } Task.Run(async() => await connection.Complete()).Wait(); } }
public static void DropTable(string tableName) { using (var connection = PostgresConnectionHelper.GetConnection().Result) { using (var comand = connection.CreateCommand()) { comand.CommandText = string.Format(@"drop table ""{0}"";", tableName); try { comand.ExecuteNonQuery(); Console.WriteLine("Dropped postgres table '{0}'", tableName); } catch (NpgsqlException exception) { if (exception.Code != TableDoesNotExist) { throw; } } } connection.Complete(); } }
public async Task Defer(DateTimeOffset approximateDueTime, Dictionary <string, string> headers, byte[] body) { using (var connection = await _connectionHelper.GetConnection()) { using (var command = connection.CreateCommand()) { command.CommandText = string.Format(@" INSERT INTO ""{0}"" (""due_time"", ""headers"", ""body"") VALUES (@due_time, @headers, @body)", _tableName); command.Parameters.Add("due_time", NpgsqlDbType.Timestamp).Value = approximateDueTime.ToUniversalTime().DateTime; command.Parameters.Add("headers", NpgsqlDbType.Text).Value = _dictionarySerializer.SerializeToString(headers); command.Parameters.Add("body", NpgsqlDbType.Bytea).Value = body; await command.ExecuteNonQueryAsync(); } connection.Complete(); } }
async Task PerformExpiredMessagesCleanupCycle() { var results = 0; var stopwatch = Stopwatch.StartNew(); while (true) { using (var connection = await _connectionHelper.GetConnection()) { int affectedRows; using (var command = connection.CreateCommand()) { command.CommandText = $@" delete from {_tableName} where recipient = @recipient and expiration < clock_timestamp() "; command.Parameters.Add("recipient", NpgsqlDbType.Text).Value = _inputQueueName; affectedRows = await command.ExecuteNonQueryAsync(); } results += affectedRows; await connection.Complete(); if (affectedRows == 0) { break; } } } if (results > 0) { _log.Info( "Performed expired messages cleanup in {0} - {1} expired messages with recipient {2} were deleted", stopwatch.Elapsed, results, _inputQueueName); } }
public Task <PostgresConnection> GetConnection() { Interlocked.Increment(ref _connections); return(_innerPostgresConnectionHelper.GetConnection()); }