ADO.NET from 50,000 Feet

Let's start with a high-level view of ADO.NET to get an overall idea of how it works and why it's needed. I've found that ADO.NET is simpler to understand (and explain) if it's broken down into two parts:

• The classes used to interact with the data source: These classes are implemented by provider-specific code designed to connect to the data source, collect and pass the queries to the SQL engine for processing, and return the resultsets, rowsets, and exceptions.

• The classes used to manage data in memory: These classes are populated by the provider-specific classes and basically implement simple in-memory data structures that support sorting, filtering, and finding, as well as the binding interfaces and classes used to connect related rowsets.

You might have heard that ADO.NET implements a disconnected in-memory "database." That's not really true. It's really a stretch to call the classes that ADO.NET exposes a "database" in the sense of a "real" database management system like SQL Server, SSEv, or other DBMS engines like Oracle or DB2. Data access interfaces like ADO.NET (and its predecessors) do not implement a JOIN or SELECT engine driven by SQL or even methods and properties. All data access interfaces simply connect to DBMS engines or other data sources that do the heavy lifting.

While ADO.NET was designed specifically to deal with disconnected data structures that ASP.NET applications require, these same ADO.NET classes can be used to manage "connected" data as wellat least, to an extent. No, ADO.NET does not know how to build or maintain any form of server-side cursoryou'll have to implement that yourself or solve your problem using a disconnected architecture. Perhaps, Appendix IV, "Creating and Managing Server-Side Cursors," will helpit contains a whitepaper I wrote a while back that shows you how it's done. Connected applications form the core of many sophisticated applications, and while ADO.NET is not as rich as ADO classic in its ability to support this type of application, many applications are still being built and converted to access data using connected strategies.

IMHO

I don't really expect Microsoft to ever implement a server-side cursor for ADO.NETthey've had over seven years to do it, and they have not made any forward progress toward that goal. They're too focused on re-creating ADO.NET again....

Working with Data Sources

As I said in earlier chapters, you don't need to access a DBMS to gather data and manage it with ADO.NET. Data can be extracted from anywhere it's storedfrom memory, to flat files, to sophisticated relational database management systems like SQL Server, to simple DBMS systems like SQL Server Everywhere. However, unless you're making up data out of thin air (as some politicians seem to do) in order to fetch your data, you'll probably want to use an existing .NET Data Provider (as I discuss later in this chapter) or create your own. While it's not hard to write and expose a custom data source, you won't have to go to the trouble for virtually any kind of data because there are existing .NET Data Providers for almost every conceivable data source, as well as one-size-fits-all (OSFA) data providers that permit you to use existing ODBC and OLE DB drivers and providers to access legacy data.

Focusing on SqlClient

For most of this book, I'll be focusing on the SqlClient .NET Data Provider, as it's specifically designed to access Microsoft SQL Server Version 7.0 and later. For earlier versions of SQL Server, I recommend the System.Data.Odbc .NET Data Providertry to avoid use of the OleDb namespace, if possibleunless you don't care about performance. If you have to use one of the other providers, some of the rules change. For example, ODBC and OLE DB use different techniques to mark parameters in T-SQL queriesthey don't support named parameters. For best results, see my earlier books that discuss these older technologies.

Essentially, the System.Data.SqlClient namespace contains classes used to create objects used by the System.Data namespace. No matter what data source you use, all roads lead to the System.Data namespace, where the DataTable, DataSet, and other essential data manipulation classes live. To illustrate this, Figure 8.1 shows a simple application's use of ADO.NET. Here I open a SqlConnection and use a parameter-driven SqlCommand to return a SqlDataReader. This data stream is used to populate a DataTable bound to a DataGridView control and pass back changes via a SqlParameter object and another SqlCommand.

Figure 8.1. ADO.NET basics.