The release of ADO.NET 2.0, as part of the Visual Studio 2005 launch and the refresh of the .NET Framework and Common Language Runtime, passed relatively unnoticed. That's understandable. With so much going on in the Microsoft tools domain, with the new Visual Studio Team System, SQL Server 2005, and previews of Office 12 and Windows Vista, that some little database connectivity stuff could easily be overlooked.

Well, look closer. ADO.NET 2.0 is a significant revamping of the connectivity infrastructure within .NET and the CLR, and has special benefits for SQL Server 2005 developers. If you're already been working with the new classes and methods, kudos to you. For the rest of you, it's time to see what the updated relational data access system can do for you. Specifically, we're going to talk about a few of my favorite enhancements, including asynchronous data access, metadata schemas, query notification, multiple active result sets and some new data types. And even with all this, we're only scratching the surface of what ADO.NET 2.0 offers.

Async Data Access
This is a biggie, folks. With the first versions of ADO.NET, numbered 1.0 and 1.1, a database connection could only process commands serially; in a series of commands to a data provider, like SqlClient, each command had to terminate before the next could begin. Sure, that's often perfectly fine for simple transaction-processing scenarios, where you to open a connection, write a record, and then close the connection again. But what if you are planning to execute a sequence of operations that can logically be performed in parallel, such as reading a number of datasets into memory? Why not do them all at once?

When using the SqlClient data provider under ADO.NET 2.0, you can now perform true non-blocked async I/O with the SQL Server database. This is enabled by initializing the connection using the phrase async=true in the connection string, and then by taking advantage of new methods in the data provider that can express that you want to perform operations asynchronously.

For example, where you used to have the synchronous ExecuteReader method, you now have two new methods, BeginExecuteReader and EndExecuteReader. Similarly, there's now a BeginExecuteNonQuery / EndExecuteNonQuery pair, and a BeginExecuteXmlReader / EndExecuteXmlReader pair. The Begin... methods take all the input parameters; the End... methods take all the output parameters and provide the return value. Neat, huh? Note that these async commands work against SQL Server 7.0, SQL Server 2000 and SQL Server 2005. Learn more from "Asynchronous Command Execution in ADO.NET 2.0."

Metadata Schemas
When you use Visual Studio 2005 to configuring ADO.NET 2.0 to talk to a live database within your application, the IDE can prepopulate many of the database access parameters. Why? Because ASP.NET 2.0 and its data providers can access metadata about that database, which the IDE can take advantage of to help you with drag-and-drop functionality as you link the database to your app. The result is that Visual Studio 2005 is more helpful, when it comes to database development, than any previous version.

Okay, so it makes coding a little easier. Beyond that, who cares about database metadata? Well, you should, if you want your own application to be as smart as Visual Studio 2005 itself. By using that metadata, your app can become more flexible, such as being able to link dynamically to databases and automatically configure itself to discover and use the appropriate tables, columns, stored procedures and triggers. Alternatively, you can use the metadata to help your users customize their interactions with databases, such as by creating reports that take advantage of tables or columns that you didn't know about you built your application.

Where does this metadata come from? Initially, it comes from the database engine itself. Every modern relational database or data source has its own way of expressing metadata to its access clients. SQL Server 2005, for example, uses a different metadata system than SQL Server 2000, but both are understood by SqlClient. Oracle has its own metadata system, which is read the OracleClient data provider.

ADO.NET 2.0 provides a class, System.Data.Common.DbMetaDataCollectionNames class, to store the metadata, and it's up to each data-provider writer to import that metadata from the database and populate the class. Microsoft does that with the SqlClient and OracleClient data providers included with ADO.NET 2.0, of course.

These new metadata classes work with all data sources supported by ADO.NET 2.0 data providers, not just with SQL Server 2005. Want to learn more? Check out "Schemas in ADO.NET 2.0."

Query Notification
A lot of Web applications use external databases to store lookup tables—lists of product codes, perhaps, or branch office locations, state abbreviations, product categories. These items don't change often; perhaps hourly, perhaps daily, perhaps annually. So, it's reasonable to cache those items within the Web app, instead of making a new query back to SQL Server every time a browser asks for a list of states to populate a drop-down field.

The problem with caching using ADO.NET 1.0 and 1.1 was that it was difficult to know when one of those rarely changed tables had actually changed, and thus the cache should be reloaded. That meant duct-tape workarounds, such as arbitrary decisions when to reload the cache, or using triggers or other mechanisms to set a "dirty" flag, which your application would watch for somehow.

ADO.NET 2.0 and SQL Server 2005 solve this problem with their new Query Notification signaling mechanism. The ASP.NET 2.0 cache can now register a request for notification with the SQL Server 2005 database, though a new SqlNotificationRequest class contained within the SqlCommand. The SQL Server database will remember that query notification, along with the query and its rowset. If that rowset changes, SQL Server 2005 will send back a notification to the client's SqlDependency class using the new SQL Server Service Broker. Simple and elegant. This only works with SQL Server 2005; more information is at "Query Notifications in ADO.NET 2.0."

Multiple Active Result Sets
It seemed like a big step backwards to many Visual Basic and Visual C++ developers: The original ADO permitted you to maintain multiple active cursorless result sets within a database, but ADO.NET 1.0 / 1.1 did not. If you tried to open more than one cursorless result set, ADO.NET 1.0 / 1.1 returned an error condition. The upshot was that in ADO.NET 1.0/1.1, if you needed multiple result sets, you had to close one SqlDataReader before opening another. Annoying, and arbitrary.

When used in conjunction with SQL Server 2005, ADO.NET 2.0 solves that problem by allowing you to open multiple result sets at the same time. You can learn more about this from "Multiple Active Result Sets (MARS) In SQL Server 2005." If you relied upon this capability in ADO, doesn't this make your day?

User Defined, XML Data Types
Another SQL Server 2005-specific feature of ADO.NET 2.0 is support for user defined types and XML data types.

User defined types were standardized by the SQL:2003 specification published by the ISO. You can now access them by using the SqlUdt namespace in the System.Data.SqlTypes namespace; the result is a .NET Object. Similarly, there's a new SqlXml namespace for XML data; the results are returned as a .NET String. These match up with the new XML data type within SQL Server 2005; this is a first-class data type, not just something cobbled together from strings, and you can also access XML using the new SqlCommand.Execute.XmlReader method.

For more, see "XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005."

That's Not All, Folks!
We've just scratched the tip of the ADO.NET 2.0 iceberg; when you add in the new capabilities in the upgraded .NET CLR, and the features of SQL Server 2005, there's a wealth of functionality for managed .NET apps now available to you. Dive in, check it out!