You were on the right track, but it sounds like a command object is warranted
(whatever that may be in ADO.NET). I swear there's been so many sql server
api's over the years they begin to blur in the head. You know I guess this problem
wasn't an ADO problem it was more of db-lib problem. But it looks like its
reincarnated back with the thinness of ADO.NET. This is an overall good thing,
but leads to your point of confusion. Here's the snippet from the this msdn article related to your issue.
Peter Evans (__PETER Peter_)
William Vaughn
Beta V Corporation
April 2003
Applies to:
Microsoft ADO.NET
Microsoft SQL Server™
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp
If you want to capture the integer passed back by the RETURN statement in TSQL
or your stored procedure's OUTPUT parameters, you're going to have
to take another few minutes to setup a Command object to capture these values f
rom the resultset. Again, and I can't emphasis this enough, these values are not made available
until you've processed all rowsets in each resultset (if there are any). Yes, a stored procedure
can return several resultsets, but the RETURN value and OUTPUT parameters are not populated
until all queries have been completed and all rowsets transmitted back to the client.
Okay, so you asked for too many rows in one of your queries and you want to step over
this rowset to get to your OUTPUT parameters when working with a DataReader.
Don't be tempted to simply close the DataReader without using the Cancel method
on the Command first. That's because ADO.NET completes the processing of any remaining rows
instead of telling SQL Server to cancel all further query processing for this Command.
The following code constructs a DataAdapter with its associated SelectCommand to execute
a multiple-resultset stored procedure that returns several OUTPUT parameters.
Note that the parameters must be named correctly when working with SQL Server.
That is they match the names used in the stored procedure. Order is not important,
but you must include any parameter that does not have a default value set in the stored procedure.
Listing 4. Constructing a Command and using Fill to execute the query
Dim bolEOF As Booleancn = New SqlConnection("server=demoserver;" & _ "database=Pubs;integrated security=sspi")CreateTestProcedure() ' Create test SP in Pubs DBda = New SqlDataAdapter("OutputTitleInfoByTID", cn) da.SelectCommand.CommandType = CommandType.StoredProcedureWith da.SelectCommand.Parameters .Add("@TID", SqlDbType.VarChar, 6).Value = "BU1032" .Add("@Title", SqlDbType.VarChar, 80).Direction _ = ParameterDirection.Output .Add("@Type", SqlDbType.VarChar, 40).Direction _ = ParameterDirection.Output .Add("@Price", SqlDbType.Money).Direction _ = ParameterDirection.Output .Add("@Advance", SqlDbType.Money).Direction _ = ParameterDirection.Output .Add("@ReturnValue", SqlDbType.Int).Direction _ = ParameterDirection.ReturnValue ds = New DataSet da.Fill(ds)
Once the Fill method executes the query and processes the rowsets, the RETURN value and OUTPUT parameters
are available through the SelectCommand.Parameters collection either by ordinal position or by name as shown in listing 5.
Listing 5. Displaying the values of the Return Value and OUTPUT parameters
Debug.WriteLine("@ReturnValue:" & _ .Item("@ReturnValue").Value.ToString)Debug.WriteLine("@Title:" & _ .Item("@Title").Value.ToString)Debug.WriteLine("@Type:" & _ .Item("@Type").Value.ToString)Debug.WriteLine("@Advance:" & _ .Item("@Advance").Value.ToString)Debug.WriteLine("@Price:" & _ .Item("@Price").Value.ToString)Debug.WriteLine("@Advance:" & _ .Item("@Advance").Value.ToString)
This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery
if you don't have a rowset to process), but there are several other steps you'll need to take
to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting
to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and
a loop to process the rowsets, and then capture the Return value and OUTPUT parameters.
You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.
Listing 6. Displaying the values of the Return Value and OUTPUT parameters
With cmd.Parameters cn.Open() dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' Process rowset(s) bolEOF = dr.Read Do Do While bolEOF = True ' Process rows bolEOF = dr.Read() Loop Loop While dr.NextResult = True cmd.Cancel() dr.Close() Debug.WriteLine("@ReturnValue:" & _ .Item("@ReturnValue").Value.ToString) Debug.WriteLine("@Title:" & _ .Item("@Title").Value.ToString) Debug.WriteLine("@Type:" & _ .Item("@Type").Value.ToString) Debug.WriteLine("@Advance:" & _ .Item("@Advance").Value.ToString) Debug.WriteLine("@Price:" & _ .Item("@Price").Value.ToString)End With
This fairly brief article walked you through the details of handling resultsets and the rowsets and
other stuff they contain. You shouldn't have trouble getting to the gazoutas after this—at least I hope not.