Click here to Skip to main content
13,297,411 members (63,518 online)
Click here to Skip to main content
Add your own
alternative version


51 bookmarked
Posted 6 Sep 2006

Provider-Independent Code Simplified in ADO.NET 2.0

, 11 Sep 2006
Rate this:
Please Sign up or sign in to vote.
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.


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.


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

Sample screenshot

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:



DbProviderFactories.GetFactory (DataRow)Returns an instance of a DbProviderFactory.

DbProviderFactories.GetFactory (String)

Returns an instance of a DbProviderFactory.

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

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


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.

Gotcha! Be careful! SQL statements are not always the same between data providers. A query that works in SQL Server may not work for Access. If you have SQL statements in your code, you run the risk of breaking your applications when you switch database providers. Stored Procedures are a great way of additionally de-coupling your application from a specific data provider.

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!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Anthony Queen
Software Developer (Senior)
United States United States
I started my career in software development back in 2000. Prior to that, I made my living as a detail drafter. My true start in programming, though, goes back much further. I first started learning to program when I was about 10 years old. It was back in ‘82 that I wrote my first application. It was a simple calculator program written on a TRS-80 that my uncle had. Since then, I’ve programmed in Basic, QuickBasic, Pascal, C++, VB 6, VB.NET, Java, HTML, and C #. I have a very diverse background. I’ve worked and written software for several types of companies, including manufacturing, engineering, and finance. I’ve had the opportunity to design and maintain a few enterprise level databases, I’ve written applications to run on windows CE, in a wireless manufacturing environment. I’ve also had opportunities to teach OOP methodologies, and design patterns. I thoroughly enjoy what I do, and my only regret is that I didn’t start sooner.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
kero man22-Sep-12 9:13
memberkero man22-Sep-12 9:13 
GeneralFactory Class Not Perfect Pin
Katghoti12-Sep-06 7:43
memberKatghoti12-Sep-06 7:43 
GeneralRe: Factory Class Not Perfect Pin
Anthony Queen12-Sep-06 10:40
memberAnthony Queen12-Sep-06 10:40 
GeneralRe: Factory Class Not Perfect Pin
Katghoti12-Sep-06 12:02
memberKatghoti12-Sep-06 12:02 
GeneralRe: Factory Class Not Perfect Pin
Anthony Queen13-Sep-06 4:02
memberAnthony Queen13-Sep-06 4:02 
I took a look at the microsoft link. It doesn't have anything to do with .net 2.0.

• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft Visual Basic .NET 2003 Standard Edition
• Microsoft ADO.NET 1.0
• Microsoft ADO.NET 1.1

Also, the article was about "How to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider" and not about using the generic types, or the factory (which didn't exist prior to .NET 2.0 (VS 2005).

Are you using .NET 2.0?

It's only when you look at an ant through a magnifying glass on a sunny day that you realise how often they burst into flames.

AnswerRe: Factory Class Not Perfect Pin
Katghoti13-Sep-06 5:18
memberKatghoti13-Sep-06 5:18 
GeneralRe: Factory Class Not Perfect Pin
Anthony Queen14-Sep-06 6:48
memberAnthony Queen14-Sep-06 6:48 
GeneralRe: Factory Class Not Perfect Pin
Katghoti14-Sep-06 7:24
memberKatghoti14-Sep-06 7:24 
GeneralRe: Factory Class Not Perfect Pin
James_Lin27-Dec-06 12:07
memberJames_Lin27-Dec-06 12:07 
GeneralRe: Factory Class Not Perfect Pin
Polymorpher21-Sep-06 22:16
memberPolymorpher21-Sep-06 22:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171207.1 | Last Updated 11 Sep 2006
Article Copyright 2006 by Anthony Queen
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid