How Can ADO.NET Create a Server-Side Cursor?

In my ADO.NET book, I state (repeatedly) that ADO.NET cannot create a server-side cursor. Strictly speaking, this is trueon its own, it can't. However, it's true only in the sense that it can't create and manage a server-side cursor on its own in the same way that DAO and ADOc can. As I'll show you in this appendix, you can create server-side cursors using some fairly easy ADO.NET methods.

It's not a good idea to use the following techniques when working with ADOc. That's because the server-side cursor mechanisms within ADOc can conflict with any cursors you create on your own using T-SQL.

This technique is really pretty simpleit's based on using T-SQL to create ANSI cursors. As illustrated in the example code, I use the ADO.NET Command object to execute specific T-SQL scripts (two or more tightly coupled T-SQL statements), including the DECLARE CURSOR statement and the FETCH and DEALLOCATE ANSI cursor commands. Let's step through the process of creating and managing a cursor.

Opening, Closing, and Re-opening the Connection

Unlike other ADO.NET examples in this book and elsewhere, when building a server-side cursor, the code needs to open a connection to the T-SQL Server instance and keep it open. That's because the cursor is built and maintained by the server and owned by the connection. Once the connection closes, the cursor is (usually) flushed^[2] (at least when the connection pooler closes or resets the connection).

^[2] It's possible to create a database-persistent cursor using the GLOBAL option.

As discussed in Chapter 9, "Getting Connected," consider that a Windows Forms application creates a new connection pool for each unique connection string. This means that if you create a cursor, it won't be destroyed (at least, not immediately) when you close the connectionassuming that you're using connection pooling (which is on by default). However, if the connection reset option is enabled (it is by default), the cursor is cleared when the connection is reused again so the new connection owner cannot inherit the cursor. If you turn off the reset connection option, the cursor should remain viable for reuse by your applicationit will still be there when you reopen the connection. However, if the connection pool times out the connection, which forces the connection closed, your cursor is lost. Of course, in a Windows Forms application, it's perfectly reasonable to disable connection pooling. In this case, when the connection is closed, the cursor is lost. No, the data addressed by the cursor is undisturbed when the cursor is closed.

Creating the Cursor

The next step is to create the cursor based on a T-SQL statement. This block of code uses the T-SQL statement passed from the user in a TextBox control. (No, it's not a good idea to just accept anything from a user, but this is a demonstration and test program, so I'm pretty generous with what I permit in the T-SQL.) To be safe(er), I call the ValidateSQL routine to do some rough tests to make sure I'm not too sleepy and enter something destructive.

The T-SQL used to create the ANSI server-side cursor can be broken down into three parts (as shown in Figure IV.1):

1. The DECLARE cursor statement, which names the cursor and sets the options to use. In this case, the code asks for a simple scrollable cursor (as opposed to a forward-only cursor) and names it myCursor.

2. The T-SQL SELECT statement, which is executed to build the cursor rowset, follows the CURSOR FOR argument. The SELECT statement should focus the cursor on a few manageable rows in the databasenot an entire database tableif you expect to scale the application. Sure, the SELECT can contain a JOIN or a VIEW, as long as it's a standard (simple) SELECT statement. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed.

3. A SELECT query to return the number of rows in the cursor. If you select a large number of rows, this query will probably return 1 until T-SQL Server has completed server-side rowset population (until SQL Server has found all of the member rows and added them to the cursor).

Figure IV.1. Constructing a server-side cursor.

Next, the code executes the T-SQL using the ADO.NET Command object's ExecuteScalar method. This returns the @@CURSOR_ROWS global variable, so I know how many rows are in the cursor. Remember not to close the connection after having created the cursorif you do, the cursor will be dropped by T-SQL Server.