Retrieving The Gazoutas: Understanding SQL Server Return Codes and Output Parameters
William Vaughnbeta V Corporation
April 2003
Applies TO: Microsoft ADO.NET Microsoft SQL Server?
Summary: discusses how to Capture, Interrupt, And Handle Resultsets and Rowsets, As Well As The Extra Information That The Return When Executing A Microsoft SQL Server Query. (7 Printed
Download The Gazoutassetup.msi Sample File.
Contents
How Are Queries Processed? Capturing the GazoutasHandling More Complex QueriesManaging Return Value and OUTPUT ParametersUsing the DataAdapter Fill Method to Process the RETURN Value and OUTPUT ParametersUsing a DataReader to Process the RETURN Value and OUTPUT ParametersConclusion
When executing queries or calling stored procedures, it's important to know how and when to fetch the other stuff returned by Microsoft SQL Server besides the rowset One question keeps popping up on the newsgroups:. "How can I tell how many rows are going to be returned by my query? "This is like asking farmer Dan exactly how many eggs his chicken laid overnight-he will not know until he goes out to the coop and collects them. However, farmer Dan does have a rough idea about how many eggs were laid. He knows that Sally is doing pretty well and should lay two and Betty is getting old and might need to be this Sunday's dinner, and so on. In this same vein, SQL Server will not know how many rows your query is Going to Return Until It Collects All of the Rows, But you have a pretty good idea how much rows your query will return.
How are queries processed?
Folks seem to think that SQL Server completes your query and packages up the rows like so many eggs in a carton before sending them down to your client application. 'Taint so. SQL Server starts sending rows to your client (assuming your code is asking for AS Soon as The First Step of The Query Plan. A Few Thousand Kb Worth of Row Data IS Buffered Up in Memory and Waits Until You ask for more. if're using the ado.net DataReader, that means using the Read method to fetch another row. The Fill method does this for you, asking for rows as fast as SQL Server can send them down. One of the last things to get sent is the rowcount-as if you didn 't already know. By this time ADO already knows, but it might be too late to do anything about it if you're trying to build an array big enough to hold the expected rowset before the first row arrives.Counting Eggs and Rows
One approach is to have SQL Server return a count of the rows using a COUNT * query. However, this is like asking farmer Dan to go out to the coop and count the eggs before actually collecting any, and since he has about 60 chickens, there's a good chance that one or more of the hens will lay another egg or break one after he counts but before he begins to collect. So using COUNT * without a repeatable-read transaction might yield only an approximation. Not only that, the COUNT * Approach Requires SQL Server To Repeat Much of The Processing of The Rowset - Returning Query and The Construction of a Second RESULTSET, BOTH OF WHICH RESULT IN A LOSS IN Performance.
I think the best way to deal with this situation is through a mix of common sense and experience. That is, if you can not limit the number of rowset members with a TOP expression or WHERE clause (you should), you can make an intelligent guess at how many rows are expected based on past experience. you can either construct an array based on that estimate (and stretch the array when it fills), or better yet, in .NET Framework CLR languages, you can use an "ArrayList . "The capacity of an ArrayList is the number of elements the list can hold. As elements are added to an ArrayList, the capacity is automatically increased as required through reallocation. The capacity can be decreased by calling TrimToSize or by setting the Capacity property explicitly .Capturing the gazoutas
But I digress. The point of this article is to discuss how to capture the extra information that SQL Server returns when you execute a query. In this context, you might try to return the COUNT * value or @@ RowCount in a stored procedure RETURN value, but remember SQL Server will not know the answer until all of the rows are processed and sent to the client. This means the RETURN value is sent as one of the last items returned to the client at the end of the resultset, which is far too late to pre-configure an array. This also means that you'll have to process all of the rowsets returned by the stored procedure before any of the non-rowset gazoutas are available. For those of you that are not familiar With the Term, A Gazouta Is A Term That I Use to Refer to anything That Gets Sent Out of A Stored Procedure. This Includes The Return Value and Output Parameters, As Well As the Rowset.
Processing resultsets
Whenever you execute a stored procedure (and some ad hoc queries), SQL Server generates one or more resultsets. Do not get this confused with an RDO Resultset object-it's related but not the same. Each resultset might contain a rowset and perhaps a packet that contains the @@ ROWCOUNT (rows affected) value. When you execute a SELECT statement or an action query (INSERT, UPDATE, DELETE), SQL Server sets the @@ ROWCOUNT to indicate the number of rows affected and returns this value in the last packets sent back to the client. That is, unless you put SET NOCOUNT ON in your stored procedure, in which case this packet is not sent. This option makes sense if you do not care about the number of rows changed by the Latest Action Query. IT Helps Performance Too AS ADO Does Not Have to Process The Extra (EMPTY) ResultSet.Select Query ResultSets
So, let's take a closer look at the resultsets that get sent back from a query. A simple SELECT resultset contains both a rowset and a Rowcount value. Your code has to deal with the single rowset and capture the Rowcount if needed. Remember, you .
Action Query Resultsets
When you execute an action query (such as an INSERT statement), the resultset contains no rowset at all-just the Rowcount value. If you do not care about resultsets from action queries, you can eliminate them by including SET NOCOUNT ON in your Stored Procedures, But if ADO.NET IS Depending On this Count, (as When You Use the Dataadapter Update Method), Your Action Command Objects Must Return this value to profrly management control.
Handling More Complex Queries
In more complex queries, several resultsets can be generated. Each SELECT statement and each action command generates its own resultset. If you use the DataAdapter Fill method to execute the query and process the resultsets, you do not have anything to worry about. ADO .NET handles all of the details of managing these resultsets as each resultset containing a rowset is used to construct a DataTable in the DataSet. The Rowcount is ignored for the most part, but for rowset-returning queries it can be accessed by checking the DataTable Rows Count Property As Follows: IntrowsReturned = myds.tables (0). ROWS.COUNT
Using a DataReader to Process Results
Handling multiple resultsets with a DataReader is not that tough, however there are a number of differences if you're used to doing this in ADO classic. To illustrate, let's start with a made-up query that returns six resultsets-some with rowsets and Some without. The code Uses The Microsoft Visual Basic? .NET STRINGBUILDER CLAERIES INTO A SINGLE STRING FOR BETTER Performance.
Listing 1. Constructing The Batch Query
'Six Result Sets - With Four Rowsets,
'One Empty Rowset, Two Action Queries
DIM SQL AS STRING () = {"SELECT TOP 5 ContactName, City", _
"From customs where fax is null", _
"SELECT TOP 5 Orderid, Unitprice", _
"From [Order Details] Order by Unitprice Desc", _
"SELECT TOP 5 Orderid, Unitprice", _
"From [Order Details] where 1 = 0", _
"Update Region Set RegionId = -1", _
"Where regiondescription = 'unknown'", _
"Delete Region Where Region Id = -1", _
"SELECT TOP 5 lastname, firstname, region from Employees"} DIM Strth As String, Sbsql As new system.text.stringbuilder ()
For Each Strt in SQL
SBSQL.Append (STRT)
NEXT
This SQL batch could have been a part of a stored procedure or simply executed stand-alone. No, not all query engines accept multiple-statement batches (Access / JET, for example), but it is allowed in SQL Server. As a matter of fact, submitting this multi-query batch eliminates five round-trips to the server to execute the individual queries. It also means that the resultsets are all threaded together in line to be processed one at a time. Next, we have to execute the Query and prepare to process Each resultset in turn.
What happened to the action commands?
An interesting point to note is that both the DataAdapter Fill method and the DataReader ExecuteReader methods appear to skip over the action commands. No, they're actually executed and their resultsets are returned, it's just that ADO.NET does not worry you with processing the resultsets. As these action queries are sensed, the DataReader.RecordsAffected value is reset. That is, when the query begins the RecordsAffected value returns -1 if a SELECT query is executed. Once an action query resultset is sensed, RecordsAffected is set to N to Reflect The Last Rowcount Value Found in The ResultSet. Because The Action Queries Are Handled for You, All You Have To Worry About As Far As Your Code in Concerned Is What To Do with The Rowset.
Listing 2. Executing The Batch and Parsing The Results
DIM CMD AS New Sqlcommand (Sbsql.tostring, CN)
DIM DR AS SQLDATAREADER
cn.open ()
DR = cmd.executeReader (Commandbehavior.CloseConnection)
Bolrows = Dr. Read 'Read First Row of RowSetdo
IF Bolrows The ProcessRows (DR) 'Dump DataReader Rows To Listbox
Bolmoreresults = Dr.NextResult
IF Bolmoreresults Then
Bolrows = Dr.Read ()
END IF
Loop While BolmoreResults
.
Listing 3. Processing The Rowset Returned by The DataReader
Sub ProcessRows (ByVal Dr As SqldataReader)
Try
Listbox1.items.add ("-----> Recordsaffected:" _
& Dr.Recordsaffected.toString)
IF Dr.Recordsaffected> = 0 THEN
'Value Is Batch-Wide Total Rows Affected,
'But it's -1 if bathch is Running a select.
'Changes State from -1 to N Once First Action Query IS Executed
Listbox1.items.add ("---> action query execute))
'But the next rowset is queued up
END IF
Do 'Process Result Set
Listbox1.items.add (Dr.GetValue (0) .tostring & "- _ _ _ _ _ _ _ _
"& Dr.getValue (1) .tostring)
Loop While (Dr.Read)
Catch exception
Msgbox ("Problem Reading Rows ..." & ex.totring)
END TRY
End Sub
Managing Return Value and Output Parameters
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 from the resultset. Again, and I can not 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.Handling Unwanted DataReader Rows
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. Do not be tempted to simply close the DataReader without using the Cancel method on ..
Using the dataadapter fill method to process the return value and output parameters
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 boolean
CN = New SqlConnection ("Server = DEMOSERVER;" & _ "Database = Pubs; Integrated Security = SSPI")
CreateTestProcedure () 'Create Test SP in Pubs DB
DA = New SqldataAdapter ("OutputTitleinfobytid", CN)
Da.selectCommand.commandtype = commandtype.storedProcedure
With 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.toToString)
Debug.writeline ("@ Advance:" & _.Item ("@ Advance"). Value.toString
Using a datareader to process the return value and output parameters
This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery if you do not 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.toToString)
End with
Conclusion
This fairly brief article walked you through the details of handling resultsets and the rowsets and other stuff they contain. You should not have trouble getting to the gazoutas after this-at least I hope not.About the Author
William (Bill) Vaughn is president of Beta V Corporation based in Redmond, Washington. He provides training, mentoring, and consulting services to clients around the globe, specializing in Visual Basic and SQL Server data access architecture and best practices. William's latest books are ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers-2nd Edition and the C # version ADO.NET Examples and Best Practices for C # Programmers. Both are available from Apress. William is also the author of many articles and training courses and is A Top-Rated Speaker At Computer Conferences Worldwide. You Can Reach Him at Billva@nwlink.com.
