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 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 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 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 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 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 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 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);
}
}
private static DatabaseModel.ResultSets FetchResultSets(ConnectionType connectionType)
{
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(databaseAssistant.GetResultSets(connection, "Test Proc"));
}
}
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 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 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 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);
}
private static FunctionResult Execute(ConnectionType connectionType, string storedProcedureName, DatabaseModel.ProcedureParameters parameters = null, params object[] parameterValues)
{
return(Execute(connectionType, DatabaseHelpers.GetDefaultConnectionString(connectionType), storedProcedureName, parameters, parameterValues, false));
}
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 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);
}
private FunctionResult Execute(DbBulkCopy.ConnectionType connectionType, string tableName)
{
string connectionString = DatabaseHelpers.GetDefaultConnectionString(ToCommonConnectionType(connectionType));
return(Execute(connectionType, connectionString, tableName));
}