protected virtual Task<IEnumerable<SQLSubscription>> SelectSubscriptions()
{
var subscriptions = new List<SQLSubscription>();
var connection = _connectionProvider.GetConnection();
try
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = _dialect.SelectSubscriptionsCommand;
command.SetParameter(_dialect.CurrentDateParameterName, DateTime.UtcNow);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var topicName = (TopicName) reader.GetString("TopicName");
var subscriber = new Uri(reader.GetString("Subscriber"));
var expires = reader.GetDateTime("Expires").GetValueOrDefault(DateTime.MaxValue);
var subscription = new SQLSubscription(topicName, subscriber, expires);
subscriptions.Add(subscription);
}
}
}
scope.Complete();
}
}
finally
{
_connectionProvider.ReleaseConnection(connection);
}
return Task.FromResult<IEnumerable<SQLSubscription>>(subscriptions);
}
protected virtual Task<SQLSubscription> InsertOrUpdateSubscription(TopicName topicName, Uri subscriber,
DateTime expires)
{
SQLSubscription subscription;
var connection = _connectionProvider.GetConnection();
try
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = _dialect.InsertSubscriptionCommand;
command.SetParameter(_dialect.TopicNameParameterName, (string) topicName);
command.SetParameter(_dialect.SubscriberParameterName, subscriber.ToString());
command.SetParameter(_dialect.ExpiresParameterName, expires);
var rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
command.CommandText = _dialect.UpdateSubscriptionCommand;
command.ExecuteNonQuery();
}
}
subscription = new SQLSubscription(topicName, subscriber, expires);
scope.Complete();
}
}
finally
{
_connectionProvider.ReleaseConnection(connection);
}
return Task.FromResult(subscription);
}
