With the exception of the .Execute() method, the other Execute* methods of the StoredProcedure class do not make a call to the Command's GetOutputParameters method. This results in null output parameters and return values when using these methods. I first came across this issue when we used a non-zero return value from the proc to indicate why the resultset was empty (using the ExecuteTypedList method). (Using SQL 2008 as back end database).
Caveat I do not fully understand the ramifications (if any) outside my testing to the changes I am mentioning which, at least in the case of SQL Server 2008, correct this deficiency.
1) I modified the IDataProvider interface to provide an overload to ExecuteReader when returns out as a second argument the DbCommand object that was used to invoke the call. (By overloading the method, I prevented breaking code I do not understand at this time, but admit I do not fully understand the ramifications to modifying this interface if others have already implemented it outside of the SubSonic.Code project.)
2) Within DbDataProvider I:
changed the ExecuteReader method to return out the DbCommand object setup within the proc and then created a wrapper method to implement the original interface method.
Added the call qry.GetOutputParameters(cmd); to the ExecuteDataSet(QueryCommand qry) method right before the return ds; statement.
Added the call qry.GetOutputParameters(cmd); to the ExecuteScalar(QueryCommand qry) method right before the closing brace of the using statement.
Changed the public IList<T> ToList<T>(QueryCommand qry) where T : new() method to invoke the new ExecuteReader(qry, out cmd) method and then invoke qry.GetOutputParameters(cmd); immediately before the return statement.
Note: At this time I am unsure whether any of the other Execute* methods need updating. The above changes allowed me to get the return value in all cases I could come up with to test.
3) Finally, I created an overload for ExecuteReader in the StoredProcedure class that invoked the oveloaded ExecuteReader method of DbDataProvider and returned out the DbCommand. This allows for the invoking code to use the reader as needed, close the DbDataReader instance (THIS IS IMPORTANT), and then call sp.Command.GetOutputParamenters(cmd); to populate the output parameters and return value.
While I would love to formally submit these changes as a Pull Request, I am unsure of the ramifications of my changes to the other database types and without being able to run the unit tests included in the git source solution, I figured I'd rather be safe than sorry.
I did create a set of 8 standalone stored procedures SQL Server stored procedures as well as xUnit tests for all the conditions where I discovered the call to GetOutputParameters was missing and the tests now show the code changes as functional. (visual studio 2010 project)
Finally, note that in order for OutputParameter to be functional, the StoredProcedure.cs that the templates generate must be hand manipulated as they are not correctly identified as pointed out in Issue # 118. Additionally, the T4 template must be updated so that it inserts the following before the return sp; line: sp.Command.AddReturnParameter();
With the exception of the
.Execute()method, the other Execute* methods of the StoredProcedure class do not make a call to the Command's GetOutputParameters method. This results in null output parameters and return values when using these methods. I first came across this issue when we used a non-zero return value from the proc to indicate why the resultset was empty (using the ExecuteTypedList method). (Using SQL 2008 as back end database).Caveat I do not fully understand the ramifications (if any) outside my testing to the changes I am mentioning which, at least in the case of SQL Server 2008, correct this deficiency.
1) I modified the IDataProvider interface to provide an overload to ExecuteReader when returns out as a second argument the DbCommand object that was used to invoke the call. (By overloading the method, I prevented breaking code I do not understand at this time, but admit I do not fully understand the ramifications to modifying this interface if others have already implemented it outside of the SubSonic.Code project.) 2) Within DbDataProvider I:
qry.GetOutputParameters(cmd);to theExecuteDataSet(QueryCommand qry)method right before thereturn ds;statement.qry.GetOutputParameters(cmd);to theExecuteScalar(QueryCommand qry)method right before the closing brace of the using statement.public IList<T> ToList<T>(QueryCommand qry) where T : new()method to invoke the newExecuteReader(qry, out cmd)method and then invokeqry.GetOutputParameters(cmd);immediately before the return statement. Note: At this time I am unsure whether any of the other Execute* methods need updating. The above changes allowed me to get the return value in all cases I could come up with to test. 3) Finally, I created an overload for ExecuteReader in the StoredProcedure class that invoked the oveloaded ExecuteReader method of DbDataProvider and returned out the DbCommand. This allows for the invoking code to use the reader as needed, close the DbDataReader instance (THIS IS IMPORTANT), and then callsp.Command.GetOutputParamenters(cmd);to populate the output parameters and return value.While I would love to formally submit these changes as a Pull Request, I am unsure of the ramifications of my changes to the other database types and without being able to run the unit tests included in the git source solution, I figured I'd rather be safe than sorry.
I did create a set of 8 standalone stored procedures SQL Server stored procedures as well as xUnit tests for all the conditions where I discovered the call to GetOutputParameters was missing and the tests now show the code changes as functional. (visual studio 2010 project)
Finally, note that in order for OutputParameter to be functional, the StoredProcedure.cs that the templates generate must be hand manipulated as they are not correctly identified as pointed out in Issue # 118. Additionally, the T4 template must be updated so that it inserts the following before the
return sp;line:sp.Command.AddReturnParameter();-Tim