public async Task CreateMovies(IEnumerable <Movie> movies)
{
var dbConnection = CreateDbConnection();
DbTransaction dbTransaction = null;
DbCommand dbCommand = null;
try
{
await dbConnection.OpenAsync().ConfigureAwait(false);
dbTransaction = dbConnection.BeginTransaction();
dbCommand = CommandFactoryMovies.CreateCommandForCreateMoviesTvpDistinctInsertInto(dbConnection, dbTransaction, movies);
await dbCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
dbTransaction.Commit();
}
catch (DbException)
{
dbTransaction.RollbackIfNotNull();
throw;
}
finally
{
dbCommand.DisposeIfNotNull();
dbTransaction.DisposeIfNotNull();
dbConnection.DisposeIfNotNull();
}
}
public async IAsyncEnumerable <Movie> GetAllMoviesStream()
{
var dbConnection = CreateDbConnection();
DbCommand dbCommand = null;
DbDataReader dbDataReader = null;
try
{
await dbConnection.OpenAsync().ConfigureAwait(false);
dbCommand = CommandFactoryMovies.CreateCommandForGetAllMovies(dbConnection);
dbDataReader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false);
await foreach (var movie in MapToMoviesStreaming(dbDataReader).ConfigureAwait(false))
{
yield return(movie);
await Task.Delay(1000);
}
}
finally
{
dbDataReader.DisposeIfNotNull();
dbCommand.DisposeIfNotNull();
dbConnection.Dispose();
}
}
public async Task <Movie> GetMovieById(int id)
{
var dbConnection = CreateDbConnection();
DbCommand dbCommand = null;
DbDataReader dbDataReader = null;
try
{
await dbConnection.OpenAsync().ConfigureAwait(false);
dbCommand = CommandFactoryMovies.CreateCommandForGetMovieById(dbConnection, id);
dbDataReader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false);
return(await MapToMovie(dbDataReader).ConfigureAwait(false));
}
finally
{
dbDataReader.DisposeIfNotNull();
dbCommand.DisposeIfNotNull();
dbConnection.Dispose();
}
}
public async Task <int> CreateMovie(Movie movie)
{
var dbConnection = CreateDbConnection();
DbCommand dbCommand = null;
DbTransaction dbTransaction = null;
try
{
await dbConnection.OpenAsync().ConfigureAwait(false);
dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable);
dbCommand = CommandFactoryMovies.CreateCommandForCreateMovie(dbConnection, dbTransaction, movie);
await dbCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
dbTransaction.Commit();
return((int)dbCommand.Parameters[0].Value);
}
catch (DbException e)
{
dbTransaction.RollbackIfNotNull();
if (e.Message.Contains("duplicate key row in object 'dbo.Movie'", StringComparison.OrdinalIgnoreCase))
{
throw new DuplicateMovieException($"A Movie with the Title: {movie.Title} already exists. Please use a different title", e);
}
else
{
throw;
}
}
finally
{
dbCommand.DisposeIfNotNull();
dbTransaction.DisposeIfNotNull();
dbConnection.Dispose();
}
}
