Export (0) Print
Expand All

ADO.NET 2.0 Feature Matrix

 

Bob Beauchemin
DevelopMentor

March 2005
Updated June 2005

Summary: ADO.NET 2.0 includes a new base-class provider model, features for all providers, and changes to System.Data.SqlClient. Get an overview of these new features, examples of their use, and a chart of which features are provider-neutral and SqlClient-specific. (14 printed pages)

Contents

The Base-Class-Based Provider Model
Provider Factories
Server Enumeration
Connection String Builder and Metadata Schemas
Tracing
SqlClient Enhancements
Connection Pooling Enhancements
Asynchronous Commands
Bulk Import
Provider Statistics
AttachDbFileName
SQL Server 2005-Specific Features in SqlClient
MARS
SqlDependency and SqlNotificationRequest
Password Changing
System.Transactions Integration
Client Failover
Support for New Transaction Isolation Level
DataTypes - UDTs, the XML data type, and "MAX" BLOBs and CLOBs
Conclusion

ADO.NET 2.0 comes with a plethora of new features. This includes a new base-class-based provider model and features that all providers can take advantage of, as well as changes that are specific to System.Data.SqlClient. Because the .NET Framework 2.0 is being released in conjunction with SQL Server 2005, some of these features require SQL Server 2005 to be usable. This article is meant to serve as an overview and roadmap of the new features, give examples of their use, and includes a chart of which features are provider-neutral and which are SqlClient-specific. In future articles in this series, I'll be going over some of the features in greater detail. In addition, there are many new features of the DataSet and friends; these will be covered in future articles.

The Base-Class-Based Provider Model

In ADO.NET 1.0 and 1.1, provider writers implemented a series of provider-specific classes. Generic coding was possible based on the fact that each of the classes implemented a generic interface. As an example, System.Data.SqlClient contains the class SqlConnection and this class implements IDbConnection. System.Data.OracleClient contains the class OracleConnection, which also implements IDbConnection. The provider-specific classes could implement data-source-specific properties and methods, e.g., SqlConnection implements the Database property and the ChangeDatabase method. OracleConnection does not, because the Oracle database does not have the concept of multiple "databases" (these are known as catalogs in ANSI SQL) per database instance. The new provider model in ADO.NET 2.0 is based on a series of base classes in System.Data.Common. These provide a basic implementation of common functionality and, of course, each of the base classes implements the still-required generic interface for backward compatibility. Provider writers can choose to use the base classes or support the interfaces.

There were two exceptions to the interface model in previous versions, the DataAdapter/DbDataAdapter and CommandBuilder. The CommandBuilder class provides an automatic implementation of INSERT, UPDATE, and DELETE commands that use the same column-set, for a simple SELECT command. Extending a CommandBuilder while keeping the base algorithm that it used to create action statements was not possible because the SqlCommandBuilder was a sealed class. Although there is still no way to reuse the SqlCommandBuilder parameter parser, there is a DbCommandBuilder base class in System.Data.Common. There are new features exposed at the base-class level in these classes, too. The DataAdapter/DbDataAdapter base classes expose mechanisms for pushing provider-specific types like SQL Server SqlTypes into the DataSet (the ReturnProviderSpecificTypes property) and for batch updates (StatementType.Batch enumeration value and UpdateBatchSize property). The DbCommandBuilder common base class includes a property to indicate concurrency policy choices (the ConflictDetection property).

Provider Factories

One of the complications of the interface-based approach in ADO.NET 1.0 and 1.1 is that you can't call a constructor on an interface. You must create a concrete instance of a specific class. Previous APIs like OLE DB and ADO worked around this by overloading the connection string. The connection string contained the COM PROGID of the provider, and the correct DataSource class was created based on this PROGID. This was possible because OLE DB DataSource PROGIDs were stored in the registry.

