he data access features in .NET changed the way developers write code by segregating the data providers into separate classes and namespaces, depending on the type of database that was being targeted. For example, to access SQL Server, Microsoft encourages you to use the classes from the System.Data.SqlClient namespace, such as SqlConnection and SqlDataReader, which take advantage of the better performance available through SQL Server’s native Tabular Data Stream (TDS) interfaces.

However, if you do that, your code won’t work if you switch to a different database system, and you may need to change your code to use classes in the OleDb or Odbc namespaces instead. Of course, you can do a global search and replace to rewrite the code, but that’s of no value if you want to be able to select the provider type through a simple configuration setting, or even dynamically at runtime.

In ADO.NET 2.0, Microsoft has both exposed and enhanced the base classes in the System.Data.Common namespace, and added some new classes that directly enable the implementation of provider-independent data access techniques. This article looks at these new features and demonstrates how you can create tools, development environments, and applications that let you change the provider type as required, without having to change your code.

The topics you’ll see discussed in this article are:

• The factory class hierarchy
• Enumerating the installed providers
• Selecting a provider factory
• Checking whether a provider factory is installed
• Specifying connection strings and parameter names
• Creating connections and commands
• Creating a DataAdapter and retrieving rows
• Creating and using a DataReader
• When should I use provider-independent code?

The Factory Class Hierarchy

	?
Figure 1. The Factory Class Hierarchy in ADO.NET 2.0: The figure shows the hierarchical relationship of the base classes and interfaces in ADO.NET 2.0 that let you write either provider-specific or provider-independent code implementations.

Figure 1 shows a schematic representation of the class hierarchy in ADO.NET 2.0 for the classes in System.Data and its descendant namespaces. At the root are the base interfaces for the classes, which define the functionality that is common across the different provider-dependent classes. For example the IDbConnection interface is implemented by SqlConnection, OleDbConnection, OracleConnection, etc.

Below this are the abstract base classes, such as DbConnection, which implement this common functionality. In ADO.NET 1.x, you could not write code to use these classes directly, but instead had to inherit from them and add your own required features. However, in ADO.NET 2.0, you can use the new provider factory model to create provider-specific instances of these classes using code, rather than having to write code that accesses specific provider-dependent subclasses.

In the remainder of this article, you’ll see how to use the new provider-independent model to access and create instances of the base classes on demand, how to enumerate the available providers, and when and where you should consider this approach over the more usual technique of specifying the concrete provider-dependent classes in your code.

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 DbException?amongst 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 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.

                        ... 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:

   Sub Page_Load()     ' get DataTable containing list      ' of installed providers     Dim dt As DataTable = _        DbProviderFactories.GetFactoryClasses()        ' bind to GridView control     grid1.DataSource = dt     grid1.DataBind()   End Sub

The DataTable is bound to a GridView control declared in the HTML section of the page:

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.

Selecting a Provider Factory
To select and retrieve an instance of a specific provider factory, from which you can create instances of the individual classes that you’ll use to perform the data access, you use the static GetFactory method. This method has two overloads:

• GetFactory(String) takes an invariant name, such as “System.Data.SqlClient”.
• GetFactory(DataRow) takes a reference to a row in the DataTable returned by the GetFactoryClasses method.

You can use either method. The choice probably depends on whether you are enumerating the providers first, in which case you will already have a reference to the DataTable you need, or just using a value from?for example?a configuration file and instantiating the specified provider factory directly.

Configuring a Specific Provider Type
If you only want your code to be configurable to a specified provider when installed, or when the database in use is changed, you can store the appropriate invariant name in the configuration information for the application. The most obvious place for ASP.NET is in a Web.config file, in the section:

Then you can create an instance of the required data factory using the following code. Notice that because you only need one instance of the data factory no matter how many provider-dependent class instances you will be creating, it’s a good idea to hold on to the single instance after you create it. You can do that by declaring the factory variable as a page-level or module-level variable, and then populating it using the GetFactory method within the Page_Load event handler or some other routine as appropriate for your application:

   ' declare a variable to hold the single instance that   ' is required of the specific provider factory class   Dim df As DbProviderFactory       Sub Page_Load()      ' get an instance of the specified provider factory class     Dim sInvariant As String = _        ConfigurationSettings.AppSettings("provider-type")     df = DbProviderFactories.GetFactory(sInvariant)     ...

