ADO.NET 2.0 Feature Matrix

ADO.NET 2.0 Feature Matrix
Bob Beauchemin
DevelopMentor
July 2004
Applies to:
Microsoft ADO.NET 2.0
Microsoft SQL Server 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
Connection Pooling Enhancements
Asynchronous Commands
Bulk Import
Provider Statistics
AttachDbFileName
SQL Server 2005-Specific Features in SqlClient
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:
1. SqlClient—the Microsoft provider for SQL Server
2. OracleClient—the Microsoft provider for the Oracle database
3. OleDb—the bridge provider for using OLE DB providers in ADO.NET
4. 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 Method Asynchronous Methods
ExecuteNonQuery BeginExecuteNonQuery, EndExecuteNonQuery
ExecuteReader BeginExecuteReader, EndExecuteReader
ExecuteXmlReader BeginExecuteXmlReader, 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 Providers SQL Server 7/2000 SQL Server 2005
Provider Factories X X X
Runs w/Partial Trust X X X
Server Enumeration X X X
Connection String Builder X X X
Metadata Schemas X X X
Batch Update Support X X X
Provider-Specific Types X X X
Conflict Detection X X X
Tracing Support X X X
Pooling Enhancements SqlClient and
OracleClient
X X
MARS X
SqlNoticicationRequest X
SqlDependency X
IsolationLevel.Snapshot X
Asynch Commands X X X
Client Failover X
Bulk Import X X X
Password Change API X
Statistics X X X
New Datatypes X
Promotable Tx X X
AttachDbFileName X X
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 the books,
A First Look at SQL Server 2005 for Developers and Essential ADO.NET