' VB6 ADO code, Connection is an interface (actually it's _Connection)
Dim conn as Connection
' note that the default provider is MSDASQL, the OLE DB provider for ODBC
' this uses the OLE DB provider for SQL Server
conn.ConnectionString = "provider=sqloledb;.."  ' other parameters deleted
conn.Open

ADO.NET 2.0 has a solution for this. Each data provider registers a ProviderFactory class and a provider string in the .NET machine.config. There is a base ProviderFactory class (DbProviderFactory) and a System.Data.Common.ProviderFactories class that can return a DataTable of information about different data providers registered in machine.config, and also retrieve the correct ProviderFactory given the provider string (called ProviderInvariantName) or a DataRow from the DataTable. Conditional code that used to be written like this:

enum provider {sqlserver, oracle, oledb, odbc};
// determine provider from configuration
provider prov = GetProviderFromConfigFile();
IDbConnection conn = null;
switch (prov) {
  case provider.sqlserver: 
    conn = new SqlConnection(); break;
  case provider.oracle:     
    conn = new OracleConnection(); break;
  case provider.oledb:      
    conn = new OleDbConnection(); break;
  case provider.odbc:       
    conn = new OdbcConnection(); break;
  // add new providers as the application supports them
}

...can now be written like this:

  // get ProviderInvariantString from configuration
  string provstring = GetProviderInvariantString(); 
  DbProviderFactory fact = DbProviderFactories.GetFactory(provstring);
  IDbConnection = fact.CreateConnection();  

The appearance of a standard for retrieving the data providers installed on a machine and a ProviderFactory for each one opens up some other interesting possibilities.

Server Enumeration

The provider configuration entry in machine.config specifies a bitmask that indicates which of the base classes or base interfaces this provider supports. This is because not all data providers need to support all the functionality in System.Data.Common. For example, CommandBuilder is a "nice-to-have" class, but you could do fine without it.

DbEnumerator is a new base class that has been added to the mix in ADO.NET 2.0. This class permits data providers that support it to obtain a list of data sources. For example SqlClient supports this class and returns a list of SQL Server instances that are available on the network. This allows programs and tools to present the user with a choice of data source. One of the tools that use this is Visual Studio 2005.

Connection String Builder and Metadata Schemas

Visual Studio .NET used, until now, an OLE DB component to build connection strings to represent data sources. When you use Server Explorer in Visual Studio 2005 to add a new Data Connection in Visual Studio .NET 2003, it displays the OLE DB connection string builder, which lists the OLE DB providers installed on your machine, not the .NET data providers. It then allows you to select a provider (albeit an OLE DB provider) and build an ADO.NET connection string for the corresponding provider. In Visual Studio 2005, DbProviderFactories, mentioned above, can present you with a list of .NET data providers, and a class, DbConnectionStringBuilder, is used by a graphic user interface component to enable a programmer to build a connection string graphically and load and store connection strings from configuration files.

Visual Studio 2005 Server Explorer also obtains data source metadata such as lists of Tables, Columns, Views, and Stored Procedures for display. The ANSI SQL specification has a base specification for this metadata; it's known as the INFORMATION_SCHEMA views. These generic views are a nice start, but sometimes need to be extended with database-specific views or information. In ADO.NET 2.0, data providers can provide an XML-format configuration file that lists what metadata is available and how to obtain it from the database, since all databases don't yet support the INFORMATION_SCHEMA views. This will be a big help in permitting tool programmers to obtain a provider-defined extended set of information. I'll be talking more about enhancements to the provider model in future articles.

Tracing

It's very useful to permit programmers and support staff to trace database API calls to find out where in the data access stack a problem lies, given a description from a user or error message from a program. In general the problem can arise from:

  1. Schema mismatch between client program and database reality
  2. Database unavailability or network library problems
  3. Incorrect SQL, either hard-coded or generated by an application
  4. Incorrect programming logic

