Provider-Independent Code Simplified in ADO.NET 2.0

• Download source code - 42.4 KB

Introduction

This article will attempt to explain and demonstrate how Microsoft has simplified the approach to writing provider-independent code. Switching between Oracle, SQL Server, or any OLEDB or ODBC data source is now as easy as changing a configuration setting.

Background

More often than not, developers write their applications against a specific data provider (e.g., SQL Server, Oracle, Access, etc...). There are times, however, when an application has to be able to switch from one data provider to another. This can be pretty painful if you haven't planned ahead.

Enlightened developers will oftentimes implement their own abstract factories and insulate their applications from having to know exactly where the data is coming from. When done right, this approach is a solid solution to a common problem.

.NET 2.0 has simplified the process by implementing this approach right in the framework. Now it is easier than ever to create robust, provider-independent applications.

What is an Abstract Factory?

For the sake of those who may not know, it may be beneficial to very quickly review the Abstract Factory pattern. For those of you in the know, feel free to skip this section.

The purpose of an abstract factory is to provide an interface for creating families of related objects, without specifying their concrete classes. It is a creational pattern. When we write code to use the interface exposed by an abstract factory, we decouple our code from the specific factory or factories that create the objects we wish to consume. This allows us to define multiple factories that produce different objects meant for different situations. In the case of this article, the concrete factories create various objects for specific data providers.

If you would like more information about the abstract factory pattern, the internet is a great place to find more information. At the time of this writing, there were 265,000 hits returned by www.google.com for the search term "Abstract Factory".

The DbProviderFactory Class

The DbProviderFactory class is an abstract class. It must be inherited. This class provides a way of insulating your application from having to know which factory you are going to use. For example, your application has a reference to an object that is of the type DbProviderFactory. That factory could be any class that inherits from DbProviderFactory, such as an OleDbFactory, OdbcFactory, SqlClientFactory, or OracleClientFactory. Since you haven't coded to a specific factory, your application doesn't care.

A factory has to have a product; otherwise it wouldn't be a factory, right? So, what do these factories produce? DB objects!

Each of these factories contains methods that allow you to create connections, commands, parameters, adapters, etc... Each factory, however, only produces related objects. That is, an OleDbFactory only produces OLEDB objects, OdbcFactory only produces ODBC objects, and so on.

The trick now is to write your application in such a way that it doesn't matter which "family" of objects you are using. How do you do that?

In .NET 1.1, each of these objects (Connections, Adapters, etc.) implemented various interfaces (IDbConnection, IDbCommand, etc.). In .NET 2.0, these objects all inherit from abstract base classes (DbConnection, DbCommand, etc.). So, instead of coding to a specific implementation of a command or connection object, you can code to these abstract classes. This way, your code doesn't have to know what database provider you need to use.

That's pretty powerful.

Here is a basic diagram of how Microsoft implemented the Abstract Factory for provider independence (not all classes or members are shown):

Getting the Right Factory

So, how do you specify what factory to use? Well, Microsoft has provided us with a static class to create the factory for us. This class is named DbProviderFactories, and also resides in the System.Data.Common namespace. Since it is static, you don't need to create an instance of the class in order to access it.

One of the functions of this class is GetFactory.

There are two overloads for this function:

Both methods will return a concrete factory for the specified provider. Changing factories can be as simple as changing the ProviderInvariantName in a configuration file, retrieving that setting, and passing it to the GetFactory(String) method. Examples of ProviderInvairantName are:

• System.Data.Odbc
• System.Data.OleDb
• System.Data.SqlClient

Please note that these names are case-sensitive!

Once you have a factory, then anytime you need a provider specific object, you just call the Create method for the object you need. We will see how this is actually done in the following code snippets. You can also download the example application to see how this all works.

The Example Application

The example application very simply demonstrates the Provider Factory in action. When you click the buttons, objects of the specified type (in this case, OLEDB or ODBC) are created. A message box then pops up to verify that objects of the correct type have been created.

Reviewing the Code

In the example application, you will see a DataGridView and two Buttons. The DataGridView will contain a list of all data sources installed on your machine. To get the data for the grid, we first have a member variable named Providers:

Private Providers As datatable

We will then fill the table with the appropriate data. We do this by calling the GetFactoryClasses function in the DbProviderFactories class. All of this is done when the form loads:

Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

    'Get a datatable from the GetFactoryClasses function
    Providers = System.Data.Common.DbProviderFactories.GetFactoryClasses
    DataGridView1.DataSource = Providers

End Sub

The two buttons, when clicked, will create a DbFactory specific to the data provider indicated in the button text. For OLEDB, the code looks like this:

Private Sub cmdGetOleDB_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) _
           Handles cmdGetOleDB.Click

    Dim dbFactory As Data.Common.DbProviderFactory

    'Get an instance of the concrete implementation for this provider.
    dbFactory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb")

    'Pass the factory on so we can create some objects
    DemonstrateFactory(dbFactory)

End Sub

Afterwards, the specific factory is passed to another function. This function is responsible for creating various objects specific to the data provider selected. After all the objects have been created, we will pop up a message box to indicate what type each object really is.

Private Sub DemonstrateFactory(ByVal Factory As System.Data.Common.DbProviderFactory)

    'declare a few common items using the base classes for each type.
    Dim Connection As Data.Common.DbConnection
    Dim DA As Data.Common.DbDataAdapter
    Dim Command As Data.Common.DbCommand
    Dim Parm As Data.Common.DbParameter

    'Let the factory return concrete instances of each dbobject
    Connection = Factory.CreateConnection
    DA = Factory.CreateDataAdapter
    Command = Factory.CreateCommand
    Parm = Factory.CreateParameter

    ' Here we get the type of each object so we can display 
    ' it in a msgbox.
    Dim str As String
    str = Connection.GetType.ToString & vbCrLf
    str = str & DA.GetType.ToString & vbCrLf
    str = str & Command.GetType.ToString & vbCrLf
    str = str & Parm.GetType.ToString & vbCrLf

    MsgBox(str)

End Sub

In Conclusion...

By putting a little forethought into the design of your applications, you can write very extensible code. The trick here is to always reference the generic base-class versions of the objects you need in code (e.g., DbConnection, DbDataAdapter, DbCommand, etc...) and avoid directly referencing their concrete implementations. By doing this, you de-couple your application from having to know which implementation you're going to use.

Please Note...

This is by no means meant to be a complete solution. Many times, there is more than one way to solve a problem. Hopefully, though, this article has proven beneficial. Perhaps this article has given you some great ideas about a better way to do this. Great! That's why I wrote it. Please feel free to leave some feedback. Let me know how it went for you. If you have some ideas on how to do this another way, or how to improve the examples or this article, please let me know that too.

P.S. Don't forget to vote! If you don't have an account, make one!