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 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 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);
}