In the past, instrumenting code to permit tracing has been left up to the individual provider writer, although there are some de facto standards in some APIs, such as ODBC. The lack of a standard OLE DB trace made it more difficult to resolve OLE DB and ADO problems. Although this is not an ADO.NET-only architecture, Microsoft providers in ADO.NET 2.0 take advantage of generalized tracing and instrumentation APIs. Using the new functionality, you'll be able to trace a problem at any level of the application stack. Not only are Microsoft ADO.NET providers instrumented, but other parts of the data access stack use this functionality and it's available for provider writers to implement as well. Even the ADO.NET 2.0 DataSet and related classes have built-in diagnostics. I'll cover tracing in depth in a future article.

SqlClient Enhancements

The Microsoft flagship database is SQL Server and SqlClient is the SQL Server-specific provider. ADO.NET 2.0 actually ships with four Microsoft providers:

  • SqlClient—the Microsoft provider for SQL Server
  • OracleClient—the Microsoft provider for the Oracle database
  • OleDb—the bridge provider for using OLE DB providers in ADO.NET
  • Odbc—the bridge provider for using ODBC drivers in ADO.NET

In ADO.NET 2.0, all four of these providers have been enhanced to enable their use in partially trusted environments. By properly configuring .NET code access security (CAS), it is possible to enable more data-centric mobile code scenarios. In ADO.NET 1.1, only the SqlClient provider supported this feature.

In addition, data providers are written by database companies (Oracle's ODP.NET and IBM's data provider for DB2), provider specialists (DataDirect Technologies), and open source projects and individuals. In addition, Microsoft will ship a DB2 data provider in Host Integration Server 2004 product.

Because SQL Server is an important piece of the software puzzle, there are many enhancements to SqlClient in ADO.NET 2.0, in addition to the enhancements in all Microsoft-supported providers. Some of this functionality supports any version of SQL Server, while much of the new functionality is meant to support the many new features available in SQL Server 2005, which may be more easily recognized by its codename, "Yukon". SQL Server 2005 supports .NET code running inside the server, and there are optimizations for data access inside the server using the provider model as well. One big internal change that is not immediately evident is that the SqlClient data provider in ADO.NET 2.0 does not use the Microsoft Data Access Components (MDAC). There is also better error handling in the provider, with clearer error messages for network errors and more granular error messages overall. Here's an overview of the programmer-visible SqlClient-specific functionality.

Connection Pooling Enhancements

ADO.NET 1.0 introduced a new infrastructure for pooling database connections. The Microsoft SqlClient and OracleClient data providers use this infrastructure; the OleDb and Odbc data providers do not. The new pooling mechanism provided granular support of connection pooling parameters, including minimum and maximum pool sizes and the ability for the pool manager to wait for a user-defined amount of time for a connection to become available in the pool. ADO.NET adds a connection-pooling enhancement that allows you to programmatically "drain" the connection pool; that is, close all of the connections currently kept alive by the pooler. You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.

Asynchronous Commands

Sometimes in client or middleware code, you want to do more than one thing at the same time. In inherently multithreaded middleware code, this is a key factor for increasing throughput. In ADO.NET 2.0, SqlClient now supports asynchronous command execution.

The .NET paradigm for asynchronous operations is to provide a set of Begin and End methods for an operation, as well as a method for synchronous operation. Because database command execution can take a long time, SqlClient now provides built-in SqlCommand methods that provide asynchronous execution. Methods that support asynchronous execution and their synchronous counterparts are listed in the table below.

Synchronous MethodAsynchronous Methods
ExecuteNonQueryBeginExecuteNonQuery, EndExecuteNonQuery
ExecuteReaderBeginExecuteReader, EndExecuteReader
ExecuteXmlReaderBeginExecuteXmlReader, EndExecuteXmlReader

