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