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 www.google.com for the search term "Abstract Factory".
The DbProviderFactory Class
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
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 (
IDbCommand, etc.). In .NET 2.0, these objects all inherit from abstract base classes (
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
There are two overloads for this function:
|Returns an instance of a
|Returns an instance of a
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
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
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
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
Providers = System.Data.Common.DbProviderFactories.GetFactoryClasses
DataGridView1.DataSource = Providers
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) _
Dim dbFactory As Data.Common.DbProviderFactory
dbFactory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb")
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)
Dim Connection As Data.Common.DbConnection
Dim DA As Data.Common.DbDataAdapter
Dim Command As Data.Common.DbCommand
Dim Parm As Data.Common.DbParameter
Connection = Factory.CreateConnection
DA = Factory.CreateDataAdapter
Command = Factory.CreateCommand
Parm = Factory.CreateParameter
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
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.,
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.
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!