How does DAAB handle stored procedure RETURN values?

Question

User-699729574 posted

Here's an example proc:

CREATE PROC MyProc

AS

IF 1=1

   RETURN

ELSE

   RETURN (-100)

GO

How can I use the DAAB to test the return value? I can find out how to get output params just fine but that's not what I want.

I've Googled for this and one common thing I find is that, whenever someone asks a similar question, posters often try to steer the original poster into using output parameters or other techniques. I don't want to use any other technique; I need to be able to test the return value and make decisions based off of it. I've been trying to port my code over to using the DAAB for my websites but this has stumped me.
 

Answer 1

User617064624 posted

Not sure if you have gotten an answer but I think I just found the solution. I duplicated your search for an answer

and found all the same discussions you mention and I also did not want to use output parameters.

 

My solution:

//******************************************************************************************************************** 

Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "update_proc_name";
DBCommandWrapper dbCommandWrapper= db.GetStoredProcCommandWrapper(sqlCommand);

dbCommandWrapper.AddInParameter("@Id", DbType.Int32, Id);

//Add an additional parameter, not an AddInParameter or AddOutParameter but an AddParameter for the return value
dbCommandWrapper.AddParameter("@ReturnValue", DbType.Int32, System.Data.ParameterDirection.ReturnValue, string.Empty, System.Data.DataRowVersion.Default, null);

db.ExecuteNonQuery(dbCommandWrapper);

//Get return value
int retVal = (int) dbCommandWrapper.GetParameterValue("@ReturnValue");

 //********************************************************************************************************************

 

The parameters for the AddParameter method do not seem to fit nicely for a 'return value' so I picked as generically as I could for the last three params. The key param has to be - <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataParameterDirectionClassTopic">ParameterDirection,</MSHelp:link> which is set to System.Data.ParameterDirection.ReturnValue . I tried this with various return values from the proc and it always worked so I hope this helps you.

FROM DAAB help:

public override <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemVoidClassTopic">void</MSHelp:link> AddParameter(    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic">string</MSHelp:link> name,    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDbTypeClassTopic">DbType</MSHelp:link> dbType,    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataParameterDirectionClassTopic">ParameterDirection</MSHelp:link> direction,    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic">string</MSHelp:link> sourceColumn,    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDataRowVersionClassTopic">DataRowVersion</MSHelp:link> sourceVersion,    <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemObjectClassTopic">object</MSHelp:link> value ); <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemVoidClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDbTypeClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataParameterDirectionClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDataRowVersionClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemObjectClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDbTypeClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataParameterDirectionClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemStringClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDataRowVersionClassTopic"></MSHelp:link><MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemObjectClassTopic"></MSHelp:link>

Parameters

name

The name of the parameter.

dbType

One of the <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDbTypeClassTopic">DbType</MSHelp:link> values.

direction

One of the <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataParameterDirectionClassTopic">ParameterDirection</MSHelp:link> values.

sourceColumn

The name of the source column mapped to the DataSet and used for loading or returning the value.

sourceVersion

One of the <MSHelp:link tabindex="0" namespace="ms-help://MS.NETFrameworkSDKv1.1" indexmoniker="!DefaultAssociativeIndex" keywords="frlrfSystemDataDataRowVersionClassTopic">DataRowVersion</MSHelp:link> values.

value

The value of the parameter.