public void TestExecuteRollback(ConnectionType connectionType, object isolationLevel)
{
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", connectionType);
DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", connectionType);
try
{
var functionResult = Execute(connectionType, DatabaseHelpers.GetDefaultConnectionString(connectionType), isolationLevel);
Assert.Catch <Exception>(
() => RunExecutionPath(functionResult.ExecutionPathResult,
transaction =>
{
var command = transaction.GetDbTransaction().Connection.CreateCommand();
command.Transaction = transaction.GetDbTransaction();
command.CommandText = "\"Test Proc\"";
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
throw new Exception("Bad things are happening!!");
}));
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(connectionType);
DatabaseObjectCreation.RemoveTestTable(connectionType);
}
}
public void TestExecute([Values(DbBulkCopy.ConnectionType.SqlServer, DbBulkCopy.ConnectionType.Oracle)] DbBulkCopy.ConnectionType connectionType)
{
DatabaseObjectCreation.CreateTestTable(ToCommonConnectionType(connectionType));
try
{
var functionResult = Execute(connectionType, "TESTTABLE");
foreach (var nextResult in functionResult.ExecutionPathResult)
{
dynamic row = new ExpandoObject();
row.IntValue = row.INTVALUE = 1;
row.DoubleValue = row.DOUBLEVALUE = 1.2;
row.StringValue = row.STRINGVALUE = "Qwer";
row.DateValue = row.DATEVALUE = new DateTime(2015, 5, 13);
row.BytesValue = row.BYTESVALUE = new List <byte> {
1, 2, 3
};
nextResult.Value.Write = row;
row = new ExpandoObject();
row.IntValue = row.INTVALUE = 2;
row.DoubleValue = row.DOUBLEVALUE = null;
row.StringValue = row.STRINGVALUE = null;
row.DateValue = row.DATEVALUE = null;
row.BytesValue = row.BYTESVALUE = null;
nextResult.Value.Write = row;
}
var results = DatabaseHelpers.GetDataTable(@"SELECT * FROM TESTTABLE", ToCommonConnectionType(connectionType));
Assert.AreEqual(2, results.Rows.Count);
object[] rowItems = results.Rows[0].ItemArray;
Assert.AreEqual(1, rowItems[0]);
Assert.AreEqual(1.2, rowItems[1]);
Assert.AreEqual("Qwer", rowItems[2]);
Assert.AreEqual(new DateTime(2015, 5, 13), rowItems[3]);
Assert.AreEqual(new byte[] { 1, 2, 3 }, rowItems[4]);
rowItems = results.Rows[1].ItemArray;
Assert.AreEqual(2, rowItems[0]);
Assert.AreEqual(DBNull.Value, rowItems[1]);
Assert.AreEqual(DBNull.Value, rowItems[2]);
Assert.AreEqual(DBNull.Value, rowItems[3]);
Assert.AreEqual(DBNull.Value, rowItems[4]);
}
finally
{
DatabaseObjectCreation.RemoveTestTable(ToCommonConnectionType(connectionType));
}
}
public void TestExecuteNoParametersAndNoReturnValue()
{
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", this.connectionType);
try
{
Execute(this.connectionType, "Test Proc");
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(1, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestExecuteWithTransaction()
{
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", this.connectionType);
try
{
foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted))
{
Execute(transaction, "Test Proc");
Execute(transaction, "Test Proc");
}
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(2, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestExecuteWithTransactionAndResultSetOnException()
{
string createProcedureStatement = this.connectionType == ConnectionType.Oracle
? @"CREATE PROCEDURE ""Test Proc"" (
ResultSet OUT SYS_REFCURSOR
)
AS BEGIN
INSERT INTO TestTable (IntValue) VALUES (0);
OPEN ResultSet FOR SELECT 'qwer' AS ""StringValue"" FROM Dual;
END;"
: @"CREATE PROCEDURE ""Test Proc""
AS BEGIN
INSERT INTO TestTable (IntValue) VALUES (0);
SELECT 'qwer' AS StringValue;
END;" ;
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
var parameters = DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
try
{
Assert.Throws <Exception>(() =>
{
foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted))
{
var result = Execute(transaction, "Test Proc", parameters, new object[0], true, OutputOption.FirstRow).Value;
Assert.AreEqual("qwer", result.Result.StringValue);
throw new Exception("Bad things are happening!!");
}
});
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestExecuteWithTransactionOnSqlError()
{
string createProcedureStatement = this.connectionType == ConnectionType.Oracle
? @"CREATE PROCEDURE ""Test Proc"" (
""@IntValue"" INT
)
AS BEGIN
INSERT INTO TestTable (IntValue) VALUES (0 / ""@IntValue"");
END;"
: @"CREATE PROCEDURE ""Test Proc"" (
@IntValue INT
)
AS BEGIN
INSERT INTO TestTable (IntValue) VALUES (0 / @IntValue);
END;" ;
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
var parameters = DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
try
{
Assert.Catch <ExecuteException>(() =>
{
foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted))
{
Execute(transaction, "Test Proc", parameters, new object[] { 1 });
Execute(transaction, "Test Proc", parameters, new object[] { 0 });
}
});
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestExecuteWhereInputParametersMatchFunctionPropertyNames()
{
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TESTTABLE ( INTVALUE1 INT, INTVALUE2 INT)", this.connectionType);
string createProcedureStatement = (this.connectionType == ConnectionType.Oracle) ?
@"CREATE PROCEDURE ""Test Proc"" (
""@{0}"" INT,
""@{1}"" INT
)
AS BEGIN
INSERT INTO TESTTABLE (""INTVALUE1"",""INTVALUE2"") VALUES (""@{0}"",""@{1}"");
END;"
: @"CREATE PROCEDURE ""Test Proc"" (
@{0} INT,
@{1} INT
)
AS BEGIN
INSERT INTO TestTable (IntValue1, IntValue2) VALUES(@{0}, @{1})
END;" ;
var parameters = new DatabaseModel.ProcedureParameters(
DatabaseObjectCreation.CreateTestProc(string.Format(createProcedureStatement, ExecuteStoredProcedureShared.ParametersPropertyName, DbShared.TransactionPropertyName), connectionType)
.Where(p => p.Direction != DatabaseModel.ParameterDirection.ReturnValue));
try
{
Execute(this.connectionType, "Test Proc", parameters, 1, 2);
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TESTTABLE", this.connectionType);
Assert.AreEqual(1, results.Rows.Count);
Assert.AreEqual(1, results.Rows[0][0]);
Assert.AreEqual(2, results.Rows[0][1]);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestGetResultSetForStoredProcedureDataDoesNotRunProcedure()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets;
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
}