Although asynchronous execution can be a nice feature, it should not be used gratuitously; only use it if you know the command can run for a long time, and also that you have something useful to do at the same time. The Windows thread scheduler in the Windows NT family of operating systems (the feature is not available on Windows 9x and Me clients) takes overhead of its own to switch between threads. Also bear in mind that some .NET libraries are thread-sensitive; using asynchrony, the thread that you use to start the operation won't necessarily be the same thread it finishes on. However, the SQL Server network library stack has been enhanced to support asynchrony by means of I/O completion ports and this provides better throughput for asynchronous SQL Server operations. Not only can asynchronous operation be effective for multiple action statements and stored procedure execution, when used with the multiple active resultset feature in SQL Server 2005, you can multiplex asynchronous SELECT statements using a single database connection.

Bulk Import

Many database applications can INSERT rows into SQL Server in large batches, quickly. The canonical example of this is an application that inserts rows into SQL Server that correspond to readings from a hardware device, such as a telephone switch or a hospital patient monitor. Although SQL Server comes with utilities (like bcp) to accommodate this, these typically use a file for their input.

SqlClient contains a new class called SqlBulkCopy. This class is not meant to directly consume input from files and produce file output like BCP, but to accommodate inserting many rows into the database from a client quickly and efficiently. SqlBulkCopy can get its input from DataReaders and DataSets. This means that you can not only stream a series of rows from a provider directly (DataReader), but also fill DataSets with outside data obtained from a hardware device that is not a provider and update this directly; in this case, no provider is needed as a source.

// Fill up a DataSet
DataSet ds = new DataSet();
FillDataSetFromHardwareDevice(ds);
// Copy the Data to SqlServer
string connect_string = GetConnectStringFromConfigFile();
SqlBulkCopy bcp = new SqlBulkCopy(connect_string);
bcp.DestinationTableName = "hardware_readings";
bcp.WriteToServer(ds);

Provider Statistics

Some application writers find it useful to do "real-time" monitoring in their application. Although you could use Windows Performance Monitor, define your own performance classes, and use internal (and possibly fragile, over time) SQL Server metadata calls to obtain this information, SqlClient now has a built-in way to provide this information for you. An instance method on the SqlConnection class lets you harvest per-connection statistics that are similar to those available in the ODBC API. Because storing and gathering these statistics takes overhead of its own, there is a property that can be used to toggle statistics gathering. There is also a method to reset the counters. Statistics gathering is turned off by default, of course, and is also set off when you return a connection to the connection pool by calling Dispose or Close in a pooling scenario. Here is an example of the statistics produced.

string connect_string = GetConnectStringFromConfigFile();
SqlConnection conn = new SqlConnection(connect_string);
conn.Open();
// Enable
conn.StatisticsEnabled = true;
// do some operations
//
SqlCommand cmd = new SqlCommand("select * from authors", conn);
SqlDataReader rdr = cmd.ExecuteReader();
Hashtable stats = (Hashtable)conn.RetrieveStatistics();
// process stats
IDictionaryEnumerator e = stats.GetEnumerator();
while (e.MoveNext())
Console.WriteLine("{0} : {1}", e.Key, e.Value);
conn.ResetStatistics();
Connection-specific statistics
BuffersReceived   : 1
BuffersSent       : 1
BytesReceived     : 220
BytesSent         : 72
ConnectionTime    : 149
CursorFetchCount  : 0
CursorFetchTime   : 0
CursorOpens       : 0
CursorUsed        : 0
ExecutionTime     : 138
IduCount          : 0
IduRows           : 0
NetworkServerTime : 79
PreparedExecs     : 0
Prepares          : 0
SelectCount       : 0
SelectRows        : 0
ServerRoundtrips  : 1
SumResultSets     : 0
Transactions      : 0
UnpreparedExecs   : 1

For more information about exactly what these statistics represent, consult the ADO.NET 2.0 or the ODBC documentation.

AttachDbFileName

