public void ExecuteNonQuery_Transaction_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" DELETE FROM EMP "); cmd.TransactionBegin(); cmd.ExecuteNonQuery(); cmd.TransactionRollback(); Assert.AreEqual(EMP.GetEmployeesCount(_connection), 14); } }
public async Task ExecuteNonQueryAsync_Transaction_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" DELETE FROM EMP "); cmd.TransactionBegin(); int count = await cmd.ExecuteNonQueryAsync(); cmd.TransactionRollback(); Assert.AreEqual(14, count); Assert.AreEqual(14, EMP.GetEmployeesCount(_connection)); } }
public void Transaction_TwoBeginTransaction_Test() { // Main Transaction using (var cmd1 = new DatabaseCommand(_connection)) { cmd1.Log = Console.WriteLine; var trans1 = cmd1.TransactionBegin(); // Second Transaction using (var cmd2 = new DatabaseCommand(trans1)) { // Raise an exception var trans2 = cmd2.TransactionBegin(); } cmd1.TransactionRollback(); } }
public void ExecuteNonQuery_TransactionForTwoIncludedCommands_Test() { using (var cmd1 = new DatabaseCommand(_connection)) { cmd1.Log = Console.WriteLine; cmd1.CommandText.AppendLine(" DELETE FROM EMP "); cmd1.TransactionBegin(); cmd1.ExecuteNonQuery(); using (var cmd2 = new DatabaseCommand(cmd1.Transaction)) { cmd2.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP "); int count = cmd2.ExecuteScalar <int>(); } cmd1.TransactionRollback(); } }
public void ExecuteNonQuery_TransactionForTwoCommands_Test() { DbTransaction currentTransaction; using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" DELETE FROM EMP "); currentTransaction = cmd.TransactionBegin(); cmd.ExecuteNonQuery(); Assert.AreEqual(EMP.GetEmployeesCount(currentTransaction), 0); // Inside the transaction cmd.TransactionRollback(); Assert.AreEqual(EMP.GetEmployeesCount(_connection), 14); // Ouside the transaction } }
public void Transaction_GetInternalTransaction_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" DELETE FROM EMP "); var trans1 = DataExtensions.GetTransaction(_connection); var trans2A = cmd.TransactionBegin(); var trans2B = DataExtensions.GetTransaction(_connection); cmd.ExecuteNonQuery(); cmd.TransactionRollback(); var trans3 = DataExtensions.GetTransaction(_connection); Assert.AreEqual(EMP.GetEmployeesCount(_connection), 14); Assert.AreEqual(null, trans1); Assert.AreEqual(trans2A, trans2B); Assert.AreEqual(null, trans3); } }
private DbException RaiseSqlDeadLock(bool withRetry, bool throwException) { // See: http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server SqlConnection connection2 = new SqlConnection(Configuration.CONNECTION_STRING); connection2.Open(); DbException exToReturn = null; try { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = @" CREATE TABLE ##Employees ( EmpId INT IDENTITY, EmpName VARCHAR(16), Phone VARCHAR(16) ) INSERT INTO ##Employees (EmpName, Phone) VALUES('Martha', '800-555-1212'), ('Jimmy', '619-555-8080') CREATE TABLE ##Suppliers ( SupplierId INT IDENTITY, SupplierName VARCHAR(64), Fax VARCHAR(16) ) INSERT INTO ##Suppliers (SupplierName, Fax) VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234') "; cmd.ExecuteNonQuery(); } using (var cmd1 = new DatabaseCommand(_connection)) { using (var cmd2 = new DatabaseCommand(connection2)) { cmd1.Log = Console.WriteLine; cmd2.Log = Console.WriteLine; cmd1.TransactionBegin(); cmd2.TransactionBegin(); cmd1.CommandText = " UPDATE ##Employees SET EmpName = 'Mary' WHERE empid = 1 "; cmd1.ExecuteNonQuery(); cmd2.CommandText = " UPDATE ##Suppliers SET Fax = N'555-1212' WHERE supplierid = 1 "; cmd2.ExecuteNonQuery(); // Start and when cmd2.ExecuteNonQuery command will be executed, an DeadLock exception will be raised. Task task1 = Task.Factory.StartNew(() => { cmd1.ThrowException = throwException; if (withRetry) { cmd1.Retry.SetDefaultCriteriaToRetry(RetryDefaultCriteria.SqlServer_DeadLock); } cmd1.CommandText = " UPDATE ##Suppliers SET Fax = N'555-1212' WHERE supplierid = 1 "; cmd1.ExecuteNonQuery(); }); System.Threading.Thread.Sleep(500); cmd2.CommandText = " UPDATE ##Employees SET phone = N'555-9999' WHERE empid = 1 "; cmd2.ExecuteNonQuery(); cmd2.Dispose(); connection2.Close(); // Wait cmd1 finished (and raised an Exception) task1.Wait(); exToReturn = cmd1.Exception; } } DropTemporaryTable(); } finally { connection2.Close(); connection2.Dispose(); connection2 = null; } return(exToReturn); }