If you've been trying to figure out why you cannot access the return value when using stored procedures in TableAdapter, here's the answer and the solution for you.

Assume I added a new query to my TableAdapter via Dataset Designer and named it ReturnSomething. For this query, I used a simple stored procedure that just returns an integer value. Without looking at the generated code, most people expect the return value of the generated query method to contain the return value of the stored procedure and try something like below:

   Dim returnValue As Integer
   returnValue = taQuery.ReturnSomething

However, above call results in either compile error or run-time error. The problem is that these query methods actually return something else depending on what option you chose for 'Choose the shape of date returned by the stored procedure' dialog when adding your query via TableAdapter Query Configuration Wizard.

  • Single value: Query method returns the value of the first column of the first row in the result set returned by SELECT statement in your stored procedure. If SELECT statement returns nothing or if there's no SELECT statement, then the return value is NULL.
  • No value: Query method returns the number of rows affected by the stored procedure.

Definitely not the return value from the stored procedure. If you look at the generated code, this all makes sense. Here's the example code for these methods.

''

'' Singe value    

''

Public Overloads Overridable Function ReturnSomethingSingleValue(ByVal MyParam As System.Nullable(Of Integer)) As Object

    ...

    ...

    Dim returnValue As Object

    Try

       returnValue = command.ExecuteScalar

   Finally

       If (previousConnectionState = System.Data.ConnectionState.Closed) Then

            command.Connection.Close

       End If

   End Try

   If ((returnValue Is Nothing)  _

      OrElse (returnValue.GetType Is GetType(System.DBNull))) Then

        Return Nothing

   Else

        Return CType(returnValue,Object)

   End If

End Function

       

''

'' No value

''

Public Overloads Overridable Function ReturnSomethingNoValue(ByVal MyParam As System.Nullable(Of Integer)) As Integer

    ...

    ...

    Dim returnValue As Integer

    Try

        returnValue = command.ExecuteNonQuery

    Finally

        If (previousConnectionState = System.Data.ConnectionState.Closed) Then

            command.Connection.Close

        End If

    End Try

    Return returnValue

End Function

Notice that for single value query, ExecuteScalar method is used and for no value query ExecuteNonQuery method is used. And both query methods return what corresponding execute methods return. ExecuteScalar method returns, as described above, the value of the first column of the first row in the result set and ExecuteNonQuery returns the number of rows affected by the command.

Ok. It's very comforting to know what these methods return and we now understand where errors come from. But where do we get actual return values from then? If you read through the generated code carefully, you will notice that TableAdapter actually has places for return values.

Private Sub InitCommandCollection()

    ...

    ...

    CType(Me._commandCollection(0),System.Data.SqlClient.SqlCommand).CommandText = "dbo.ReturnSomething"

    ...

    CType(Me._commandCollection(0),System.Data.SqlClient.SqlCommand).Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))

    ...

    ...

    CType(Me._commandCollection(1),System.Data.SqlClient.SqlCommand).CommandText = "dbo.ReturnSomething"

    ...

    CType(Me._commandCollection(1),System.Data.SqlClient.SqlCommand).Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))

    ...

End Sub

As you can see from above code sample, each SqlCommand object has ReturnValue parameter in its Parameters collection. So, it's just a matter of retrieving these values after you execute your query method. To demonstrate how this can be done, I wrote below code in partial class. Note that this is very simple code sample that has no error handling or validation. It just demonstrates the idea.

Namespace MyDatasetTableAdapters

    Partial Class QueriesTableAdapter

        Public Function GetReturnValue(ByVal commandIndex As Integer) As Object

            Return Me.CommandCollection(commandIndex).Parameters(0).Value

        End Function

    End Class

End Namespace

After you add above code to your partial class, you can retrieve return value of specific command by doing something like below.

Dim taQueries As MyDatasetTableAdapters.QueriesTableAdapter

Dim returnValue As Integer

Dim myInputParam As Integer

 

myInputParam = 999

taQueries = New MyDatasetTableAdapters.QueriesTableAdapter()

taQueries.ReturnSomethingSingleValue(myInputParam)

returnValue = taQueries.GetReturnValue(0)

Above code will execute the first query method in the commandCollection and retrieve the return value of the same command via GetReturnValue method added to the partial class. Notice that 0 is passed as the parameter value for GetReturnValue method to specify that we want to retrieve the return value for the first command in the collection. Yeah, it's ugly. But, again, you get the idea. :)

Typed Dataset simplifies your data access coding. But there are many things that you might not be able to do using generated Typed Dataset. If you are faced with that situation, look for ways to leverage partial classes to add/modify Typed Dataset behavior. That's exactly why we added partial class in Visual Studio 2005!

If you have better ways to handle return values, please let me know. See something wrong with above approach? Don't be afraid to send me hate email! :)

Technorati Tags: