Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series) - page 192

Summary

This appendix focused on two essential tools that developers use to monitor SQL Server. The SQL Profiler has helped me for over a decade to understand how the data access interfaces interact with SQL Server. As each new interface arrived, I was able to tell exactly what was working and what was not by monitoring the SQL Profiler traces.

Performance counters have been around for some time as well. Now that Microsoft has the ADO.NET connection pool counters working, these can play a pivotal role in ensuring that your application is not leaking connections.

Appendix IV. Creating and Managing Server-Side Cursors

They told you it couldn't be done. They were wrong. Yes, you can create and manage your own server-side, fully scrollable and updatable cursors on T-SQL Serverand you can do it all with ADO.NET. This appendix is excerpted from a popular magazine appendix that focused on missing functionality in the then-current version of ADO.NET. Since then, the problem of missing server-side cursors has not been fixedand it probably won't be. For a litany of reasons, Microsoft chose not to implement any server-side cursor functionality in any version of ADO.NET. This appendix discusses how you can work around this limitation to create and manage your own server-side cursors by using T-SQL ANSI T-SQL statements. Yes, this a lot more trouble than using ADO classic's (default) server-side cursor, but at least it's one viable (albeit a bit unusual) way to solve this problem.

Why Are Server-side Cursors Important?

For over a decade , T-SQL Server and other DBMS developers have been using server-side cursors to access their databases and scroll through updateable rowsets. A server-side cursor in its purest form is simply a block of pointers to live data rows as defined by a SELECT statement. The cursor can include all rows from a specific table or just a selected few. It can address rows generated from a JOIN, view, or stored procedure. Again, when you create a server-side cursor, you aren't asking for data; you're asking for pointers to the data. This means that the data remains where it should remainon the server, where other clients can see and change the values. When you fetch rows from a server-side cursor, you are fetching the "live" dataas it is on the server that instantnot some cached version that's been sitting around in memory for a few seconds to a few days.

Server-side cursors were implemented in the earliest versions of RDO and as the default cursor in ADO classic. This "connected" data access paradigm assumes that the database connection remains in place while the application runs a query and builds a server-side structure that permits one to browse up and back through a set of rows that can be retrieved and updated as needed.

Server-side cursors are especially useful when working with highly interactive applicationsespecially when the application cannot work with disconnected (static) data. This type of application needs a mechanism to work with a single row or a small set of rows at once. Server-side cursors are designed to meet this need. As illustrated by the examples in this appendix, I'll show you how to:

Create a cursor based on a focused SELECT statement
Position a cursor to any designated row
Change the data in the currently selected cursor row
Adapt the cursor so other users' changes are visible (or not)

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.

Buy on amazon.com >>

Vaughn W.R., Blackburn P.

<< Previous page

Table of contents

Next page >>