Fetching Data from the Cursor

Once the cursor is open, you have lots of options. The data can be fetched once or any number of times and in any order (since the code created a scrollable cursor). Yes, as in ADO classic cursors, a "current position" must be maintained. But this time, it has to be handled in your codeADO.NET won't help here. You use the TSQL FETCH operator to retrieve a specific row (and only one row at a time) from the server-side cursor. The FETCH operator can be set to fetch the FIRST, LAST, NEXT, or PRIOR rows based on the current row pointer simply by using the appropriate operand. You can also fetch a specific row (the "nth" row) using the ABSOLUTE operand; or, if you want to fetch a row that's positioned in the cursor a specific number of rows forward or back, you can use the FETCH RELATIVE operand.

Let's go over these techniques and how they're implemented in code (as shown in Figure IV.2):

• To fetch the "first" or "next" row: When the cursor is first opened, the "current row" pointer is positioned before the first row (just like in ADOc), so either FETCH FIRST or FETCH NEXT gets the first (or next) row as defined by the declared cursor SELECT statement. In this case, simply set the CommandText with the appropriate FETCH TSQL operator and execute it. Note that the code returns a second resultset that contains the @@FETCH_STATUS global variable. This tells the application if the code has fetched past the last row or before the first row, or if something else went wrong. If you position past either end of the cursor, @@FETCH_STATUS returns 1 and there is no "current" row.

• To fetch the previous row: You can use FETCH PRIOR to step backward through the rows until @@FETCHSTATUS returns 1, which indicates that you've stepped over the "BOF" line.

• To fetch a specific (absolute) row: Use FETCH ABSOLUTE with a specific row number to fetch. Note that cursor rows begin with 1 and end in @@CURSOR_ROWS.

Figure IV.2. Using the T-SQL FETCH operator to reposition the current-row pointer.

Fetching the Entire Cursor

The T-SQL ANSI cursor implementation does not support the ability to fetch all of the rows in the cursor at once. This makes sense because once you fetch the rows into the client, they start getting stale the instant they arrive. However, I think fetching the entire cursor might be useful when you want to display the members of the cursor and let the user choose one of them to update (yes, it's possible to update through the cursor).

While it's possible to loop through multiple FETCH NEXT calls to get each row one at a time, it's far faster to get the rows in blocks. This means that instead of one round-trip for each of 100 cursor rows, the code executes only ten round-tripsfetching 10 rows at a timeassuming it uses a batch size of ten rows. Behind the scenes, the example code sets the number of FETCH operations to the number of expected rows in the cursor, but not greater than about 150 or so. Yes, you can get the number of rows in the cursor as soon as SQL Server completes membership population. Remember the SELECT @@CURSOR_ROWS T-SQL command?

To carry off this batch fetch approach, the example builds a long string to contain a set of FETCH NEXT operators, as shown in Figure IV.3. This is done using the StringBuilder class to reduce string handling overhead. In this case, sbFetchNext is declared as a new StringBuilder and intBatchSize is declared as 100. Remember, this routine simply calls FETCH NEXT N times so it starts from the current row. To have the routine fetch rows from the top, simply execute a FETCH FIRST before calling the FetchAll routine. Yes, if there are less than cursor rows MOD N, some of the FETCH NEXT commands will return empty rows. The example code is prepared for that.

Figure IV.3. Create the bulk fetch string.

Executing this batch of FETCH operators causes T-SQL Server to return N+1 resultsetsone for each row in the batch and one to contain the @@FETCH_STATUS. I used a few new ADO.NET 2.0 methods and techniques to construct the output DataTable containing the rows returned from the FETCH NEXT statements, as shown in Figure IV.4. This routine has been completely re-engineered since first publishedall because of the new ADO.NET features that make it far, far faster than earlier versions.

Figure IV.4. Fetching the entire cursor from the current position.