public void CreateMoviesWithoutTvp(IEnumerable <ImdbMovie> imdbMovies)
{
DbConnection dbConnection = null;
DbTransaction dbTransaction = null;
DbCommand dbCommand = null;
try
{
dbConnection = CreateDbConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
foreach (var imdbMovie in imdbMovies)
{
dbCommand = CommandFactoryMovies.CreateCommandForCreateMovie(dbConnection, dbTransaction, imdbMovie);
dbCommand.ExecuteNonQuery();
}
dbTransaction.Commit();
}
catch (DbException)
{
dbTransaction.RollbackIfNotNull();
throw;
}
finally
{
dbCommand.DisposeIfNotNull();
dbTransaction.DisposeIfNotNull();
dbConnection.CloseAndDispose();
}
}
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();
}
}
protected static TResult ExecuteReaderUsingTransactionAndAdaptToModelWithPossibleNull <TCommandParameters, TResult>(DbConnection dbConnection, CommandFactory <TCommandParameters> commandFactory, TCommandParameters commandParameters, ModelAdapter <TResult> modelAdapter)
{
dbConnection.Open();
DbTransaction transaction = null;
DbCommand dbCommand = null;
DbDataReader dbDataReader = null;
try
{
transaction = dbConnection.BeginTransaction();
dbCommand = commandFactory(dbConnection, commandParameters);
dbCommand.Transaction = transaction;
dbDataReader = dbCommand.ExecuteReader();
TResult result = dbDataReader.Read() ? modelAdapter(dbDataReader) : default(TResult);
dbDataReader.Close();
transaction.Commit();
return(result);
}
catch
{
transaction.Rollback();
throw;
}
finally
{
dbDataReader.DisposeIfNotNull();
dbCommand.DisposeIfNotNull();
transaction.DisposeIfNotNull();
dbConnection.Close();
}
}
protected static TResult ExecuteNonQueryUsingTransactionAndReturnValue <TCommandParameters, TResult>(DbConnection dbConnection, CommandFactory <TCommandParameters> commandFactory, TCommandParameters commandParameters)
{
dbConnection.Open();
DbCommand dbCommand = null;
DbTransaction transaction = null;
try
{
dbCommand = commandFactory(dbConnection, commandParameters);
transaction = dbConnection.BeginTransaction();
dbCommand.Transaction = transaction;
dbCommand.ExecuteNonQuery();
TResult result = (TResult)dbCommand.Parameters[0].Value;
transaction.Commit();
return(result);
}
catch
{
transaction.Rollback();
throw;
}
finally
{
dbCommand.DisposeIfNotNull();
transaction.DisposeIfNotNull();
dbConnection.Close();
}
}
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();
}
}
private static void ExecuteNonQueryUsingTransaction(DbConnection dbConnection, DbCommand dbCommand)
{
dbConnection.Open();
DbTransaction transaction = null;
try
{
transaction = dbConnection.BeginTransaction();
dbCommand.Transaction = transaction;
dbCommand.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
dbCommand.DisposeIfNotNull();
transaction.DisposeIfNotNull();
dbConnection.Close();
}
}