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 TestExecuteResultSetsRowByRow()
{
var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType);
try
{
FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, OutputOption.RowByRow);
var executionPathResults = result.ExecutionPathResult.ToList();
Assert.AreEqual(3, executionPathResults.Count());
Assert.AreEqual("Result1", executionPathResults[0].Name);
var row = executionPathResults[0].Value;
Assert.AreEqual(1, row.Counter);
Assert.AreEqual("one", row.StringValue);
Assert.AreEqual("Result1", executionPathResults[1].Name);
row = executionPathResults[1].Value;
Assert.AreEqual(2, row.Counter);
Assert.AreEqual("two", row.StringValue);
Assert.AreEqual("Result2", executionPathResults[2].Name);
row = executionPathResults[2].Value;
Assert.AreEqual("qwer", row.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteResultSetsListOfRows()
{
var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType);
try
{
var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, OutputOption.ListOfRows).Value;
Assert.AreEqual(2, result.Result1Rows.Count);
var row = result.Result1Rows[0];
Assert.AreEqual(1, row.Counter);
Assert.AreEqual("one", row.StringValue);
row = result.Result1Rows[1];
Assert.AreEqual(2, row.Counter);
Assert.AreEqual("two", row.StringValue);
Assert.AreEqual(1, result.Result2Rows.Count);
row = result.Result2Rows[0];
Assert.AreEqual("qwer", row.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteResultSetRowByRowWithInvalidNumberOfResultSets()
{
var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType);
try
{
FunctionResult result = Execute(this.connectionType.ToConnectionTypeSelection(), null, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc",
parameters, new object[] { 2 }, FetchResultSets(this.connectionType), 1000, OutputOption.RowByRow);
var executionPathResults = result.ExecutionPathResult.ToList();
Assert.AreEqual(2, executionPathResults.Count());
Assert.AreEqual("Result1", executionPathResults[0].Name);
var row = executionPathResults[0].Value;
Assert.AreEqual(1, row.Counter);
Assert.AreEqual("one", row.StringValue);
row = executionPathResults[1].Value;
Assert.AreEqual(2, row.Counter);
Assert.AreEqual("two", row.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteResultSetRowByRowWithCustomType()
{
var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType);
try
{
var customType = TypeReference.CreateGeneratedType(new TypeProperty("Name", typeof(string)));
var resultSet = new DatabaseModel.ResultSet {
CustomType = customType
};
resultSet.Fields.Add(new DatabaseModel.ResultSetField("Counter", DatabaseModel.DataType.Int32, string.Empty));
resultSet.Fields.Add(new DatabaseModel.ResultSetField("StringValue", DatabaseModel.DataType.String, "Name"));
FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2 }, new DatabaseModel.ResultSets {
resultSet
}, OutputOption.RowByRow);
var executionPathResults = result.ExecutionPathResult.ToList();
Assert.AreEqual(2, executionPathResults.Count());
Assert.AreEqual("Result", executionPathResults[0].Name);
Assert.AreEqual(Names.GetValidName(customType.Name), executionPathResults[0].Value.GetType().Name);
Assert.AreEqual("one", executionPathResults[0].Value.Name);
Assert.AreEqual("two", executionPathResults[1].Value.Name);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteParametersAndReturnValue()
{
if (this.connectionType == ConnectionType.Oracle)
{
// Cannot return a value in an Oracle stored procedure - have to use OUT parameters
return;
}
var parameters = new DatabaseModel.ProcedureParameters(DatabaseObjectCreation.CreateParameterTestProc(this.connectionType));
try
{
List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 });
dynamic result = Execute(this.connectionType, "Test Proc", parameters, 1, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>()).Value;
Assert.AreEqual(1, result.ResultParameters.RETURN_VALUE);
Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut);
Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut);
Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestGetResultSetsForStoredProcedureWithDynamicSQL()
{
string createProcedureStatement = GetProcedureWithDynamicSQL(this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets = null;
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");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("Rowcount_Returned", field.ColumnName);
Assert.AreEqual("Rowcount_Returned", field.OutputName);
}
public void TestGetStoredProcedureDoesNotLoadFunction()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
string createFunctionStatement = GetFunctionStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseObjectCreation.CreateTestFunction(createFunctionStatement, this.connectionType);
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")));
Assert.IsFalse(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("TestFunction")));
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestFunction(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public void TestExecuteParameterAndResultSetsRowByRow()
{
var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType);
try
{
List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 });
FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>() }, true, OutputOption.RowByRow);
var executionPathResults = result.ExecutionPathResult.ToList();
Assert.AreEqual(3, executionPathResults.Count());
Assert.AreEqual("Result1", executionPathResults[0].Name);
var row = executionPathResults[0].Value;
Assert.AreEqual("qwer", row.StringValue);
Assert.AreEqual("Result2", executionPathResults[1].Name);
row = executionPathResults[1].Value;
Assert.AreEqual(1, row.Counter);
Assert.AreEqual("Qwer", row.StringValue);
row = executionPathResults[2].Value;
Assert.AreEqual(2, row.Counter);
Assert.AreEqual("Qwer", row.StringValue);
Assert.AreEqual("Qwer", result.Value.ResultParameters.StringValueOut);
Assert.AreEqual(new DateTime(1987, 1, 24), result.Value.ResultParameters.DateValueOut);
Assert.AreEqual(bytes, result.Value.ResultParameters.BytesValueOut);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteParameterAndResultSetsFirstRow(
[Values(OutputOption.FirstRow, OutputOption.FirstRowElseEmptyRow)]
OutputOption outputOption)
{
var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType);
try
{
List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 });
var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>() }, true, outputOption).Value;
Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut);
Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut);
Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut);
Assert.AreEqual("qwer", result.Result1.StringValue);
Assert.AreEqual(1, result.Result2.Counter);
Assert.AreEqual("Qwer", result.Result2.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteResultSetFirstRowNoRows()
{
var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType);
Assert.That(() => Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 0 }, true, OutputOption.FirstRow),
Throws.Exception.TypeOf <ExecuteException>()
.With.Property("Message").EqualTo("No rows returned for Result.\r\nSee Code and Parameter properties for more information."));
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
public void TestExecuteWithExpressionsInSQLString(ConnectionType connectionType)
{
DatabaseObjectCreation.CreateTestTable(connectionType);
try
{
string sql = @"INSERT INTO TestTable
(IntValue, DoubleValue, StringValue, DateValue, BytesValue)
VALUES (@{SomeIntValue - 3}, @{NullableDoubleValue}, @{Name + "" "" +
Surname}, @{Date}, @{Bytes})" ;
Execute(connectionType.ToConnectionTypeSelection(), null, sql, ExecuteSQLShared.ReturnModeType.FirstRowElseEmptyRow, new ResultType(), false,
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 30),
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 2, null),
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 3, "John Doe"),
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 4, DateTime.Today),
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 5, new List <byte> {
1, 2, 3
}));
sql = @"SELECT INTVALUE, DOUBLEVALUE, STRINGVALUE, DATEVALUE, BYTESVALUE FROM TestTable WHERE IntValue = @{TheIntValue}";
var resultType = new ResultType();
resultType.Fields.Add(new ResultTypeField {
ColumnName = "INTVALUE", Type = typeof(int), Name = "IntValue"
});
resultType.Fields.Add(new ResultTypeField {
ColumnName = "DOUBLEVALUE", Type = typeof(double), Name = "DoubleValue"
});
resultType.Fields.Add(new ResultTypeField {
ColumnName = "STRINGVALUE", Type = typeof(string), Name = "StringValue"
});
resultType.Fields.Add(new ResultTypeField {
ColumnName = "DATEVALUE", Type = typeof(DateTime), Name = "DateValue"
});
resultType.Fields.Add(new ResultTypeField {
ColumnName = "BYTESVALUE", Type = typeof(List <byte>), Name = "BytesValue"
});
dynamic dataOut = Execute(connectionType.ToConnectionTypeSelection(), null, sql, ExecuteSQLShared.ReturnModeType.ListOfRows, resultType, false,
new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 30)).Value;
Assert.AreEqual(1, dataOut.Count);
dynamic row = dataOut[0];
Assert.AreEqual(30, row.IntValue);
Assert.AreEqual(0, row.DoubleValue);
Assert.AreEqual("John Doe", row.StringValue);
Assert.AreEqual(DateTime.Today, row.DateValue);
Assert.AreEqual(new byte[] { 1, 2, 3 }, row.BytesValue);
}
finally
{
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 TestExecuteResultSetFirstRowElseEmptyRowNoRows()
{
var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType);
try
{
var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 0 }, true, OutputOption.FirstRowElseEmptyRow).Value;
Assert.AreEqual(0, result.Result.Counter);
Assert.AreEqual(string.Empty, result.Result.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.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 TestExecuteParametersAndNoReturnValue()
{
var parameters = new DatabaseModel.ProcedureParameters(DatabaseObjectCreation.CreateParameterTestProc(this.connectionType).Where(p => p.Direction != DatabaseModel.ParameterDirection.ReturnValue));
try
{
List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 });
dynamic result = Execute(this.connectionType, "Test Proc", parameters, 1, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>()).Value;
Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut);
Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut);
Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestTableData()
{
var columns = DatabaseObjectCreation.CreateTestTable(this.connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetTableNames(connection).Any(t => t.IndexOf("TestTable", StringComparison.InvariantCultureIgnoreCase) != -1));
}
}
finally
{
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(5, columns.Count);
var column = columns[0];
Assert.AreEqual("INTVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Int32, column.DataType);
Assert.IsFalse(column.IsNullable);
column = columns[1];
Assert.AreEqual("DOUBLEVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Double, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[2];
Assert.AreEqual("STRINGVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.String, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[3];
Assert.AreEqual("DATEVALUE", column.Name.ToUpper());
Assert.AreEqual(this.connectionType == ConnectionType.OleDb ? DatabaseModel.DataType.String : DatabaseModel.DataType.Date,
column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[4];
Assert.AreEqual("BYTESVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Binary, column.DataType);
Assert.IsTrue(column.IsNullable);
}
public void TestExecuteResultSetsFirstRow(
[Values(OutputOption.FirstRow, OutputOption.FirstRowElseEmptyRow)]
OutputOption outputOption)
{
var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType);
try
{
var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, outputOption).Value;
Assert.AreEqual(1, result.Result1.Counter);
Assert.AreEqual("one", result.Result1.StringValue);
Assert.AreEqual("qwer", result.Result2.StringValue);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteNoParametersAndReturnValue()
{
if (this.connectionType == ConnectionType.Oracle)
{
// Cannot return a value in an Oracle stored procedure - have to use OUT parameters
return;
}
var parameters = DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN RETURN 1 END;", this.connectionType);
try
{
dynamic result = Execute(this.connectionType, "Test Proc", parameters).Value;
Assert.AreEqual(1, result.ResultParameters.RETURN_VALUE);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
}
public void TestExecuteWithTransaction(ConnectionType connectionType)
{
DatabaseObjectCreation.CreateTestTable(connectionType);
try
{
string sql = @"INSERT INTO TestTable (IntValue) VALUES (@{Value})";
foreach (var transaction in new BeginTransaction.BeginTransactionX(connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(connectionType), IsolationLevel.ReadCommitted))
{
Execute(transaction, sql, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 0));
Execute(transaction, sql, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 1));
}
object rowCount = Helpers.DatabaseHelpers.ExecuteSqlScalar("SELECT COUNT(*) FROM TestTable", connectionType);
int rowCountInt = connectionType == ConnectionType.Oracle ? (int)(decimal)rowCount : (int)rowCount;
Assert.AreEqual(2, rowCountInt);
}
finally
{
DatabaseObjectCreation.RemoveTestTable(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);
}
public void TestGetResultSetsForStoredProcedure()
{
var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType).Where(p => (p.Direction != DatabaseModel.ParameterDirection.ReturnValue) && (p.DataType != DatabaseModel.DataType.RefCursor)).ToList();
DatabaseModel.ResultSets resultSets;
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
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");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(8, parameters.Count);
var parameter = parameters[0];
Assert.AreEqual("@IntValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Int32, parameter.DataType);
parameter = parameters[1];
Assert.AreEqual("@DoubleValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Double, parameter.DataType);
parameter = parameters[2];
Assert.AreEqual("@StringValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[3];
Assert.AreEqual("@StringValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[4];
Assert.AreEqual("@DateValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[5];
Assert.AreEqual("@DateValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[6];
Assert.AreEqual("@BytesValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
parameter = parameters[7];
Assert.AreEqual("@BytesValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
Assert.AreEqual(2, 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);
fields = resultSets[1].Fields;
Assert.AreEqual(2, fields.Count);
field = fields[0];
Assert.AreEqual("Counter", field.ColumnName);
Assert.AreEqual("Counter", field.OutputName);
field = fields[1];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
Assert.AreEqual(DatabaseModel.DataType.String, field.DataType);
}
