The Common Provider Classes
The provider-independent programming model in ADO.NET 2.0 revolves around the classes in the System.Data.Common namespace. These classes include the obvious base classes such as DbConnection, DbCommand, DbDataAdapter, DbParameter, DbTransaction, DbCommandBuilder, and DbExceptionamongst all the others you'd expect to find there. However, there are also two new classes that implement the provider-independent model:
- DbProviderFactory is the core class that you use to generate instances of the "working" classes you need, for example a connection, command, parameter, etc.
- DbProviderFactories is a class that allows you to create specific instances of the DbProviderFactory class, each of which represents one of the managed code providers that are installed and available on the machine.
This means that your code can enumerate the installed providers, and display a list from which the user can select at runtime. For example, you might want to provide such a list if you build tools or applications such as a data management IDE that require this flexibility. Alternatively, you can simply use a configuration setting to let an administrator select the required provider at install time, or whenever the database system changes. And, if you follow this approach, you can still use the provider enumeration facility to check that the configured provider actually is available at runtime.
Enumerating the Installed Providers
ADO.NET needs a way to identify each provider that is installed, and this is done through an invariant name
. Each provider exposes an invariant name, which can be found in the machine.config
file section named DbProviderFactories. Generally, the invariant name is the same as the namespace in which that provider is implemented, for example "System.Data.SqlClient," or "System.Data.OleDb." The code below shows an (abridged) portion of the contents of the <DbProviderFactories>
section for a machine that has version 2.0 Beta 1 of the .NET Framework installed. You can see that this approach to defining providers supports extensibility, in that you can add your own data providers to the system by declaring them here.
<add name="SqlClient Data Provider"
description=".Net Framework Data Provider for SqlServer"
<add name="Odbc Data Provider"
".Net Framework Data Provider for Odbc"
... more providers here ...
The process for enumerating the providers (or, to be more accurate, the installed provider factories) simply involves obtaining a standard ADO.NET DataTable instance that contains details of all the installed provider factories. The static GetFactoryClasses
method of the DbProviderFactories collection returns this DataTable:
Dim dt As DataTable = DbProviderFactories.GetFactoryClasses()
|Figure 2. Enumerating the Installed Data Providers: The page shows a GridView control displaying information about the installed providers. The GridView is bound to a DataTable obtained via the DbProviderFactories.GetFactoryClasses() method.|
If you just want to display a list of providers, you can iterate through the rows in the DataTable inspecting the values they contain. An easier way is to use data binding to display the details. Figure 2
shows a simple ASP.NET page that displays details of all the installed providers. It uses just the following simple code:
' get DataTable containing list
' of installed providers
Dim dt As DataTable = _
' bind to GridView control
grid1.DataSource = dt
The DataTable is bound to a GridView control declared in the HTML section of the page:
<asp:GridView id="grid1" runat="server"
Remember that you must import the System.Data.Common namespace as well as the System.Data namespace, because that namespace implements the DbProviderFactories class. However, you don't need any of the provider-dependent classes (such as SqlClient):
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
You can see from the screenshot that there are five provider factories installed with the version 2.0 Beta 1 .NET Framework (here running on Windows Server 2003). Each has a name and description, an invariant name by which it can be uniquely identified, and details of the assembly where it is implemented.