Checking Whether a Provider Factory Is Installed
If you allow users to change the provider factory using a configuration setting, it’s a good idea to check that the invariant name they provide is legal and corresponds to an installed provider before you attempt to create the provider factory. Doing so prevents your code from raising an exception when the GetFactory method is called, and can more clearly indicate the source of the error to the user. All you need to do is ensure that the invariant name provided does exist in one of the rows of the DataTable returned by the GetFactoryClasses method. The easiest way is to try and select that row in the DataTable. The Select method returns an array of matching DataRow instances; if no matching row exists the array will have a Length property value of zero:

   ' check that the value supplied is a valid provider   ' invariant name   If DbProviderFactories.GetFactoryClasses.Select _      ("InvariantName='" & sInvariant & "'").Length = 0 Then      output.Text =          "Invariant Name in Web.config file is invalid."      Exit Sub   End If

Creating a Provider Factory from a DataTable Row
If your application needs to enumerate the installed providers, and provide a list from which the user can select, you will already have a DataTable containing all the installed provider factory details. You can then access a specific provider factory, and create an instance of it, by passing a reference to the appropriate row in the DataTable to the GetFactory method. For example, the code below creates a GridView that displays just the invariant name and description, together with a Select button in each row:

	?
Figure 3. Selecting a Data Provider at Runtime: The page provides the invariant name and description of the five installed data provider factories, along with a Select button so users can choose the provider.

The Page_Load event handler populates this GridView when the page first loads, though not in each postback that occurs when the user clicks one of the Select buttons. However, the code (shown in the following code) still populates the DataTable containing the list of installed data provider factories because this is needed to demonstrate the use of the GetFactory(DataRow) method later on. Figure 3 shows the page with the invariant name and description of the five installed providers displayed.

   ' declare a variable to hold the single instance that   ' is required of the specific provider factory class   Dim df As DbProviderFactory      ' declare a variable to hold the DataTable containing   ' the list of installed providers   Dim dt As DataTable      Sub Page_Load()     ' get DataTable containing list of installed providers     dt = DbProviderFactories.GetFactoryClasses()        If Not Page.IsPostback Then       ' bind to GridView control       grid1.DataSource = dt       grid1.DataBind()     End If   End Sub

Each Select button executes the routine named GetSomeDataRows, which is specified for the OnRowCommand attribute of the GridView control. The RowCommand event receives an instance of a GridViewCommandEventArgs class, whose CommandArgument property contains the index of the row in the GridView containing the Select button that was clicked. This value is first used to select the row in the GridView so that it is highlighted. Then it is used to specify the row in the DataTable that contains details of the selected provider factory. From this row, the code can extract the invariant name, which is displayed in the page and used later in the code. The next code fragment shows the relevant parts of the GetSomeDataRows routine:

   Sub GetSomeDataRows(s As Object, e As GridViewCommandEventArgs)        ' get the row index within the DataTable from the GridView     Dim iIndex As Integer = Convert.ToInt32(e.CommandArgument)        ' select this row in the GridView control     grid1.SelectedIndex = iIndex        ' get an instance of the specified provider factory class     df = DbProviderFactories.GetFactory(dt.Rows(iIndex))        ' get the Invariant Name from the selected DataRow     Dim sInvariant As String = dt.Rows(iIndex)("InvariantName")     ...

Using a Provider Factory
After getting an instance of the required provider factory, you can use it to create instances of the provider-specific data access classes you’ll need. The DbProviderFactory class exposes a series of methods that return these class instances, as listed in Table 1.

Table 1. DbProviderFactory Methods: The table lists the methods of the DbProviderFactory that return provider-dependent class instances.

You’ll see how to use some of these methods in the following example. Note, however, that there is no method to create instances of a DbDataReader. This is still an abstract class, and (as in ADO.NET version 1.x) you can create an instance only by executing the ExecuteReader method of a Command object. To declare a variable to reference a DbDataReader you use the IDataReader interface. You’ll see how this works in the final example in this article.

Specifying Connection Strings and Parameter Names
Unfortunately, there is another issue to contend with when writing provider-independent data access code. Although the code can automatically adjust to create the required provider-specific data access class instances, these classes have other dependencies. The data store connection strings differ for each provider, and the way that parameter naming behaves can also depend on the provider type. To assist you in creating the appropriate connection string, ADO.NET includes a new class in the System.Data.Common namespace named DbConnectionStringBuilder.

