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