Data Provider Factories in .NET 2.0
One of the interesting
new features in ADO.NET 2.0 are the DbProviderFactory
classes and the related Db* abstract base classes. These new classes make it a
lot easier to create database agnostic code. In this article I will show how to
do this and some of the issues that sill remain to be solved.
What are Data Provider Factories
A data provider class
is an object factory that is used to create a set of related object like
SqlConnection and SqlCommand. Once the proper data provider factory object to
use has been determined all the remainder of the application has to do is use
this object to create all other associated objects.
Factory object
functions always have a return type of an abstract base class, for example: DbConnection instead of SqlConnection. This is because the
use of a factory object means we will not know the exact type of object used
until runtime. The new provider factory classes themselves are for the same
reason also derived from an abstract base class, System.Data.Common.DbProviderFactory.
A new class is derived from this abstract base class per supported database
type. Examples are SQL Server, OldDB, ODBC and
Oracle. The DbDataProvider class defines a number of
abstract functions that these subclasses need to implement.
|
CreateComand() |
Create a from DbCommand derived command
object. |
|
CreateCommandBuilder() |
Create a from DbCommandBuilder derived object. |
|
CreateConnection() |
Create a from DbConnection derived
object. |
|
CreateConnectionStringBuilder() |
Create a from DbConnectionStringBuilder
derived object. This is a completely new class in the 2.0 framework. |
|
CreateDataAdapter() |
Create a from DbDataAdapter derived object. |
|
CreateDataSourceEnumerator() |
Create a from DbDataSourceEnumerator derived
object. This is a completely new class in the 2.0 framework. |
|
CreateParameter() |
Create a from DbParameter derived object |
|
CreatePermission() |
Create a from CodeAccessPermission derived
object. |
Table 1: The DbDataProvider functions.
Once the appropriate DbProviderFactory has been made the function in table 1 can
be used to create all the appropriate objects instead of the New
operator as would be done in older code. To determine the all available DbProviderFactory classes and to create these an additional
class, the System.Data.Common.DbProviderFactories, is
provided.
|
GetConfigTable() |
Returns a DataTable object
with a row for each registered DbProviderFactory. |
|
GetFactory() |
Creates a DbProviderFactory derived
object based on the specified name of DataRow. This function is overloaded so it can be
called with either the provider name of a row from the DataTable returned by
the GetConfigTable() function. |
Table 2: The DbProviderFactories functions.
To determine all
registered DbProviderFactories for an application a lookup action is done in
the machine.config and the applications app.config for a <DbProviderFactories> section. These
two sections are combined and provide the basis for the DataTable returned by
the GetConfigTable() function.
The system machine.config contains entries for all DbProviderFactories
installed on the system. The applications config.app
makes it possible to add or remove elements on per per
application basis. A app.config
<clear/> element can be used to hide all DbProviderFactories
registered in the machine.config. More likely
individual providers can be removed. Esing a <remove invariant="Microsoft.SqlServerCe.Client"></remove>
element removes the SQL Server CE entry. The invariant attribute is required
and serves as the key in the collection. Note that this only prevents the table
from showing up in the DbProviderFactories.GetConfigTable() return value but will not prevent the application code
from explicitly creating an objectof this type and
using it. Besides removing entries it is also possible to add a new provider
using the “add” element.
<add name="The
Problem Solver Data Provider"
invariant="TheProblemSolver.Data.SqlClient"
description=
"The Problem Solver.Net Framework
Data Provider for SqlServer"
type="TheProblemSolver.Data.SqlClient.SqlClientFactory,
TheProblemSolver.Data,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089" />
Listing 1: Adding
an application specific DbProviderFactory.
The ShowProviders form in the sample code shows all, for the
application, known DbProviderFactory classes. If you
take a look at the config.app you can see that the Microsoft.SqlServerCe.Client provider has been deleted and
the TheProblemSolver.Data.SqlClient provider has been
added. This last DbProviderFactory doesn’t actually
exist anywhere in the code and is just an example of how to add one.
Figure 1: The
list of DbProviderFactory classes on my machine.
Why where the new abstract classes
added?
In version 1 of the .Net framework the ADO.NET classes where not based upon a common baseclass but implemented a set of interfaces. The class hierarchy for the SQLDataReader class was:
· System.Data.SqlClient.SqlDataReader
· System.MarshalByRefObject
· System.Object
To enable the creation
of generic code the SQLDataReader class also implements the System.Data.IDataReader
interface. However this approach has one big drawback in the fact that an
interface cannot be changed once it has been published. This means that the
inevitable changes cannot be made to the interface itself after its
publication. Result is that new functions cannot be added. An example of this
is the HasRows property that was added to the
SQLDataReader, and all related DataReaders. This new property isn’t usable from
generic code as it isn’t available in the IDataReader interface. To solve this
problem the decision has been made to add a new abstract class to the class
hierarchy. This results in the following new class hierarchy for the SQLDataREader version 2.0:
· System.Data.SqlClient.SqlDataReader
· System.Data.Common.DbDataReader
· System.MarshalByRefObject
· System.Object
Why a published interface cannot be
changed:
When a published
interface is changed all developers that implement this interface and ship
components have a problem. Once this happens they must implement and compile a
separate version of their code per version of the interface. The reason for
this is that you can compile an assembly on the old version of the framework,
with the first version of the interface, but use this assembly in the newer
version of the framework with the new interface. The compiler will allow calls
to all functions as defined in the interface, including the new one, even
though the actual class doesn’t implement this. The result is a pretty dramatic
fatal error. In the case of a base class all that will happen is that the base
implementation of the new function will be called.
Database independent code, a simple example
Now
that we have had a quick look at the new classes its time to put them to use. As a first and very simple example I have
created a form that uses a database connection string and SQL statement and
tries to retrieve the data using the different providers. I have provided three
sample for the connection string, one for the SQL Northwind database using the SQLConnection,
another for the same database but then using the OleDb
provider and finally another OleDb connection string
to provide the same data from the Nothwind database
that ships with Visual FoxPro. The SQL statement used is a very simple “Select * From
Customers”.
Figure 2: The
customers table from SQL Server.
The code start of
trying to open a database connection using all registered DbProviderFactory
classes. See the code below in listing two.
Dim factory As DbProviderFactory = Nothing
Dim conn As DbConnection = Nothing
Dim cmd As DbCommand = Nothing
Dim
dtFactories As DataTable =
DbProviderFactories.GetFactoryClasses()
For Each drFactory As
DataRow In dtFactories.Rows
Try
factory =
DbProviderFactories.GetFactory(drFactory)
conn = factory.CreateConnection()
conn.ConnectionString = cboConn.Text
conn.Open()
conn.Close()
Exit For
Catch ex As Exception
conn = Nothing
factory = Nothing
End Try
Next
Listing
2: Determining the correct DbProviderFactory to use.
The code first calls
the DbProviderFactories.GetFactoryClasses() to retrieve a DataTable with all possible data providers.
Next we try them all by creating a connection object and opening the connection
to the database. If this fails an exceprion is
raised. Please note that I test for the very generic Exception instead of a
more specific DbException. The reason for this is
that a number of reasons might cause this connection to fail, like an unknown
assembly, and not all raise a DbException. If opening
the connection succeeds we end up a local variable “factory” that
points to the DbProviderFactory used by the
rest of the code.
Figure 3: The
same customers table but now from Visual FoxPro using the OleDb
provider.
Once we have
determined the correct data provider we can proceed and create DbCommand and a DbDataAdapter
objects and use these to fill a DataTable. The code
below does just that. To indicate the DbProviderFactory
used an extra TextBox is updated with the class name
of the DbProviderFactory used.
If
conn IsNot Nothing AndAlso factory IsNot Nothing Then
txtProvider.Text = factory.ToString()
cmd = factory.CreateCommand()
cmd.CommandText = txtSQL.Text
cmd.Connection = conn
Dim da As DbDataAdapter
da = factory.CreateDataAdapter
da.SelectCommand = cmd
da.Fill(dtData)
Else
txtProvider.Text = "Geen
provider gevonden"
End If
dgvData.DataSource =
dtData
Listing
3: Using the DbProviderFactory object.
The code above show
just how easy it is to switch database provider based on just a connection
string.
Database independent code, the
complete story
The previous example
shows how easy it is to get started. However in practice things aren’t quite as
simple. The first problem is in the SLQ language itself and more specifically
the fact that every database server has a slightly different syntax. To solve
this problem we have a choice of three possible approaches:
1.
Use only
standard ANSI SQL, a syntax that almost every database
server supports, and remove the unwanted providers using the <DbProviderFactories> section of the config.app.
2.
Create a
class that can parse the SQL syntax and convert the database specific parts to
the required dialect.
3.
Add a
system that allows developers to introduce database server specific queries
where needed.
Option 1 is the
simplest solution by far but is very restrictive as to what can be achieved.
The main reason for this is that only the common syntax ban be used. Option 2
is very nice but very complicated and a lot of work. Once developed this might
be sold as a product by itself. Option 2 is, in most cases at least, the most
practical. In this case developers just create multiple SQL statements per
dialect where needed.
Whatever option one
chooses an additional problem remains and that is the problem of SQL parameters
as only very small and simple systems can be build using non parameterized
queries. Some providers, like OleDb and ODBC, require parameters to be indicated by a “?”
and a matching order of the parameter objects in the parameters collection.
Other providers, like SQL Server and Oracle, use named parameters like @CustomerId or :CustomerId.
In this case the order in the parameters collection is not important.
I have include a class in the code samples that makes a start with
solving these problems. The name of the class is CommandBuilder
and is used in the sample form ShowDataParameters. To
use the class first create an object using the required connection string as a
parameter for the constructor. The constructor calls the GetFactory() function to
create the DbProviderFactory object as previously
described. After this has been done the CommandBuider
is created to create and execute SQL commands.
Figure 4: A SQL
select with parameters.
The cmdLoad_Click()
function in the sample form is used to load the data. First a CommandBuilder object is created using the connection
string as was previously described. Next the parameter collection is
constructed. When writing this code we are still unaware of the type of
provider and therefore the kind of parameter collection to use. For that reason
I use a HashTable instead and fill it with the
parameters. This was we have a generic key/value collection that allows for
quick searches.
Private Sub cmdLoad_Click(ByVal
sender As System.Object, _
ByVal e
As System.EventArgs) Handles
cmdLoad.Click
Dim builder As New
CommandBuilder(cboConn.Text)
Dim cmd As
DbCommand
Dim da As
DbDataAdapter
Dim dt As
New DataTable
Dim params As New Hashtable
params.Add("@Country",
"
params.Add("@Region",
"WA")
cmd = builder.CreateCommand(txtSQL.Text, params)
txtExecuted.Text = cmd.CommandText
da = builder.CreateDataAdapter()
da.SelectCommand = cmd
da.Fill(dt)
dgvData.DataSource = dt
End Sub
Listing 4: Thee cmdLoad_Click()
function.
When building the SQL
command itself we write the parameters as if we where using SQL Server and let
the CommandBuilder object change this if needed.
Please note that for test purposes the order of parameters in de SQL Command
and HashTable has been reversed.
Select * From Customers
Where Region = @Region and Country = @Country
Listing 5: The SQL command.
A DbCommand
object is constructed by calling the CreateCommand
function with the CommandBuilder and the SQL command as
parameters. The CreateCommand function first calls
the CreateCommand function of the DbFactory
object to create the real DbCommand and the CreateConnection for the read DbConnection
objects. Up to here nothing new. Next the CommandBuilder.FixCommandText
function is called to correct the syntax of the SQL command and create and fill
the matching parameters collection.
Public
Function CreateCommand(ByVal sql As String, _
ByVal params As Hashtable) As DbCommand
Dim cmd As DbCommand = Nothing
cmd = m_Factory.CreateCommand()
cmd.Connection = CreateConnection()
cmd.CommandText = FixCommandText(sql,
cmd.Parameters, params)
Return cmd
End
Function
Listing
6: The CreateCommand function.
The FixCommandText function is where most of the actual work
occurs. First we check what kind of DbFactory is
being used. Unfortunately information like the positional or named usage of
parameters and the prefix character isn’t available as properties on the DbFactory object. So we need to take a look at the kind of DbFactory used and figure these out ourselves. Once
determined we have a choice between positional or named parameters. If we use
positional parameters a regular expression object is used to locate all
parameters in the original SQL string. Once a parameter has been located it is
replaced by a “?” and we locate the parameter value in the HashTable
so we can add it to the DbParameters collection. In
the case of named parameters things are somewhat easier and all we need to do
is change the original “@” to the correct character for the database server in
use.
Private Function FixCommandText(ByVal
sql As String, ByVal dbParams As
DbParameterCollection, ByVal params As Hashtable) As String
Dim newSql As String = sql
Dim
paramPrefix As String
= ""
Dim
positional As Boolean
= True
Dim
paramName As String
Dim param As DbParameter
Dim re As New Regex("@\w+")
Dim match As Match
If TypeOf m_Factory Is _
System.Data.SqlClient.SqlClientFactory
Then
positional = False
paramPrefix = "@"
ElseIf TypeOf m_Factory Is _
System.Data.OracleClient.OracleClientFactory Then
positional = False
paramPrefix = ":"
Else
positional = True
paramPrefix = "?"
End If
If
positional Then
match = re.Match(newSql)
Do While match.Success
newSql = newSql.Substring(0,
match.Index) + _
paramPrefix + _
newSql.Substring(match.Index +
match.Length)
If
params.ContainsKey(match.Value) Then
paramName = match.Value
param =
m_Factory.CreateParameter()
param.Value = params(paramName)
' Is
ignored but what the heck
param.ParameterName = paramName
dbParams.Add(param)
End
If
match = re.Match(newSql)
Else
Dim
startAt As Integer
= 0
match = re.Match(newSql, startAt)
Do While match.Success
newSql = newSql.Substring(0,
match.Index) + _
paramPrefix + _
newSql.Substring(match.Index +
1)
startAt = match.Index +
match.Length
match = re.Match(newSql, startAt)
For Each paramName In
params.Keys
param = m_Factory.CreateParameter()
param.Value = params(paramName)
param.ParameterName = paramPrefix +
paramName.Substring(1)
dbParams.Add(param)
Next
End If
Return
newSql
End Function
Listing 7: Thee FixCommandText function.
All that remains after
we have created the correct DbCommand object is to
create a DbDataAdapter object to fill the DataTable. For this purpose a CreateDataAdapter
function is called that does nothing more than call the DbFactory
CreateDataAdaptor and return the result.
This example is not
intended as production code, only to show a possible solution when trying to
write database independent code.
Conclusion
The new Data Provider
Factories and related base classes make writing database independent code
easier than it was using previous versions of the .Net framework. Unfortunately
the differences per provider and database server still leave a number of issues
to be solved. This article is base on beta 2 of the .Net framework so the
possibility remains that a number of these issues will be solved before the
final release.