The SqlClient data provider supports desktop applications (in which the database is stored on a user's desktop) as well as client-server and middleware-based applications. There is a special version of SQL Server known as MSDE; the SQL Server 2005 era name for this product is SQL Server 2005 Express Edition. In desktop applications, the database itself is application-specific and bundled with the application. The user may even be unaware that SQL Server is being used as the data repository, as the application setup program will run the SQL Server Express installation.

To facilitate attaching the database files to the SQL Server Express instance inside of an application, ADO.NET 1.0 provided a connection string parameter, AttachDbFileName. This parameter had to be specified as a hard-coded pathname, however, making it difficult for users to install the application in a location other than the default. In ADO.NET 2.0, the AttachDbFileName parameter can be a relative path, and is used in conjunction with application configuration settings. This makes setting up a desktop application for SQL Server Express as easy as connecting to a Microsoft Access file-based data store.

SQL Server 2005-Specific Features in SqlClient

MARS

When you select a set of rows using a SQL SELECT statement, either as a stand-alone or inside a stored procedure, SQL Server doesn't automatically produce a cursor over the set of rows as some databases do. Instead, it uses an optimized method to stream the resultset across the network, on occasions reading from the database buffers directly as the network library pulls the data in packet-size chunks. This is known as "the default resultset of SQL Sever" in SQL Server Boks Oline, or "the cursorless resultset". In versions of SQL Server prior to SQL Server 2005, there could only be a single cursorless resultset active on a single connection at a time.

Different database APIs and libraries dealt with the one connection/one cursorless resultset behavior differently. ADO.NET 1.0 and 1.1 throw an error if you attempt to open a second cursorless resultset; ADO "classic" actually opened a new database connection behind the scenes. Opening a new database connection was more convenient, though less "precisely correct" than throwing an error; this convenience feature was inadvertently abused by some programmers and resulted in more database connections than they bargained for.

In SQL Server 2005, the database has been enhanced to permit multiple cursorless resultsets to be active on a single connection at a time. This produces the feature acronym "MARS" (multiple active resultsets). There are changes to the network libraries to support this behavior, and both the new network libraries and the new database are needed to enable MARS.

In SqlClient code, you multiplex resultsets by having multiple SqlCommand instances use the same connection. Each SqlCommand can accommodate a SqlDataReader produced by calling Command.ExecuteReader, and multiple SqlDataReaders can be used in tandem. In ADO.NET 1.0 and 1.1, you must close one SqlDataReader before obtaining another, even if multiple SqlCommand are used. Note that you cannot multiplex SqlDataReaders produced from multiple ExecuteReader calls on the same SqlCommand instance. Here's a short (but not very functionally useful) example:

// connection strings should not be hardcoded
string connstr = GetConnStringFromConfigFile();
SqlConnection conn = new SqlConnection(connstr);
SqlCommand cmd1 = new SqlCommand(
  "select * from employees", conn)
SqlCommand cmd2 = new SqlCommand(
  "select * from jobs", conn)
SqlDataReader rdr1 = cmd1.ExecuteReader();
// next statement causes an error prior to SQL Server 2005
SqlDataReader rdr2 = cmd2.ExecuteReader();
// now you can reader from rdr1 and rdr2 at the same time.

This feature is not just about reducing errors or clarifying what used to be ADO library magic. It can be extremely useful in conjunction with asynchronous operations described above. Multiple asynchronous SELECT statements or stored procedure invocations can be executed in tandem, saving database connections and optimizing throughput. Imagine filling 20 drop-down list boxes on a form at the same time, using a single connection. You can also execute non-resultset-returning statements while a resultset is active.

Although multiple streams of execution can be active at the same time, all of the execution streams must share the same transaction, if a transaction exists. Transactions are still connection-scoped rather than command-scoped. You associate the SqlTransaction instance with the SqlCommand by setting the SqlCommand Transaction property as in previous versions of ADO.NET.

SqlDependency and SqlNotificationRequest

It's extremely helpful in middle-tier caching situations to be able to refresh the cache based on the fact that someone else has changed a row in the database. Programmers have resorted to a few different techniques to accomplish this, such as writing a trigger that updates a file when the table or view changes, or refreshing the cache every so often whether the database has changed or not. There is no straightforward way the register for database notifications until the SqlClient SqlNotificationRequest and SqlDependency classes.

SqlDependency is a high-level class that wraps a SqlNotificationRequest and presents your notification information as a .NET event. With SqlNotificationRequest, there is no event and you must "do the heavy lifting" of registering for the notification and harvesting the information yourself. The great majority of programmers will use SqlDependency. SqlDependency can be used as a stand-alone and its functionality is available directly when using the ASP.NET Cache class.

This SQL Server 2005-specific functionality depends on SQL Server Service Broker, a new feature that implements a scalable queuing system. Note that, when using the ASP.NET Cache class, polling the database is used instead of Service Broker to achieve similar functionality. When using Service Broker and SQL Server 2005, you need not maintain a connection to the database in order to be notified. SqlDependency uses your choice of HTTP or TCP protocols and contacts you when the underlying rows change. The notification does not contain any row-specific information: when you are notified, you must fetch the entire set of rows again and re-register for the notification.

This functionality is just what you need for a single cache or a limited set of users, but beware when using it with large numbers of users listening at the same time. Each user must refresh the entire rowset in cache when any row changes. With a large number of changes and a large number of users, the SELECT statements used for refresh could be a significant hit on the database.

Password Changing

SQL Server 2005 provides a mechanism to use SQL logins that are subject to the same expiration as other password policies that integrated logins (Windows logins connecting to SQL Server). This feature requires SQL Server 2005 running on Windows Server 2003. If a SQL login password (like 'sa') is going to expire, you won't be able to use the traditional Windows mechanism and password changing APIs to change it. You can only change this password using a SQL Server client that ends up calling the Transact SQL ALTER LOGIN verb.

SqlClient accommodates this through the ChangePassword method on the SqlConnection class. Note that this method is only useable if executed against a SQL Server 2005 instance; although you can change a SQL login on older versions of the database, this API uses a network packet type that no other version of SQL Server supports. Another aspect of password changing to consider is that it is no longer possible to hard-code SQL Server login IDs and passwords in connection strings in programs. Unless you are going to produce .NET intermediate language and replace the executable each time the password changes (this is not a viable option), you must store your SQL Server password in a configuration file. Serious SQL Server developers have been using a configuration file to store (hopefully encrypted) passwords for quite a while. Better yet, always use SQL Server integrated security, if possible.

System.Transactions Integration

The SqlClient provider in ADO.NET 2.0 integrates with the new System.Transactions namespace, enabling a behavior known as promotable transactions. Although Transact SQL can be used to start a local or distributed transaction (BEGIN TRANSACTION and BEGIN DISTRIBUTED TRANSACTION), there are occasions, especially in client-side/middle-tier programming where the programmer may wish to write a component that could be used in one database or multiple database scenarios. These scenarios might include multiple SQL Server instances and SQL Server can automatically detect multi-instance access and "promote" a transaction from local to multi-instance (distributed). This is even possible when multiple database products or multiple connections are used, as long the first database (known as a resource manager in distributed transaction terminology) is SQL Server. Promotable transactions are enabled by default in ADO.NET.

Client Failover

SQL Server 2005 supports a "hot spare" capability through database mirroring. If a SQL Server instance fails, the work can be shifted over to the backup server automatically. This requires an instance to witness the failover known as (not surprisingly) the "witness instance". Hot spare scenarios require that existing client connections must "know" to fail over (establish a connection with the new server instance), as well. Client connections that produce an error on the next attempted access and must be manually "failed over" by client programming are suboptimal. SqlClient in ADO.NET 2.0 supports client failover without special programming of the application program.

Support for New Transaction Isolation Level

SQL Server 2005 supports transaction isolation through two methods, locking and versioning. Previous versions of SQL Server supported locking but not versioning. Two types of versioning are supported; these are known as statement-level versioning and transaction-level versioning. The feature is meant to selectively reduce locking in extreme circumstances and to ease conversion of applications that were designed for versioning databases. Applications designed for versioning databases often need significant changes when porting them to a locking database, and vice-versa. The default behavior of a versioning database is almost always statement-level versioning. For more information on the difference, consult A First Look at SQL Server 2005 for Developers by Beauchemin, Berglund, and Sullivan.

Both different versioning and different locking behavior equate to starting a transaction using a specific transaction isolation level. There are four transaction isolation levels defined by the ANSI SQL specification:

  • READ UNCOMMITED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

SQL Server supports all four isolation levels, and did so even prior to SQL Server 2005. Versioning databases typically support only READ COMMITTED and SERIALIZABLE. READ COMMITTED implements statement-level versioning and SERIALIZABLE implements transaction-level versioning in versioning databases. READ COMMITTED is the default behavior for almost all databases, whether locking or versioning is used.

Statement-level versioning is enabled and it is the default behavior by setting database options on a per database basis. When statement versioning is enabled, specifying IsolationLevel.ReadCommitted or IsolationLevel.ReadUncommitted uses this behavior. To support transaction-level isolation, SQL Server 2005 defines a new isolation level IsolationLevel.Snapshot. SqlClient (and only SqlClient) supports this isolation level. This isolation level was required because you can turn on statement-level or transaction-level versioning separately and IsolationLevel.Serializable is already used by SQL Server to correspond to locking behavior.

DataTypes - UDTs, the XML data type, and "MAX" BLOBs and CLOBs

SQL Server 2005 adds support for user-defined types, a native XML data type and better large data support. Large data support is improved by using the Transact-SQL types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). User-defined types and a native XML type are defined by the SQL:1999 and SQL:2003 specifications. To use these data types with SqlClient, new classes in the System.Data.SqlTypes namespace are defined (SqlUdt and SqlXml), support is added to the SqlDbTypes enumeration and IDataReader.GetValue was enhanced to support returning UDTs as .NET Object types, and support returning XML as a .NET String.

