internal static void DbContextTransaction(AdventureWorks adventureWorks)
{
adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
{
using (IDbContextTransaction transaction = adventureWorks.Database
.BeginTransaction(IsolationLevel.ReadUncommitted))
{
try
{
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
adventureWorks.ProductCategories.Add(category);
adventureWorks.SaveChanges().WriteLine(); // 1
adventureWorks.Database
.ExecuteSqlCommand($@"DELETE FROM [Production].[ProductCategory] WHERE [Name] = {nameof(ProductCategory)}")
.WriteLine(); // 1
adventureWorks.CurrentIsolationLevel().WriteLine(); // ReadUncommitted
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
});
}
internal static async Task DbContextTransactionAsync(AdventureWorks adventureWorks)
{
await adventureWorks.Database.CreateExecutionStrategy().ExecuteAsync(async() =>
{
using (IDbContextTransaction transaction = await adventureWorks.Database.BeginTransactionAsync(
IsolationLevel.ReadUncommitted))
{
try
{
adventureWorks.CurrentIsolationLevel().WriteLine(); // ReadUncommitted
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
await adventureWorks.ProductCategories.AddAsync(category);
(await adventureWorks.SaveChangesAsync()).WriteLine(); // 1
await adventureWorks.Database.ExecuteSqlCommandAsync(
sql: "DELETE FROM [Production].[ProductCategory] WHERE [Name] = {0}",
parameters: nameof(ProductCategory)).WriteLine(); // 1
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
});
}
internal static void DbTransaction()
{
using (DbConnection connection = new SqlConnection(ConnectionStrings.AdventureWorks))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
using (AdventureWorks adventureWorks = new AdventureWorks(connection))
{
adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
{
adventureWorks.Database.UseTransaction(transaction);
adventureWorks.CurrentIsolationLevel().WriteLine(); // RepeatableRead
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
adventureWorks.ProductCategories.Add(category);
adventureWorks.SaveChanges().WriteLine(); // 1.
});
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = @Name";
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "@Name";
parameter.Value = nameof(ProductCategory);
command.Parameters.Add(parameter);
command.Transaction = transaction;
command.ExecuteNonQuery().WriteLine(); // 1
connection.CurrentIsolationLevel(transaction).WriteLine(); // RepeatableRead
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
internal static async Task DbTransactionAsync()
{
using (SqlConnection connection = new SqlConnection(ConnectionStrings.AdventureWorks))
{
await connection.OpenAsync();
using (DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
{
try
{
using (AdventureWorks adventureWorks = new AdventureWorks(connection))
{
await adventureWorks.Database.CreateExecutionStrategy().ExecuteAsync(async() =>
{
adventureWorks.Database.UseTransaction(transaction);
adventureWorks.CurrentIsolationLevel().WriteLine(); // Serializable
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
await adventureWorks.ProductCategories.AddAsync(category);
(await adventureWorks.SaveChangesAsync()).WriteLine(); // 1.
});
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = @p0";
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "@p0";
parameter.Value = nameof(ProductCategory);
command.Parameters.Add(parameter);
command.Transaction = transaction;
(await command.ExecuteNonQueryAsync()).WriteLine(); // 1
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
internal static void TransactionScope(AdventureWorks adventureWorks)
{
adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
{
using (TransactionScope scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.Serializable
}))
{
using (DbConnection connection = new SqlConnection(ConnectionStrings.AdventureWorks))
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [Production].[ProductCategory] ([Name]) VALUES(@Name); ";
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "@Name";
parameter.Value = nameof(ProductCategory);
command.Parameters.Add(parameter);
connection.Open();
command.ExecuteNonQuery().WriteLine(); // 1
connection.CurrentIsolationLevel().WriteLine(); // Serializable
}
using (AdventureWorks adventureWorks1 = new AdventureWorks())
{
ProductCategory category = adventureWorks1.ProductCategories
.Single(entity => entity.Name == nameof(ProductCategory));
adventureWorks1.ProductCategories.Remove(category);
adventureWorks1.SaveChanges().WriteLine(); // 1
adventureWorks1.CurrentIsolationLevel().WriteLine(); // Serializable
}
scope.Complete();
}
});
}
