How can we get the @RETURN_VALUE from DB rather that rowsAffected from the DAAB?

Topics: Data Access Application Block
Jan 18, 2007 at 9:20 PM
We are using the DAAB 2.0 and we just discovered that the DAAB when using the ExecuteNonQuery() method returns the rows affected. No rows affected seems to me -1 in the DAAB.

We want to use the result returned in a if statement to determine success or failure of the stored procedure.

We were expecting the @RETURN_VALUE in our case 0 for success -1 for failure from our database.

How can we get the return value form the db (SQL Server 2005) rather than the rows affected from the DAAB returned???

Cheers, Newbie
Jan 18, 2007 at 10:55 PM
Try adding this at the top of your stored procedure:


Also see this thread for additional discussion:
Jan 19, 2007 at 6:06 PM
Thanks to your post I was able to make it work.

public static bool Delete(AvatarImageInfo avatarImage)
string sqlCommand = "syl_AvatarImageDelete";

//Name of Stored Procedure.
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

int avatarImageID = avatarImage.AvatarImageID;

if (avatarImageID > 0)
//Create a parameter.
db.AddParameter(dbCommand, "RETURN_VALUE", DbType.Int32, ParameterDirection.ReturnValue, null, DataRowVersion.Default, null);
db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);

//Invoke a SQL command and return true if deletion is successful.
//return (db.ExecuteNonQuery(dbCommand) == 0) ? true : false;

int returnValue = (int)db.GetParameterValue(dbCommand, "RETURN_VALUE");
if (returnValue == 0)
return true;
return false;

return false;
Jan 19, 2007 at 7:35 PM

I believe in your case, you could also use the ExecuteScalar method which returns the first column of the first row.

/* This comes directly out of the documentation */
Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "GetProductName";
int productId = 7;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, productId);

string productName = (string)db.ExecuteScalar(dbCommand);

Your query could then look something like this
if (select categoryid from categories where categoryname='Beverages')=1
select 0
select -1

Best regards,