The Provider Factory in ADO.NET 2.0

Different database products have different providers. If the connection string and the provider can be disassociated, then the provider information can be stored in a central location such as the Machine.config file. This came into being with ADO.NET 2.0. Since there are many providers (MS SQL, Oracle, SQL Anywhere, and so on), they form a collection which can be interrogated at run time and modified at runtime. The provider factory object then provides support to create connections, commands, and data adapters that are independent of the underlying database. This tutorial looks at the abstract provider factory as described in ADO.NET 2.0.

What is the Provider Factory?

In order to get a basic understanding of the enhanced architecture of ADO.NET let us start a new Asp.NET web site project, Factory in VS 2005 using the installed template as shown in the next picture.

Since the Provider Factory is a generic model applicable to all data sources, it is found in the System.Data.Common namespace. After setting the reference to this by using the imports statement, the DbProviderFactories object's properties and methods can be seen in the object browser as shown in the next picture. This shows that you can access the DbProviderFactory using the GetFactory() methods. In the next section we will look at all the DbProviderFactories available on this machine.

Data Providers on This Machine

In order to find a specific DbProviderFactory on this machine let us place a button and a textbox and use the GetFactory(string) method as shown from its definition in the object browser (copied from the ObjectBrowser in VS 2005).

GetFactory(string) As SYStem.Data.Common.DbProviderFactory

Public Shared Function GetFactory(ByVal providerInvariantName As String)

As & _
System.Data.Common.DbProviderFactory
Member of: System.Data.Common.DbProviderFactories
Summary:
Returns an instance of a System.Data.Common.DbProviderFactory.
Parameters:
providerInvariantName: Invariant name of a provider.
Return Values:
An instance of a System.Data.Common.DbProviderFactory for a specified 

provider name.

All providers have what is called a providerInvariantName. This GetFactory() method needs to be called by providing a providerInvariantName as a string. To the click event of the button add the following code. It is assumed that you have the imports statements (System.Data and System.Data.Common) in place.

 Protected Sub Button2_Click(ByVal sender As Object, ByVal e As 

System.EventArgs) Handles Button2.Click
Dim strg As System.Data.Common.DbProviderFactory
dstrg = Data.Common.DbProviderFactories.GetFactory

("System.Data.SqlClient")
TextBox1.Text = dstrg.ToString
End Sub

The above code returns the following as the reference to the DbProviderFactory.

More Data Providers on This Machine

System.Data.SqlClient.SqlClientFactory

To find all the different providers on this machine the GetFactoryClasses must be interrogated. This method is described as follows (copied from the ObjectBrowser in VS 2005):

GetFactoryClasses() As System.Data.DataTable

Public Shared Function GetFactoryClasses() As System.Data.DataTable
Member of: System.Data.Common.DbProviderFactories
Summary:
Returns a System.Data.DataTable that contains information about all 

installed providers 
that implement System.Data.Common.DbProviderFactory.
Return Values:
Returns a System.Data.DataTable containing System.Data.DataRow objects 

that contain the 
following data. Column ordinalColumn nameDescription 0NameHuman-readable 

name for the data 
provider.1DescriptionHuman-readable description of the data provider.

2InvariantNameName 
that can be used programmatically to refer to the data provider.

3AssemblyQualifiedNameFully 
qualified name of the factory class, which contains enough information 

to instantiate the 
object.

As the return value comes out in rows of data in three columns each, place a command button, a textbox and a GridView control on the form, and to the click event of the button insert the following code.

Protected Sub Button1_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Button1.Click
'since we know the returned info is in a table format we start out 
'with a table
Dim dtable As New DataTable
'GetDataClasses() is without any arguments
dtable = Data.Common.DbProviderFactories.GetFactoryClasses
GridView1.DataSource = dtable
GridView1.DataBind()
GridView1.Visible = True
Dim drfow As System.Data.Common.DbProviderFactory
drfow = Data.Common.DbProviderFactories.GetFactory(dtable.Rows(1))
TextBox2.Text = drfow.ToString
End Sub

When the button is clicked all the different providers are displayed in the GridView as shown in the next picture. The last two providers are not from an out-of-the box install of VS 2005. These were automatically added when Jasper (SQL Anywhere 10) was installed on this machine.

The textbox shows the variable drfow in the following paragraph corresponding to the second row.

It was noted that, although the individual columns for any row should be accessible, the program throws an exception for any value other than for ColumnIndex=2 for no apparent reason.

Creating Provider Independent Data Access

The DbProviderFactory object gives you an indication of how you may use it for the purposes of writing a generic data access program, which may be connected to the provider of your choice at run time, as shown in the next picture.

As the code in the next paragraph shows, once you get an instance of the DbProviderFactory, you can create a connection using the CreateConnection() method. Similarly you can create the DbDataAdapter using the CreateDataAdapter method; DbParameter using CreateParameter(); and DbDataCommand using CreateCommand(). All this can be set at run time passing the strings Provider, ConnectionString, and CommandText.

The following code does not show all this; it shows the connection being opened with a connection string taking strings from textboxes and using the factory model (the CreateConnection() function is highlighted in the code).

Imports System.Data
Imports System.Data.Common
Partial Class Generic
Inherits System.Web.UI.Page
Private connstrg As String
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As 

System.EventArgs) Handles Button1.Click
Dim dstrg As System.Data.Common.DbProviderFactory
dstrg = Data.Common.DbProviderFactories.GetFactory(TextBox1.Text)
Dim conn As Data.Common.DbConnection = dstrg.CreateConnection() 

conn.ConnectionString = TextBox2.Text
conn.Open()
Response.Write("open")
conn.Close()
End Sub
End Class

When the button is clicked the above code runs and you will see the following display. As long as you insert proper values into textbox1 and textbox2 which are compatible, the connection opens. In the example shown the boxes are hard coded, but it is possible to have drop-downs from which you may make connections to anticipated data sources. In fact the whole process of data retrieval can now be written in a generic format since all objects are derived from the DbProviderFactory instance. If you need to connect to the other providers look up the table of providers shown earlier.

Summary

The Provider Factory Model will be useful for writing provider independent data access code. It must be realized that the ConnectionString as required by this model should not contain the keyword "Provider" (see textbox2's text). The password has been masked in the image but it was in clear text.