public async Task<long> GetRandomWodIdAsync()
{
using (var conn = new SqlConnection(AppConfig.ConnectionString))
{
return await conn.ExecuteScalarAsync<long>("SELECT TOP 1 [Id] FROM [Wod] ORDER BY NEWID()");
}
}
public async Task<long> CreateAlbum(NewAlbum album)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertAlbum, new { artistId = album.ArtistId, albumArtId = album.AlbumArtId, name = album.Name, releaseDate = album.ReleaseDate });
await connection.OpenAsync();
var albumArtId = await connection.ExecuteScalarAsync<long>(cmd);
return albumArtId;
}
}
public static async Task <T> ExecuteScalarWithRetryAsync <T>(
this SqlConnection connection,
string sql,
object param = null,
IDbTransaction transaction = null,
TimeSpan?commandTimeout = null,
CommandType?commandType = null,
int maxRetries = 10,
Action onRetry = null)
{
for (int attempt = 0; attempt < maxRetries; attempt++)
{
try
{
return(await connection.ExecuteScalarAsync <T>(sql, param, transaction, (int?)commandTimeout?.TotalSeconds, commandType));
}
catch (SqlException ex)
{
switch (ex.Number)
{
case -2: // Client Timeout
case 701: // Out of Memory
case 1204: // Lock Issue
case 1205: // >>> Deadlock Victim
case 1222: // Lock Request Timeout
case 8645: // Timeout waiting for memory resource
case 8651: // Low memory condition
// Ignore
if (attempt < (maxRetries - 1))
{
if (onRetry != null)
{
onRetry();
}
}
else
{
throw;
}
break;
default:
throw;
}
}
}
throw new Exception("Unknown error! Should have thrown the final timeout!");
}
/// <summary>
/// Insert a new artist in the database, or return its ID if it already exists.
/// </summary>
/// <param name="artist">The artist to insert.</param>
/// <returns>The ID of the new artist inserted, or the existing ID if it is a duplicate.</returns>
public async Task<long> CreateArtist(NewArtist artist)
{
//var existingArtist = await GetArtistByName(artist.Name);
//if (existingArtist != null)
//{
// return existingArtist.Id;
//}
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertArtist, new { name = artist.Name, country = artist.Country });
await connection.OpenAsync();
var artistId = await connection.ExecuteScalarAsync<long>(cmd);
return artistId;
}
}
public async Task<long> CreateAlbumTrack(NewAlbumTrack albumTrack)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertAlbumTrack, new
{
albumId = albumTrack.AlbumId,
trackNumber = albumTrack.TrackNumber,
name = albumTrack.Name,
durationMs = albumTrack.LengthInMs
});
await connection.OpenAsync();
var trackId = await connection.ExecuteScalarAsync<long>(cmd);
return trackId;
}
}
public static Task <T> ExecuteScalarWithRetryAsync <T>(
this SqlConnection connection,
string sql,
object param = null,
IDbTransaction transaction = null,
TimeSpan?commandTimeout = null,
CommandType?commandType = null,
int maxRetries = SqlRetryUtility.DefaultMaxRetries)
{
return(SqlRetryUtility.RetryReadOnlySql(
() => connection.ExecuteScalarAsync <T>(
sql,
param,
transaction,
(int?)commandTimeout?.TotalSeconds,
commandType),
maxRetries));
}
public async Task AppendStream(string streamId, int expectedVersion, IEnumerable<StreamEvent> streamEvents)
{
Guard.NullOrWhiteSpace(() => streamId);
Guard.NullOrDefault(() => streamEvents);
await _log.Debug("Appending stream {@streamId} with {@events}", streamId, streamEvents.ToArray());
// create DataTable to send as a TVP
var newStreamEventsTable = new DataTable();
newStreamEventsTable.Columns.Add("Id", typeof(Guid));
newStreamEventsTable.Columns.Add("Type", typeof(string));
newStreamEventsTable.Columns.Add("Data", typeof(string));
newStreamEventsTable.Columns.Add("Metadata", typeof(string));
newStreamEventsTable.Columns.Add("StreamVersion", typeof(int));
var eventVersion = expectedVersion;
var fallbackCommitId = Guid.NewGuid();
newStreamEventsTable.BeginLoadData();
foreach(var se in streamEvents) {
se.EnrichMetadata(streamId, eventVersion++, fallbackCommitId);
newStreamEventsTable.Rows.Add(
se.Metadata[EventMetadataKeys.Id],
se.Event.GetType().Name,
_serializer.Serialize(se.Event),
_serializer.Serialize(se.Metadata),
eventVersion);
}
newStreamEventsTable.EndLoadData();
// create parameters
var parameters = new DynamicParameters();
parameters.AddDynamicParams(new {
StreamId = streamId,
ExpectedStreamVersion = expectedVersion,
StreamEvents = newStreamEventsTable.AsTableValuedParameter("StreamEvents")
});
int actualVersion;
// execute operation
using(var connection = new SqlConnection(_settings.ConnectionString)) {
actualVersion = await connection
.ExecuteScalarAsync<int>(
sql : "AppendStream",
param : parameters,
commandType: CommandType.StoredProcedure)
.ConfigureAwait(false);
}
// if the actual version is different from the expected version
if(actualVersion != eventVersion) {
throw new StreamConcurrencyException(streamId, expectedVersion, actualVersion);
}
await _log.Information("Events appended to stream {@streamId}", streamId);
// dispatch events
await _dispatcher.DispatchStreamEvents(streamEvents);
}
public async Task<CreateUserResult> CreateUserAsync(ApplicationUser user)
{
int? id = 0;
string error = null;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
int existingId = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Users WHERE Email=@Email", new {Email = user.Email});
if (existingId == 0)
{
id = await connection.InsertAsync(user);
}
else
{
error = "Email address already exists.";
}
}
if (id > 0)
{
return new CreateUserResult {IsSuccess = true};
}
return new CreateUserResult {IsSuccess = false, ErrorMessage = error ?? "Error inserting user."};
}
