locked
How does DAAB handle stored procedure RETURN values? RRS feed

  • 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.
     

    Thursday, September 21, 2006 7:16 AM

All replies

  • 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.

     

     

    Monday, December 4, 2006 7:43 PM