protected static ConnectionHolder CreateConnection(string connectionString)
{
var connection = new NpgsqlConnection(connectionString);
connection.Open();
if (Transaction.Current == null)
{
var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
return(ConnectionHolder.ForTransactionalWork(connection, transaction));
}
return(ConnectionHolder.ForNonTransactionalWork(connection));
}
void CreateIndex(ISagaData sagaData, ConnectionHolder 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 = string.Format("@n{0}", i),
PropertyValueParameter = string.Format("@v{0}", 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 => string.Format(
@" insert into ""{0}""
(""saga_type"", ""key"", ""value"", ""saga_id"")
values
(@saga_type, {1}, {2}, @saga_id)",
sagaIndexTableName, a.PropertyNameParameter, a.PropertyValueParameter));
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.Parameters.AddWithValue(parameter.PropertyNameParameter, NpgsqlDbType.Text, parameter.PropertyName);
command.Parameters.AddWithValue(parameter.PropertyValueParameter, NpgsqlDbType.Text, parameter.PropertyValue);
}
command.Parameters.AddWithValue("saga_type", NpgsqlDbType.Text, sagaTypeName);
command.Parameters.AddWithValue("saga_id", NpgsqlDbType.Uuid, sagaData.Id);
command.ExecuteNonQuery();
}
}