DbConnectionStringBuilder is rather like a cross between the StringBuilder and Dictionary classes. It exposes an Add method that takes two parameters, a key and a value. So, you can add the individual “name/value” pairs that are required in a connection string one at a time. Then you extract the complete connection string from the ConnectionString property of the DbConnectionStringBuilder. The following code creates a connection string suitable for use with the SqlClient classes, and then sets the ConnectionString property of a new DbConnection instance:

   ' create and populate a DbConnectionStringBuilder   Dim csb As DbConnectionStringBuilder = _      df.CreateConnectionStringBuilder()    csb.Add("Data Source", "localhost")   csb.Add("Initial Catalog", "AdventureWorks")   csb.Add("Integrated Security", "SSPI")      ' create a connection using this connection string   Dim conn As DbConnection = df.CreateConnection()    conn.ConnectionString = csb.ConnectionString

The second area where your code must cope with the variations in the behavior of the different data providers is in the names of parameters. Most providers use a “question-mark” (?) character as the placeholder for a parameter in a SQL statement. Parameters are passed by position?which means that the names of the individual parameters are irrelevant. However, the classes in the SqlClient namespace pass parameters by name, so you must ensure that your code can adapt to this.

The examples you see here use a Select Case statement (a switch statement in C#) that examines the invariant name of the selected provider and then?based on this?builds the correct connection string and a parameter name to suit the SQL statement that will be used later on. You can see all this in the next code fragment. As the machine used for this article does not have access to an Oracle database, and the standard Beta 1 installation has problems with the SqlServerCe provider, the code handles those two selections by simply displaying a message and exiting from the routine:

     ' create a builder for the connection string     Dim csb As DbConnectionStringBuilder = _        df.CreateConnectionStringBuilder()        ' fill in appropriate connection string details     ' and set parameter name (which is provider-specific)     Dim paramName As String = String.Empty        Select Case sInvariant       Case "System.Data.SqlClient"         csb.Add("Data Source", "localhost")         csb.Add("Initial Catalog", "AdventureWorks")         csb.Add("Integrated Security", "SSPI")         paramName = "@discount"       Case "System.Data.OleDb"         csb.Add("Provider", "SQLOLEDB")         csb.Add("Data Source", "localhost")         csb.Add("Initial Catalog", "AdventureWorks")         csb.Add("Integrated Security", "SSPI")         paramName = "?"       Case "System.Data.Odbc"         csb.Add("Driver", "{SQL Server}")         csb.Add("Server", "localhost")         csb.Add("Database", "AdventureWorks")         csb.Add("Trusted_Connection", "yes")         paramName = "?"       Case Else         ' cannot connect to other data sources so exit         output.Text = _            "Sorry, cannot extract rows using this provider."         Exit Sub     End Select     ...

Creating Connections and Commands
The next step is to create the data access object instances required. First you create the connection by calling the CreateConnection method of the DbProviderFactory. You obtain the appropriate connection string from the ConnectionString property of the ConnectionStringBuilder. But, because of the problem with the ConnectionStringBuilder in Beta 1 (as mentioned earlier), the code then implements a work-around by replacing the connection string if the user selected the ODBC provider:

     ...     ' create a connection using the new connection string     Dim conn As DbConnection = df.CreateConnection()     conn.ConnectionString = csb.ConnectionString        '*******************************************     ' fix for bug in ConnectionStringBuilder in Beta 1 --      ' fixed in Beta 2     If sInvariant = "System.Data.Odbc" Then       conn.ConnectionString = _       "Driver={SQL Server};Server=localhost;" & _       "Database=AdventureWorks;Trusted_Connection=yes"     End If     '*******************************************     ...

After displaying the connection string so that you can see what has been created, the code creates a command of the correct type using the CreateCommand method?but this time calls the method on the DbConnection that was previously created. This automatically uses the current connection, without having to specify it separately. Then the code specifies the parameterized SQL statement for the CommandText property, and sets the CommandType to CommandType.Text (although this is the default). Notice that the SQL statement uses the correct parameter name, as created in the Select Case statement in the previous sections of code:

     ...     ' display connection string     output.Text &= "Connection String: " & _        conn.ConnectionString & "
"        ' create a command from the connection and set properties     Dim cmd As DbCommand = conn.CreateCommand()     cmd.CommandText = "SELECT Description " & _        "FROM [Sales].[SpecialOffer]" & _        "WHERE DiscountPct > " & paramName     cmd.CommandType = CommandType.Text     ...

Next, you create the parameter itself. Here, the code uses the CreateParameter method of the current DbCommand instance, and sets the properties. This includes the parameter name, which is required if the selected provider is SqlClient (the name has no effect for other providers that pass parameters by position rather than by name). Even though the DBCommand creates the parameter, you must still remember to add it to the ParametersCollection of the command.

     ...     ' create a parameter and add to command     ' parameter name is only important for SqlClient     Dim param As DbParameter = cmd.CreateParameter()     param.ParameterName = "@discount"     param.DbType = DbType.Double     param.Value = "0.25"     cmd.Parameters.Add(param)     ...

Creating a DataAdapter and Retrieving Rows
Finally, the code creates a DbDataAdapter instance using the DbProviderFactory.CreateDataAdapter method, and sets the command just created as the SelectCommand property. Then, after creating a new DataSet to hold the incoming rows, it calls the Fill method of the DbDataAdapter and displays the rows returned from the database:

	?
Figure 4. Using the System.Data.OleDb Provider: The figure shows the page generated by selecting the OleDB provider, including the connection string, the parameterized SQL query, and the query results.

     ...     ' create DataAdapter and DataSet, and     ' fetch some rows using selected provider     Dim da As DbDataAdapter = df.CreateDataAdapter()     da.SelectCommand = cmd     Dim ds As New DataSet()     Try          da.Fill(ds, "Sales.SpecialOffer")          ' iterate through table rows and display the results       Dim disc As Double = _          Convert.ToDouble(cmd.Parameters("@discount").Value)       output.Text &=           "Products on sale with discount greater than " _          & disc.ToString("P") & ":
"       For Each dr As DataRow In ds.Tables(0).Rows         output.Text &= " - " & dr("Description") & "
"       Next        Catch ex As Exception       output.Text &= "
* ERROR: " & ex.Message     End Try

Figure 4 shows the results of selecting the OleDb provider. You can see that the invariant name is System.Data.OleDb, and that the code creates the appropriate connection string for this provider (note that the connection string is displayed only when running the page on the local machine?it is not displayed when you access the page remotely). The page also shows the SQL statement containing the “?” parameter placeholder, followed by the results of executing the statement.

	?
Figure 5. Using the System.Data.SqlClient Provider: The figure shows the page generated by selecting the SqlClient provider, including the connection string, the parameterized SQL query (including the parameter name as required by this provider), and the query results.

Figure 5 shows the results of selecting the SqlClient provider. In this case, the invariant name is System.Data.SqlClient, and the connection string uses the correct format for this provider. You can also see that the SQL statement now contains the name of the parameter, as required when using this provider.

Creating and Using a DataReader
As discussed earlier, you cannot create instances of a DbDataReader directly, as it is an abstract class. The technique for reading data through a DataReader, when using provider-independent code, is to declare the variable as of type IDataReader. The following code extract shows how you can modify the previous example to use a DataReader instead of a DataAdapter and DataSet:

   ' fetch some rows using selected provider and a DataReader.    ' Assumes that the command and parameters have already been    ' created, as in the previous example   Try     conn.Open()     Using reader As IDataReader = _        cmd.ExecuteReader(CommandBehavior.CloseConnection)       ' iterate through table rows and display the results       Dim disc As Double = _           Convert.ToDouble(cmd.Parameters("@discount").Value)       output.Text &= "Products on sale with discount " & _          "greater than " & disc.ToString("P") & ":
"       While reader.Read()         output.Text &= " - " & reader("Description") & "
"       End While     End Using   Catch ex As Exception     output.Text &= "
* ERROR: " & ex.Message   End Try

Alternatively, instead of the Using construct, you could use the more traditional approach?but remember to destroy the reader when the process completes:

   Dim reader As IDataReader = _      cmd.ExecuteReader(CommandBehavior.CloseConnection)

When Should I Use Provider-Independent Code?
To end this article, it’s appropriate to discuss when and where to consider using provider-independent code. Remember that it is only an option, and is not the way that you should approach all your data access requirements from now on. It is primarily designed as a feature for tool and IDE suppliers, rather than regular data access applications. However, you may want to consider it if your application needs to be configurable to run against different database systems on a regular basis.

Remember that, although you can dynamically choose the data access provider type, you also have to contend with the differences between the connection strings and parameter name requirements. However, the good news is that the performance of provider-independent code is pretty much equal to code that uses the equivalent provider-specific classes (such as SqlClient or OleDb). You do have to create the provider factory, but you should do this once and subsequently reuse the factory. After doing that, there is no performance overhead, because the factory object returns the base class itself.