getting table adapter return values from stored procedures?

[Dean Slindee]

The question is simply stated on the last line of this post, reading it
first might save time.

I am exploring the possibility of using typed datasets and table adapters as
the basis for a data access layer. Using stored procedures as the table
adapter's communication vehicle to the database. I have gotten a demo
form/dal/sp/table working for dataset retrieval, single value retrieval, and
delete/insert/update operations. In the delete/insert/update area, I have a
serious reservation about going "production". It has to do with how to
reference the return value from the stored procedure, so I can use it to
generate a status message to the form's user.

From this site I picked up one way to get the return value from the stored
procedure:
blogs.msdn.com/vsdata/archive/2006/08/21/711310.aspx

A helper function is added to the generated dal code, like this:
Partial Public Class QueriesTableAdapter
Public Function GetReturnValue(ByVal commandIndex As Integer) As
Object
Return Me.CommandCollection(commandIndex).Parameters(0).Value
End Function
End Class

And gets the ReturnValue from the stored procedure, as found here in the
generated table adapter code:

Me._commandCollection(3) = New
Global.System.Data.SqlClient.SqlCommand
CType(Me._commandCollection(3),Global.System.Data.SqlClient.SqlCommand).Connection
= New
Global.System.Data.SqlClient.SqlConnection(Global.AdminSQLBind.My.MySettings.Default.AdminConnectionString)
CType(Me._commandCollection(3),Global.System.Data.SqlClient.SqlCommand).CommandText
= "dbo.UpdateAdminActivity"
CType(Me._commandCollection(3),Global.System.Data.SqlClient.SqlCommand).CommandType
= Global.System.Data.CommandType.StoredProcedure
CType(Me._commandCollection(3),Global.System.Data.SqlClient.SqlCommand).Parameters.Add(New
Global.System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
Global.System.Data.SqlDbType.Int, 4,
Global.System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing,
Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))

My problem is: I cannot imagine using the offset value "(3)" above to get to
the correct parameter (@RETURN_VALUE) value. Especially, since there could
be (n) parameters in the generated table adapter code.

Here is the code in the WinForm that calls the above table adapter/stored
procedure and gets the return value:
intReturn = ta.UpdateAdminActivity(ActivityID, _
Trim(txtActivityDescription.Text),
_
Trim(txtActivityName.Text), _
Trim(txtDirectIndirect.Text), _
Trim(cboInactive.Text), _
CType(Trim(txtInactiveDate.Text),
Global.System.Nullable(Of Date)), _
CType(Trim(txtEffectiveDate.Text),
Global.System.Nullable(Of Date)), _
Trim(txtComment.Text), _
Trim(txtCode.Text), _
AuditLog, _
guid, _
NewGuid)
intReturn = CInt(ta.GetReturnValue(3))

Question: Is there a "named value" syntax alternative to the "(3)" in the
statement above?

Thanks in advance,
Dean S

 

[Stephany Young]

I can't imagine you using an offset of 3 either.

The @RETURN_VALUE is declared first so it's offset will always be 0.

I think you are confusing the index of the @RETURN_VALUE parameter in a
given collection of parameters with the index of a given command in a
collection of commands.

The way that the GetReturnValue is written you MUST know (in advance) the
index of the command you are dealing with before you can call it.