Suggestion for future version of DAAB

Topics: Data Access Application Block
Jan 11, 2007 at 6:56 PM
Hi there,

I think it could be easier to add a return value parameter to a stored proc command, something along the lines of:

Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand(name, args);

int returnCode = db.GetReturnValue(command);

Currently the only way you can add a return value parameter is using the AddParameter method of Database, which is a bit smelly as it takes so many parameters. Return value parameters always have the same signature, given that the parameter name is not important, so there's not really any need to use AddParameter.

I'm adding this myself to the version of the code we use, but it would be great if this could be added to the library proper.

Jan 11, 2007 at 7:55 PM

Also, I'm having problems getting a return parameter to work in the first place - the return parameter is not getting bound to the actual return statement in a stored procedure.

Has anyone else encountered this? This is the code I am using:

Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand("TestReturnValue", 43);

db.AddParameter(command, "ReturnCode", DbType.Int32, ParameterDirection.ReturnValue, null, DataRowVersion.Default, 0);

int returnCode = (int) db.GetParameterValue("ReturnCode");

And the proc (simplified for demonstration purposes):

@Input int = NULL

The return value parameter is always the same as the initial value. Rather frustrating as the design of the actual proc requires me to use return codes.

If anyone knows of a fix for this I would love to hear about it.

Thanks again!
Jan 11, 2007 at 7:56 PM
Forgot to mention - I'm using SQL Server 2005.
Jan 12, 2007 at 4:44 AM
This code works for me, using your stored procedure prototype from above:

public static void TestReturnValue(int Input)
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "TestReturnValue";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

// Add procedure parameters
int result;
db.AddParameter(dbCommand, "RETURN_VALUE", DbType.Int32, ParameterDirection.ReturnValue, null, DataRowVersion.Default, null);
db.AddInParameter(dbCommand, "Input", DbType.Int32, Input);


// Get return value
result = (int)db.GetParameterValue(dbCommand, "RETURN_VALUE");
Jan 12, 2007 at 3:13 PM
Your code works for me too - but it's not the same code as what I originally posted.

The difference is that my code uses parameter discovery, which seems to be causing the problem:

DbCommand command = db.GetStoredProcCommand("TestReturnValue", 43);

uses discovery, whereas

DbCommand command = db.GetStoredProcCommand("TestReturnValue");

in your code doesn't use discovery - you are adding the input parameter manually.

It's definitely a solution to my immediate problem though - I'll just use your code. Thanks!

I'll dig some more into the parameters collection in both examples to see what the differences are, which may indicate what the underlying problem is. It should be possible to use parameter discovery and return parameters without hassle.
Jan 12, 2007 at 3:30 PM
Duh - discovery automatically adds the return parameter as well. That's why my additional parameter is never getting bound.

The return parameter name from discovery is always @RETURN_VALUE. This information is kinda hidden though - I still think my opening post is relevant.
Jan 12, 2007 at 4:03 PM
Well, I understand your request, it is valid. I guess I never noticed because I use code generation to create my proc calls, so I never type it out by hand. :)

Glad you found the solution!
Jan 20, 2007 at 9:44 AM

I'm also in favour of including the AddReturnParameter in the Database class. Also something like:

GetParameterValue<T>(DbCommand cmd, string parameterName, T defaultValue)

would be a great addition for output parameters. I have coded both myself but it is quite inconvenient to have to always remember to add those with each release of EntLib. This is also the thing that stops me from testing CTP drops against my production code.

Those feature requests are really basic so I don't see any reason not to include them in this release. Any Idea if those could make it into the final 2007 release?