How Does ADO.NET Implement Cursors?

ADO.NET is fully capable of running queries, but the "cursor" it knows how to create is not the same as DAO, RDO, or ADO "classic" (ADOc) developers might be used to. In the strictest sense, a cursor is a device used to browse through the rows that result from a query. Some cursors are scrollable, so you can position a "current-row" pointer at arbitrary points in the rowset and return a specific row, while others are "forward-only". I expect most of you are interested in fully scrollable cursors.

Yes, if you squint a DataTable might be thought of as a cursor, in that it exposes a Rows collection that can be browsed (or scrolled) in any order. But a DataTable is static client-side datait's really no different than simply taking a DB-Library data stream and dumping it into a local array. While server-side cursors can also be static data (that does not change as the database is updated), server-side cursors can also be a fixed set of keys (a keyset cursor) or a dynamically changing set of keys (a dynamic cursor) that points to live data. That's the critical difference. Server-side cursors leave the data on the server (or they can), returning only "pointers" in the form of keys that point back to live data. As other users change the data, the cursor member keys still point to fresh data.

The ADO.NET architects and Microsoft product managers think that static, client-side DataTable and TableAdapter objects are enough for many customers. They're rightup to a point. I think that quite a few applications designed to use server-side cursors could have been written using client-side static cursors. However, when they are, developers are sometimes forced to make extra round-trips to the server to detect changes in the data or membership. Another approach Microsoft is promoting is T-SQL Server Notification Services, where you can set up call-backs to your application when specifically (and carefully) selected data changes. However, this approach is not for the faint of heart or simple, fast, mainstream applications.

I think there are plenty of viable situations where a server-side cursor makes sense. I keep hearing that Microsoft was concerned that developers were "misusing" server-side cursors. That's understandable, as the default cursor library (CursorType) in ADO implements a server-side cursor (adUseServer)so it's their own fault. In addition, too many examples illustrated fundamental operations using (often expensive) server-side cursors. I think that developers are smart enough to make up their own minds, and server-side cursors can increase developer and code performance if used wisely.

This approach to data access is not particularly interesting in stateless Web-based applications. It makes the most sense in connected Windows Forms applications.