A few years ago in one of the companies I worked for, we had an ADO.NET 1.0 application that used MS Access as its database system. We needed to port it to MS SQL Server and so we had to modify the source code. What we did was to write data provider specific code each for MS Access and MS SQL Server, similar to the following sample code. It worked but it was not an elegant solution.
If strDBMS = "MS SQL Server" Then
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = sqlCn.CreateCommand()
sqlCmd.CommandText = pSQL
Dim dr As SqlClient.SqlDataReader = sqlCmd.ExecuteReader()
Else
Dim oleDbCmd As OleDb.OleDbCommand
oleDbCmd = oleCn.CreateCommand()
oleDbCmd.CommandText = pSQL
Dim dr As OleDb.OleDbDataReader = oleDbCmd.ExecuteReader()
End If
Now, in my current company, I need again to port an application from one database system to another, this time from MS SQL Server to Oracle Database. Fortunately, there is now an easy way to write data provider independent code in ADO.NET 2.0 using the Provider Factory Model.
Provider Factory Model Overview
You can think of the provider factory as a generic namespace for different data-related objects such as the Connection, Command, and DataReader objects. Instead of declaring your objects using types under provider-specific namespaces such as System.Data.SqlClient or System.Data.OleDb, you will use the abstract base classes under the common namespace System.Data.Common.
The classes under System.Data.Common are however abstract so you cannot instantiate new objects from them. You will have to create a factory object that will provide concrete database objects. The DbProviderFactories class, also declared under System.Data.Common, provides a static method for creating these factory objects.
Sample Code
Here’s a VB.NET sample code for issuing a simple Select statement using the Provider Factory Model. In a real world application, you would have to specify the provider name and the connection string from a configuration or an ini file.
To run the sample code, create a Windows Forms Application project in Visual Studio, add a button to a form and associate the following code. Don’t forget to change the connection string to an appropriate one.
I have included comments to explain what the code does. If you have any questions about the sample code, please leave a comment to this article.
Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Provider name for Oracle
Dim myDataProviderName As String = "System.Data.OracleClient"
'Connection string
Dim myConnectionString As String = "Data Source=testdb;User Id=testuser;Password=testpassword;"
Try
'Create a DB Object Factory
Dim gDBFactory As DbProviderFactory = DbProviderFactories.GetFactory(myDataProviderName)
'Get a connection object from the factory
Dim sqlCn As DbConnection = gDBFactory.CreateConnection
'Open a DB connection
sqlCn.ConnectionString = myConnectionString
sqlCn.Open()
'Retrieve a row of data from a table
Dim sqlCmd As DbCommand = sqlCn.CreateCommand()
sqlCmd.CommandText = "SELECT field1 FROM table1 where field2 = 'abc'"
Dim dr As DbDataReader = sqlCmd.ExecuteReader()
dr.Read()
'Display the value of a field in the row
MessageBox.Show(dr.Item("field1"))
'Close the connection
sqlCn.Close()
sqlCn.Dispose()
Catch ex As Exception ' catches any error
MessageBox.Show(ex.Message.ToString())
Finally
'put clean-up code here in a real application
End Try
End Sub
End Class