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);
}
}
private void Save()
{
DatabaseModel.ProcedureParameters savedParameters = new DatabaseModel.ProcedureParameters();
int parameterPosition = 0;
foreach (var nextParameterModel in Parameters)
{
if (string.IsNullOrEmpty(nextParameterModel.Name))
{
continue;
}
if (savedParameters.Any(p => p.Name.Equals(nextParameterModel.Name, StringComparison.CurrentCultureIgnoreCase)))
{
if (ShowMessage != null)
{
ShowMessage(this, new ShowMessageEventArgs("Duplicate parameter name: " + nextParameterModel.Name, "Save parameters", MessageBoxButton.OK, MessageBoxImage.Asterisk));
}
return;
}
if ((nextParameterModel.Direction == DatabaseModel.ParameterDirection.ReturnValue) && (savedParameters.Any(p => p.Direction == DatabaseModel.ParameterDirection.ReturnValue)))
{
if (ShowMessage != null)
{
ShowMessage(this, new ShowMessageEventArgs("At most one Return Value parameter is allowed.", "Save parameters", MessageBoxButton.OK, MessageBoxImage.Asterisk));
}
return;
}
nextParameterModel.ProcedureParameter.Position = parameterPosition++;
savedParameters.Add(nextParameterModel.ProcedureParameter);
}
EditingInfo.ShouldUpdateProcedureParameters = true;
EditingInfo.ProcedureParameters = savedParameters;
DialogResult = true;
}
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);
}
}
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()));
}
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);
}
}
private IFunctionData UpdateToVersion2(IFunctionData data)
{
IPropertyData parametersProperty;
if (data.TryFindPropertyById(ExecuteStoredProcedureShared.ParametersPropertyName, out parametersProperty))
{
DatabaseModel.ProcedureParameters parameterValues = parametersProperty.Value as DatabaseModel.ProcedureParameters;
if (parameterValues != null)
{
foreach (DatabaseModel.ProcedureParameter parameter in parameterValues)
{
IPropertyData parameterProperty;
if (data.TryFindPropertyById(parameter.Name, out parameterProperty))
{
data = data.UpdateProperty(parameterProperty,
parameter.DisplayPropertyId, parameter.DisplayPropertyName, parameterProperty.TypeReference, parameterProperty.Value, parameterProperty.IsVisible, parameterProperty.ValueUsage);
}
}
}
}
return(data.UpdateVersion("2"));
}
private static FunctionResult Execute(Transaction transaction, string storedProcedureName, DatabaseModel.ProcedureParameters parameters = null, object[] parameterValues = null, bool fetchResultSets = false, OutputOption outputOption = OutputOption.RowByRow)
{
return(Execute(ConnectionTypeSelection.UseTransaction, transaction, null, storedProcedureName, parameters, parameterValues, fetchResultSets ? FetchResultSets(transaction.GetConnectionType()) : null, null, outputOption));
}
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));
}
private static FunctionResult Execute(ConnectionType connectionType, string connectionString, string storedProcedureName, DatabaseModel.ProcedureParameters parameters = null, object[] parameterValues = null, bool fetchResultSets = false, OutputOption outputOption = OutputOption.RowByRow)
{
return(Execute(connectionType, connectionString, storedProcedureName, parameters, parameterValues, fetchResultSets ? FetchResultSets(connectionType) : null, outputOption));
}
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));
}
