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);
}
private void ResetProcedureParameters()
{
procedureParameters = null;
NotifyPropertyChanged("HasProcedureParameters");
NotifyPropertyChanged("ProcedureParameters");
resultSets = null;
NotifyPropertyChanged("HasResultSets");
NotifyPropertyChanged("ResultSets");
NotifyPropertyChanged("ResultSetCount");
NotifyPropertyChanged("ResultSetCountText");
}
private static FunctionResult Execute(ConnectionTypeSelection connectionType, Transaction transaction, string connectionString, string storedProcedureName,
DatabaseModel.ProcedureParameters parameters = null, object[] parameterValues = null, DatabaseModel.ResultSets resultSets = null, int?numberOfResultSets = null,
OutputOption outputOption = OutputOption.RowByRow)
{
if (parameters == null)
{
parameters = new DatabaseModel.ProcedureParameters();
}
if (resultSets == null)
{
resultSets = new DatabaseModel.ResultSets();
}
int i = 0, j = 0;
var tester = (new FunctionTester <ExecuteStoredProcedure.ExecuteStoredProcedure>()).Compile(new PropertyValue[] {
new PropertyValue(DbShared.ConnectionTypePropertyName, connectionType),
new PropertyValue(ExecuteStoredProcedureShared.ParametersPropertyName, parameters),
new PropertyValue(ExecuteStoredProcedureShared.OutputOptionPropertyName, outputOption),
new PropertyValue(ExecuteStoredProcedureShared.ResultSetCountPropertyName, (numberOfResultSets.HasValue)? numberOfResultSets.Value : resultSets.Count)
}.Concat(
resultSets.Select(r => new PropertyValue(string.Format(ExecuteStoredProcedureShared.ResultSetPropertyNameFormat, ++i), resultSets[j++]))
).ToArray());
i = 0;
var executeParameters = new ParameterValue[]
{
new ParameterValue(ExecuteStoredProcedureShared.StoredProcedurePropertyName, storedProcedureName)
};
if (connectionType == ConnectionTypeSelection.UseTransaction)
{
executeParameters = executeParameters.Concat(new ParameterValue[] { new ParameterValue(DbShared.TransactionPropertyName, transaction) }).ToArray();
}
else
{
executeParameters = executeParameters.Concat(new ParameterValue[] { new ParameterValue(DbShared.ConnectionStringPropertyName, connectionString) }).ToArray();
}
return(tester.Execute(executeParameters
.Concat(
parameters.Where(p => (p.Direction == DatabaseModel.ParameterDirection.In) || (p.Direction == DatabaseModel.ParameterDirection.InOut))
.Select(p => new ParameterValue(p.DisplayPropertyName, parameterValues[i++]))).ToArray()));
}
private static FunctionResult Execute(ConnectionType connectionType, string connectionString, string storedProcedureName, DatabaseModel.ProcedureParameters parameters = null, object[] parameterValues = null, DatabaseModel.ResultSets resultSets = null, OutputOption outputOption = OutputOption.RowByRow)
{
return(Execute(connectionType.ToConnectionTypeSelection(), null, connectionString, storedProcedureName, parameters, parameterValues, resultSets, null, outputOption));
}