These new SQL Server 2005 types are supported in DataReaders returned from SQL SELECT statements and as Parameters using SqlParameter. A special class, SqlMetaData, can return information about extended properties of these new data types, such as the XML schema collection that a strongly typed XML column adheres to, or the database name of a UDT. You can use these types from the client directly, in generic code, and also in the DataSet. Finally you can perform partial updates on the "MAX" data types from the client, which required using special SQL functions before ADO.NET 2.0. There will be future articles on this site that delve into the details.

Conclusion

Whew! That's a lot of features, almost too many to keep track of. To help you from drowning in the new sea of functionality, I conclude with a chart of each new feature and which database, provider, and version you must have to make it work. I currently only have information on the four providers that are part of ADO.NET, but other provider vendors will likely join in shortly. In future articles, I hope to expand the chart.

New Feature Availability

All ProvidersSQL Server 7/2000SQL Server 2005
Provider FactoriesXXX
Runs w/Partial TrustXXX
Server EnumerationXXX
Connection String BuilderXXX
Metadata SchemasXXX
Batch Update SupportXXX
Provider-Specific TypesXXX
Conflict DetectionXXX
Tracing SupportXXX
Pooling EnhancementsSqlClient and OracleClientXX
MARS X
SqlNotificationRequest X
SqlDependency X
IsolationLevel.Snapshot X
Asynch Commands XX
Client Failover X
Bulk Import XX
Password Change API X
Statistics XX
New Datatypes X
Promotable Tx XX
AttachDbFileName XX

 

About the author

Bob Beauchemin is an instructor, course author, and database curriculum course liaison for DevelopMentor. He has over twenty-five years of experience as an architect, programmer, and administrator for data-centric distributed systems. He's written articles on ADO.NET, OLE DB, and SQL Server for Microsoft Systems Journal and SQL Server Magazine and others, and is the author of A First Look at SQL Server 2005 for Developers and Essential ADO.NET.

Show:
© 2014 Microsoft