getting table adapter return values from stored procedures?

  • Thread starter Thread starter Dean Slindee
  • Start date Start date
D

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
 
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.
 
Back
Top