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."}; }