Cool New Features in ADO.NET 2.0

In the last several columns, I've been covering SQL Server 2005's new features. I haven't finished discussing those features, but I want to take a short break and discuss ADO.NET 2.0 instead. ADO.NET 2.0 introduces a host of new features, the majority of which are specific to SQL Server 2005. There are also features that, while specific to SQL Server 2005, are available for previous versions of SQL Server. ADO.NET 2.0 even offers new features that are common across the ADO logic regardless of the underlying database or access method. Because there are so many new features in ADO.NET 2.0, let's build on our recent discussions of SQL Server 2005 by looking at some features that are specific to SQL Server 2005.

The first feature I want to discuss is ADO.NET 2.0's support for custom user-defined types (UDTs). As I mentioned in my column "Leveraging the CLR's Power" (http://www.windowsitpro.com/Article/ArticleID/45575/45575.html), you can use the Windows .NET Framework 2.0 and the embedded Common Language Runtime (CLR) in SQL Server 2005 to create custom UDTs. When you use Visual Studio 2005 to create an assembly, you can leverage that same assembly on SQL Server 2005 and the .NET client that will consume your data. ADO.NET 2.0 references the custom assembly you create to enable the client to consume your custom type. The result is that all the information about your custom UDT, which is defined in SQL Server 2005, is available on your .NET client. This is important because your custom UDT will have custom behavior. By having the class available both on the client and server, this behavior is carried with the data. The key is that a UDT doesn't define just a custom structure; it defines both a custom structure and custom behavior. And ADO.NET 2.0 is the means by which you can communicate a UDT's behavior outside the database.

Speaking of data types, SQL Server 2005 introduces a new length parameter, MAX, that applies to three existing data types. The three affected data types that are fully supported by not only SQL Server 2005 but also ADO.NET 2.0 are varchar(MAX), nvarchar(MAX), and varbinary(MAX). The only difference between these new data types and their existing counterparts (i.e., varchar(#), nvarchar(#), and varbinary(#)) is that you aren't specifying the variable length, so you aren't limited to the maximum length of a row in SQL Server. Thus, for example, the varchar(MAX) data type allows up to 2GB of text data. Just like a Text column, a varchar(MAX) column acts like it's part of the existing row because it spans the 8K row limit of SQL Server, but the varchar(MAX) column data is actually stored separately from the remaining row data.

From what I can tell, the new MAX length parameter is more sizzle than steak. The Text, nText, and Image types already support very large data elements, and there isn't any significant difference in behavior between them and varchar(MAX), nvarchar(MAX), and varbinary(MAX). However, for those who have been looking for what is essentially an unlimited variable character field that isn't called a text field, this new parameter will meet your needs.

For a demonstration of how to work with varchar(MAX), nvarchar(MAX), and varbinary(MAX), see the Microsoft article "Working with Large Value Types in ADO.NET" (http://msdn2.microsoft.com/library/6dc6c720.aspx--this link will change after Visual Studio 2005 is released). In the "Retrieving Large Value Types as Parameter Values" section at the bottom of the article, you'll find a Visual Basic (VB) example in which a varchar(MAX) column is returned as an output parameter. This example provides the best demonstration.

For me, a more useful feature in ADO.NET 2.0 is Multiple Active Result Sets (MARS), which lets you simultaneously reference multiple database commands across a single connection. MARS lets you use multiple commands without each of those commands needing its own connection.

You can use two data readers to get an idea of how MARS works. (This feature is generally described in relation to data readers simply because Datasets and DataTables are disconnected constructs.) There are times that you might want to step through more than one table at the same time. For example, suppose you want to associate two different command objects with the same connection object. If you retrieve the first row from the first data reader, then attempt to access the second data reader, you'll get an error when using ADO.NET. You'll get the same result for any command that uses a connection object already in use by an open data reader. In ADO.NET 2.0, MARS resolves this problem by letting you have multiple different SQL commands actively working against your database.

Perhaps one of the best aspects of MARS is that it doesn't require any effort on the developer's part--it's more of an enabling technology. Think about the ability to truly share a connection across multiple simultaneous threads and you'll get an idea of how MARS could significantly impact performance in applications such as Microsoft IIS.

Another enabling feature is SQL Query Notifications. Let me clarify here that I'm not referring to SQL Server's Notification Services (http://www.microsoft.com/sql/NS/default.asp), which is an entirely different class of notifications.

Under ADO.NET 2.0, you can have applications notify SQL Server 2005 when any of the table data associated with a query changes. This feature essentially removes the need to carry out the same polling logic that every developer who has ever cached data has been forced to implement. Instead of having to repeatedly check to see whether anything has changed or watch for a triggered column to be updated, you can just tell SQL Server 2005 to notify you when a change occurs.

There are two options for Query Notifications: the automated dependency option and the manual notification request. The automated dependency option does the most to free the average developer from worrying about updates. You register for this update when you execute your command; SQL Server 2005 will then contact you when there's an update. Your application isn't going to be sitting around waiting for SQL Server 2005 to notify you of an update, so these notifications occur as events within your application. The really cool thing is that these events don't require you to maintain a connection to SQL Server 2005. You register with SQL Server 2005 to receive an event. This is event-based programming--the same type of events you receive from a mouse click or when an ASP.NET control is accessed--only these events come to your application from the database. SQL Server 2005 raises the appropriate event to your application, and your application can simply respond to the event by refreshing the cached data.

The manual notification request is similar to the traditional polling method. Why use it? Well, in some cases, you might not be able to remain on the network with SQL Server constantly, so what you're doing is leveraging the plumbing provided by Microsoft to set up the traditional flag to indicate that a table has been modified. SQL Server 2005 automatically handles the setup and monitoring of your flag. After you receive your notification, you can process the results automatically.

SQL Query Notifications are a great new feature that's coming with ADO.NET 2.0 and SQL Server 2005. An excellent article that goes in-depth into the Query Notifications feature is at the following URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp.