static void ExecuteCommands(PostgresConnection connection, string sqlCommands)
{
foreach (var sqlCommand in sqlCommands.Split(new[] { "----" }, StringSplitOptions.RemoveEmptyEntries))
{
using (var command = connection.CreateCommand())
{
command.CommandText = sqlCommand;
Execute(command);
}
}
}
async Task CreateIndex(ISagaData sagaData, PostgresConnection connection, IEnumerable <KeyValuePair <string, string> > propertiesToIndex)
{
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value ?? "",
PropertyNameParameter = $"@n{i}",
PropertyValueParameter = $"@v{i}"
})
.ToList();
// lastly, generate new index
using (var command = connection.CreateCommand())
{
// generate batch insert with SQL for each entry in the index
var inserts = parameters
.Select(a =>
$@"
INSERT
INTO ""{_indexTableName}"" (""saga_type"", ""key"", ""value"", ""saga_id"")
VALUES (@saga_type, {a.PropertyNameParameter}, {a.PropertyValueParameter}, @saga_id)
");
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter.PropertyNameParameter, NpgsqlDbType.Text).Value = parameter.PropertyName;
command.Parameters.Add(parameter.PropertyValueParameter, NpgsqlDbType.Text).Value = parameter.PropertyValue;
}
command.Parameters.Add("saga_type", NpgsqlDbType.Text).Value = sagaTypeName;
command.Parameters.Add("saga_id", NpgsqlDbType.Uuid).Value = sagaData.Id;
await command.ExecuteNonQueryAsync();
}
}
async Task CreateIndex(ISagaData sagaData, PostgresConnection connection, IEnumerable<KeyValuePair<string, string>> propertiesToIndex)
{
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value ?? "",
PropertyNameParameter = $"@n{i}",
PropertyValueParameter = $"@v{i}"
})
.ToList();
// lastly, generate new index
using (var command = connection.CreateCommand())
{
// generate batch insert with SQL for each entry in the index
var inserts = parameters
.Select(a =>
$@"
INSERT
INTO ""{_indexTableName}"" (""saga_type"", ""key"", ""value"", ""saga_id"")
VALUES (@saga_type, {a.PropertyNameParameter}, {a.PropertyValueParameter}, @saga_id)
");
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter.PropertyNameParameter, NpgsqlDbType.Text).Value = parameter.PropertyName;
command.Parameters.Add(parameter.PropertyValueParameter, NpgsqlDbType.Text).Value = parameter.PropertyValue;
}
command.Parameters.Add("saga_type", NpgsqlDbType.Text).Value = sagaTypeName;
command.Parameters.Add("saga_id", NpgsqlDbType.Uuid).Value = sagaData.Id;
await command.ExecuteNonQueryAsync();
}
}