public async Task CreateIfNecessary(CancellationToken cancellationToken = default)
{
using (var connection = await connectionFactory.OpenNewConnection(cancellationToken).ConfigureAwait(false))
{
try
{
using (var transaction = connection.BeginTransaction())
{
var sql = string.Format(SqlConstants.CreateSubscriptionTableText, tableName.QuotedQualifiedName, tableName.QuotedCatalog);
using (var command = new SqlCommand(sql, connection, transaction)
{
CommandType = CommandType.Text
})
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
transaction.Commit();
}
}
catch (SqlException e) when(e.Number == 2714 || e.Number == 1913) //Object already exists
{
//Table creation scripts are based on sys.objects metadata views.
//It looks that these views are not fully transactional and might
//not return information on already created table under heavy load.
//This in turn can result in executing table create or index create queries
//for objects that already exists. These queries will fail with
// 2714 (table) and 1913 (index) error codes.
}
}
}
public async Task Subscribe(string endpointName, string queueAddress, string topic)
{
using (new TransactionScope(TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = await connectionFactory.OpenNewConnection().ConfigureAwait(false))
using (var command = connection.CreateCommand())
{
command.CommandText = subscribeCommand;
command.Parameters.Add("Endpoint", SqlDbType.VarChar).Value = endpointName;
command.Parameters.Add("QueueAddress", SqlDbType.VarChar).Value = queueAddress;
command.Parameters.Add("Topic", SqlDbType.VarChar).Value = topic;
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
public async Task CreateIfNecessary()
{
using (var connection = await connectionFactory.OpenNewConnection().ConfigureAwait(false))
using (var transaction = connection.BeginTransaction())
{
#pragma warning disable 618
var sql = string.Format(SqlConstants.CreateSubscriptionTableText, tableName.QuotedQualifiedName, tableName.QuotedCatalog);
#pragma warning restore 618
using (var command = new SqlCommand(sql, connection, transaction)
{
CommandType = CommandType.Text
})
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
transaction.Commit();
}
}