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