Discovered InputOutput Parameters Do Not Return Values?

Topics: Data Access Application Block
Jun 8, 2009 at 2:13 PM

I am finding when using ExecuteNonQuery, discovered InputOutput parameter values are not being returned.   I am able to get a return value if I get the command parameter and specifically set it to an output parameter.  Is this the expected behavior and if not, what am I doing wrong?

Thank you,
Rich

My simple procedure is:

CREATE PROCEDURE [dbo].[BOInsertOrUpdate]
    @Id int = null OUTPUT,
    @Value nvarchar(50)
AS
    IF @Id IS NULL or @Id = 0
    BEGIN
        INSERT BO (Value) VALUES (@Value);
        SET @Id = SCOPE_IDENTITY()
    END       
    ELSE
    BEGIN
        UPDATE BO SET Value=@Value
        WHERE Id=@Id
    END
RETURN 0;

My function is:

    Private Sub InsertOrUpdate(ByVal parameters() As Object)
        Dim db As Database = DatabaseFactory.CreateDatabase()      
        db.ExecuteNonQuery("dbo.BOInsertOrUpdate", parameters)
        Me.ID = CInt(parameters(0))
    End Sub

Parameters(0) always comes back as null.

I can use the follwing code to get the return parameter:

    Private Sub InsertOrUpdate(ByVal parameters() As Object)
        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim command As DbCommand = db.GetStoredProcCommand("dbo.BOInsertOrUpdate", parameters)
        With command.Parameters("Id")
            .Direction = ParameterDirection.Output
            .Value = parameters(0)
        End With
        command.Parameters("Value").Value = parameters(1)       
        db.ExecuteNonQuery(command)
        Me.ID = command.Parameters("Id").Value
    End Sub

 

 

Jun 9, 2009 at 2:27 AM

It is the expected behavior.   The value of the out parameters will be in the .Parameters of the command object created from the first parameter you passed in.  Below is the actual ExecuteNonQuery code.

 

<font size="2">

 

</font>

 

<font size="2" color="#0000ff">

public

</font>

virtual int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)

{<font size="2">

 

</font>

        using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameterValues))

       {

<font size="2">

 

</font>

             return ExecuteNonQuery(command);

       }

}

The updated value of the out parameters will be in the command variable, in its Parameters property.

 

Sarah Urmeneta
Global Technology & Solutions
Avande, Inc.
entlib.support@avanade.com