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