Writing Provider-Independent Data Access Code with ADO.NET 2.0

Note: the code described in this article is based on the Beta 1 release of the .NET Framework v 2.0. All the examples can be downloaded from http://www.daveandal.net/articles/provider-independent/, and you can also run them online from the same location.

One of the ways that the data access features in .NET changed the way developers write code was the segregation of the data providers into separate classes and namespaces, depending on the type of database that was being targeted. If you want to access SQL Server, you are encouraged 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, this code won't work if you need to switch over to using a different database system. For example, you may need to change to using classes in the OleDb or Odbc namespaces instead. Of course, you can do a global search and replace, but this is no use 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 exposed and enhanced the base classes in the System.Data.Common namespace, as well as adding some new classes that directly enable the implementation of provider-independent data access techniques. This article looks at this feature, and demonstrates how you can create tools, development environments and applications that allow the provider type to be changed 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 if 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 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 that 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, the new provider factory model allows you to create instances of these classes that are provider-specific using code, rather than having to write code that accesses specific provider-dependent subclasses.

Figure 1 - The Factory Class Hierarchy in ADO.NET 2.0

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 you can enumerate the providers that are available, 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, 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 select the required provider when the application is installed, or when the database system is changed. 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 listing below shows an (abridged) section 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.

<system.data>

<add name="SqlClient Data Provider"

invariant="System.Data.SqlClient"

support="FF"

description=".Net Framework Data Provider for SqlServer"

type="System.Data.SqlClient.SqlClientFactory, System.Data,

Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" />

<add name="Odbc Data Provider"

invariant="System.Data.Odbc"

support="BF" description=".Net Framework Data Provider for Odbc"

type="System.Data.Odbc.OdbcFactory, System.Data,

Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" />

... more providers here ...

</DbProviderFactories>

</system.data>

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()

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:

<asp:GridView id="grid1" runat="server" enableviewstate="false" />

Remember that, as well as the System.Data namespace, you must also import the System.Data.Common namespace, as this is where the DbProviderFactories class is implemented. However, you don’t need any of the provider-dependent classes (such as SqlClient):

<%@Import Namespace="System.Data" %>

<%@Import Namespace="System.Data.Common" %>

Figure 2 - Enumerating the Installed Data Providers

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 get 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 that is returned by the GetFactoryClasses method.

You can use either one of these methods. 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 appSettings section:

</appSettings>

</configuration>

Then you can create an instance of the required data factory using the following code. Notice that, as only one instance of the data factory is required (irrespective of 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. This is done by declaring it 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 if 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. This prevents an exception being raised 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 they provided does exist in one of the rows of the DataTable returned by the GetFactoryClasses method, and the easiest way is to try and select this row in the DataTable. The Select method returns an array of matching DataRow instances, and 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:

<asp:GridView id="grid1" runat="server"

BorderWidth="1px" ForeColor="Black" BackColor="White"

CellPadding="4" BorderStyle="None" BorderColor="#dedfde"

GridLines="Vertical" AutoGenerateColumns="False"

OnRowCommand="GetSomeDataRows">

<SelectedRowStyle ForeColor="White" Font-Bold="True"

BackColor="#CE5D5A" />

<asp:CommandField ShowSelectButton="True" SelectText="Select"

ButtonType="Button" />

<asp:BoundField DataField="InvariantName"

HeaderText="Invariant Name" />

<asp:BoundField DataField="Description"

HeaderText="Description" />

</Columns>

</asp:GridView>

This GridView is populated in the Page_Load event handler 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 listing) 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

Obviously, in an ASP.NET application, you could avoid recreating the DataTable on each postback. The code could cache the DataTable between requests (it could be stored in the Application or in the ASP.NET Cache, as it is the same for all users of the application). Alternatively, you could use the invariant name in the GetFactory method, as described earlier, by extracting it from the selected row. However, the GetFactory method is not particularly resource-intensive, and so caching the DataTable it returns is probably not worthwhile.

Figure 3 - The invariant name and description of the five installed data provider factories

Each Select button will execute the routine named GetSomeDataRows, which is specified for the OnRowCommand attribute of the GridView control. The RowCommand event is passed 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 listing 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

Now that you've got 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 - Methods of the DbProviderFactory that return provider-dependent class instances

Method	Description
CreateConnectionStringBuilder()	Returns an instance of a DbConnectionStringBuilder that you can use to create connection strings dynamically.
CreateConnection()	Returns an instance of a DbConnection that you can use to connect to a data store. The DbConnection class exposes a method CreateCommand() that returns a new DbCommand instance. You can use this instead of the DbProviderFactory.CreateCommand() method to create a command for that connection.
CreateCommand()	Returns an instance of a DbCommand that you can use to execute SQL statements and stored procedures. The DbCommand class exposes a method CreateParameter() that returns a new DbParameter instance. You can use this instead of the DbProviderFactory.CreateParameter() method to create parameters for that command.
CreateParameter()	Returns an instance of a DbParameter that you can use to pass values into and out of SQL statements and stored procedures.
CreateCommandBuilder()	Returns an instance of a DbCommandBuilder that you can use to create the UPDATE, INSERT and DELETE SQL statements for a DataAdapter automatically.
CreateDataAdapter()	Returns an instance of a DbDataAdapter that you can use to fill or update a DataSet or DataTable.
CreateDataSourceEnumerator()	Returns an instance of a DbDataSourceEnumerator that you can use to examine the data sources available through this DbProviderFactory instance.
CreatePermission(PermissionState)	Takes a value from the PermissionState enumeration and returns an instance of a CodeAccessPermission that you can use to ensure that callers have been granted appropriate permission for all the objects they require access to.

You'll see how some of these methods can be used 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) can only be created as a result of executing the ExecuteReader method of a Command. 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 behaviour 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, and 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, and 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 listing. As the machine we're using does not have access to an Oracle database, and there are problems with the standard Beta 1 installation of the SqlServerCe provider, these two selections are handled simply by 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 is the connection, which is returned from the CreateConnection method of the DbProviderFactory, and the appropriate connection string is obtained 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 ODBC provider was selected:

...

' 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

...

The next step is to 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 parameter is created from the DbCommand, you still have 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 an instance of a DbDataAdapter, using the CreateDataAdapter method of the DbProviderFactory, 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:

...

' 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") & " "

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 the appropriate connection string is created for this provider (note that the connection string is only displayed when running the page on the local machine - it is not displayed when you access the page remotely). The SQL statement is also shown, containing the "?" parameter placeholder, followed by the results of executing this SQL statement.

Figure 4 - Using the System.Data.OleDb provider

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.

Figure 5 - Using the System.Data.SqlClient 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

Or, instead of the Using construct, you could use the more traditional approach - but remember to manage destroying the reader when complete:

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 the use of provider-independent code should be considered. 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 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 this should be done once and then re-used. After that, there is no layer of overhead, because the base class itself is returned from the factory object.