Provider-Independent code simplified in ADO.NET 2.0

• Download sourcecode

Introduction

This article will attempt to explain and demonstrate how Microsoft has simplified the approach to writing provider-independent code. Switching between Oracle, SQL, 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 (i.e. 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.

Dot 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 benefit 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 Dot Net 1.1, each of these objects (Connections, Adapters, etc�) implemented various interfaces (IDbConnection, IDbCommand, etc�). In Dot 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 independent (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. Exmples of �ProviderInvairantName� are:

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. Well 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 messagebox 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 datasources 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 (i.e. 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 example or this article, please let me know that too.

Thanks,

Tony

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