public void UpdateTagAfterExecution_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
// Tag 1
cmd.TagWith("Tag1");
cmd.CommandText = "SELECT * FROM EMP";
cmd.ActionBeforeExecution = (query) =>
{
Assert.AreEqual($"Tag1", query.Tags.First());
Assert.AreEqual($"SELECT * FROM EMP", query.CommandText);
Assert.AreEqual($"-- Tag1{NEW_LINE}SELECT * FROM EMP", query.Formatted.CommandAsText);
};
cmd.ExecuteNonQuery();
// Tag 2
cmd.TagWith("Tag2");
cmd.CommandText = "SELECT * FROM EMP";
cmd.ActionBeforeExecution = (query) =>
{
Assert.AreEqual($"Tag1", query.Tags.First());
Assert.AreEqual($"SELECT * FROM EMP", query.CommandText);
Assert.AreEqual($"-- Tag1{NEW_LINE}-- Tag2{NEW_LINE}SELECT * FROM EMP", query.Formatted.CommandAsText);
};
cmd.ExecuteNonQuery();
}
}
public int Insert(DataAspect aspect, IEnumerable <AspectMemberValue> values, out object identityValue)
{
using (IDbConnection conn = _dialect.CreateConnection(_ConnectionString))
{
conn.Open();
try
{
bool hasIdentity;
DatabaseCommand cmd = _dialect.CreateInsert(aspect, values, out hasIdentity);
if (hasIdentity)
{
identityValue = cmd.ExecuteScalar(conn);
return(1);
}
else
{
identityValue = null;
return(cmd.ExecuteNonQuery(conn));
}
}
finally
{
conn.Close();
}
}
}
private void DropTemporaryTable()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @" DROP TABLE ##Employees
DROP TABLE ##Suppliers ";
cmd.ExecuteNonQuery();
}
}
public void ExecuteNonQuery_NoData_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" DELETE FROM EMP WHERE EMPNO = 99999 ");
var count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
}
}
public LocalDbBuilder Create()
{
var script = $"CREATE DATABASE {dbName}";
using (var connection = new SqlConnection(masterConnectionString))
using (var databaseCommand = new DatabaseCommand(connection))
{
connection.Open();
databaseCommand.CommandText.AppendLine(script);
databaseCommand.ExecuteNonQuery();
}
return(this);
}
public LocalDbBuilder Drop()
{
var script = $@"
ALTER DATABASE {dbName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE {dbName};";
using (var connection = new SqlConnection(masterConnectionString))
using (var databaseCommand = new DatabaseCommand(connection))
{
connection.Open();
databaseCommand.CommandText.AppendLine(script);
databaseCommand.ExecuteNonQuery();
}
return(this);
}
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 void Mock_ExecuteNonQuery_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("INSERT"))
.ReturnsScalar(14);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("INSERT ...");
cmd.AddParameter("@ID", 1);
var result = cmd.ExecuteNonQuery();
Assert.AreEqual(14, result);
}
}
public void SimpleTag_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.TagWith("My command");
cmd.CommandText = "SELECT * FROM EMP";
cmd.ActionBeforeExecution = (query) =>
{
Assert.AreEqual($"My command", query.Tags.First());
Assert.AreEqual($"SELECT * FROM EMP", query.CommandText);
Assert.AreEqual($"-- My command{NEW_LINE}SELECT * FROM EMP", query.Formatted.CommandAsText);
};
cmd.ExecuteNonQuery();
}
}
public LocalDbBuilder ExecuteScript(string sqlScript)
{
try
{
using (var connection = new SqlConnection(ConnectionString))
using (var databaseCommand = new DatabaseCommand(connection))
{
connection.Open();
databaseCommand.CommandText.AppendLine(sqlScript);
databaseCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(this);
}
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 TagMultilineTags_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.TagWith($"Tag1{NEW_LINE}Tag2");
cmd.CommandText = "SELECT * FROM EMP";
cmd.ActionBeforeExecution = (query) =>
{
Assert.AreEqual($"Tag1", query.Tags.ElementAt(0));
Assert.AreEqual($"Tag2", query.Tags.ElementAt(1));
Assert.AreEqual($"SELECT * FROM EMP", query.CommandText);
Assert.AreEqual($"-- Tag1{NEW_LINE}-- Tag2{NEW_LINE}SELECT * FROM EMP", query.Formatted.CommandAsText);
};
cmd.ExecuteNonQuery();
}
}
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 ExecuteNonQuery_ActionAfter_Test()
{
bool isPassed = false;
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
cmd.ActionAfterExecution = (command, tables) =>
{
Assert.AreEqual(-1, tables.First().Rows[0][0]);
isPassed = true;
};
int rowsAffected = cmd.ExecuteNonQuery();
Assert.AreEqual(-1, rowsAffected);
Assert.IsTrue(isPassed);
}
}
public void ExecuteNonQuery_ActionBefore_Test()
{
bool isPassed = false;
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
cmd.ActionBeforeExecution = (command) =>
{
command.CommandText.Clear();
command.CommandText.Append("SELECT 1+1 FROM EMP");
isPassed = true;
};
cmd.ExecuteNonQuery();
Assert.IsTrue(isPassed);
Assert.AreEqual("SELECT 1+1 FROM EMP", cmd.CommandText.ToString());
}
}
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);
}
}
public void ExecuteNonQuery_DefineTransactionBefore_Test()
{
using (var transaction = _connection.BeginTransaction())
{
using (var cmd = new DatabaseCommand(transaction))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" INSERT INTO EMP (EMPNO, ENAME) VALUES (1234, 'ABC') ");
cmd.ExecuteNonQuery();
}
using (var cmd = new DatabaseCommand(transaction))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" INSERT INTO EMP (EMPNO, ENAME) VALUES (9876, 'XYZ') ");
cmd.ExecuteNonQuery();
}
transaction.Rollback();
Assert.AreEqual(EMP.GetEmployeesCount(_connection), 14);
}
}
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);
}