DevCity.NET - http://devcity.net
Create generic database code using ADO.NET 2.0 Provider Factories
http://devcity.net/Articles/279/1/article.aspx
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 
by David Jeavons
Published on 10/17/2006
 

ADO.NET 2.0 introduces many new features and enhancements over previous versions of ADO.NET, but one new feature that has caught my eye recently is the new DBProviderFactory class which allows you to write generic database code without too much difficulty.

In this article, I will demonstrate a simple application that can communicate with both the Northwind Microsoft Access database and the Northwind SQL Server database without changing a single line of code.  OK, that may be a bit far fetched, but in all honesty, the only piece of information you would need to realistically change would be in the configuration file of your application to state which database your application is to use.


The System.Data.Common Namespace

The new DBProviderFactory class lives in the System.Data.Common namespace. Within this Namespace you will see the usual suspects such as DbConnection, DbDataAdapter, DbCommand etc., but note how they are all prefixed with Db. Each of these classes are abstract classes and implement their respective interfaces in the System.Data Namespace. For example, the DbConnection class implements the IDbConnection interface, the DbDataAdapter implements the IDbDataAdapter interface and so on.

Another interesting point to note is that the concrete classes for each provider inherit their respective base classes in the System.Data.Common namespace. For example, the System.Data.OleDb.OleDbConnection class inherits the DbConnection class from the System.Data.Common namespace.  The same is true for the System.Data.SqlClient.SqlConnection class and all other provider classes that are installed on your machine.

The job of the DbProviderFactory class is to return concrete classes based on the provider you wish to target. However, each provider has its own implementation of the DbProviderFactory class, so you first need a method of retrieving the correct provider's implementation of the DbProviderFactory class. This is where the DbProviderFactories class comes in.

The DbProviderFactories class, like those above, lives in the System.Data.Common namespace and doesn't really do an awful lot. However, it is responsible for creating concrete DbProviderFactory classes based on the provider you are targeting. For example, if you wanted to target a Microsoft Access database then before you could use any of the concrete OleDb classes you first need an OleDbFactory. The DbProviderFactories class has a method called GetFactory which accepts either an invariant name (i.e. System.Data.OleDb) or a DataRow.

 Now, you may be wondering why a DataRow (I know I did), this is because the DbProviderFactories class has another method called GetFactoryClasses that will return a DataTable containing information regarding all of the providers that are installed on your machine. The GetFactory method knows what the structure of this DataTable is and is therefore able to take a DataRow containing the InvariantName and other information and return a concrete implementation of the provider's DbProviderFactory.

In the next section, we will explore the DbProviderFactories in a little more detail and see how we can retrieve a list of all installed providers and retrieve a concrete DbProviderFactory using the GetFactories and GetFactory methods respectively.

 

 

 

DbProviderFactories

The DbProviderFactories class, as mentioned previously, is responsible for returning concrete implementations of the DbProviderFactory class. In order to get a concrete DbProviderFactory, you first need to know the Invariant Provider Name that you will be targeting. This in itself is not difficult as the Invariant Names are simply System.Data.xxxx (where xxxx is OleDb, SqlClient, OracleClient, Odbc etc.), but instead of just relying on this piece of information, we can have a look at the GetFactoryClasses method which returns a DataTable containing information relating to all providers installed on your machine.

To demonstrate, create a new windows forms project and add a DataGridView to your form. Then use the following code to display the providers installed on your machine:

    1         'Retrieves a list of available providers and binds this list to the DataGrid.

    2         Dim availableProviders As DataTable = DbProviderFactories.GetFactoryClasses

    3         availableProvidersGrid.DataSource = availableProviders

Nb: This code assumes that you have imported the System.Data.Common namespace.

When you populate the grid, you will be presented with something similar to the following shortened table:

Name Description InvariantName AssemblyQualifiedName
Odbc Data Provider .Net Framework Data Provider for Odbc System.Data.Odbc ...
OleDb Data Provider .Net Framework Data Provider for OleDb System.Data.OleDb ...

The Name and Description fields are used to describe the provider in human readable terms. The InvariantName field however can be used to programmatically refer to the data provider you wish to target. The AssemblyQualifiedName field contains the fully qualified name of the factory class which is used to create a concrete implementation of the DbProviderFactory class you wish to target (omitted above due to space constraints).

Now that you know what providers are available on your system, the next task is to retrieve a concrete implementation of the required DbProviderFactory. The following example demonstrates how to retrieve the concrete implementation of the OleDbProviderFactory:

    1         Dim provider As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

    2         MessageBox.Show(provider.GetType.ToString)

Assuming all went well, you should be presented with a message box stating "System.Data.OleDb.OleDbFactory". This is achieved by asking the DbProviderFactories to return a Factory (via the GetFactory method) based on the InvariantName string for System.Data.OleDb. If you change this InvariantName to System.Data.SqlClient, you should retrieve a message box stating "System.Data.SqlClient.SqlClientFactory". An important point to make at this time is that the InvariantName passed is case sensitive.

As mentioned previously, the GetFactory method also allows you to pass in a DataRow that contains this information via a call to the GetFactoryClasses method. If we combine the two examples above and pass in a DataRow to the GetFactory method you should get similar results. In the following example, I am passing row 2 of the availableProviders DataTable to return an instance of the OleDbFactory:

    1         Dim availableProviders As DataTable = DbProviderFactories.GetFactoryClasses

    2         Dim provider As DbProviderFactory = DbProviderFactories.GetFactory(availableProviders.Rows(1))

    3         MessageBox.Show(provider.GetType.ToString)

So, we now know how to retrieve the concrete provider factory that we want to target, the next step is in actually communicating with the provider (and hence the data store) without having to worry about which database we are actually using.

Note: The rest of this article assumes that you have both the Northwind databases for Microsoft Access and Microsoft SQL Server 2000/2005. If you are already running Microsoft SQL Server 2000 then you should already have this available to you. If you are running Microsoft SQL Server 2005 then you will need to download the Northwinds database which you can then attach to your instance of Microsoft SQL Server 2005.

Northwind and Pubs sample databases for Microsoft SQL Server 2000
Northwind Traders Sample Database (Microsoft Access 2000)

 

 

Retrieving data using the DbProviderFactory

The first thing we have to consider at this point is the differences in connection strings and SQL syntax when targeting different providers. For the sake of simplicity I have opted to add two connection string values to my app.config file (if you are developing ASP.NET applications then this would be your web.config file) and am also using inline SQL statements. Of course, you will face a high level of complexity if you are trying to target a large number of providers and in some instances, it may make more sense to roll your own implementations of generic database coding, but for the sake of this article, we are only targeting Microsoft Access and Microsoft SQL Server databases.

The App.Config file

Below is an example of the App.Config file entries I have used to store the connection strings to the Northwind database for both Microsoft Access and Microsoft SQL Server:

  <connectionStrings>

    <add name="System.Data.SqlClient" providerName="System.Data.SqlClient" connectionString="Data Source=YourServer;Initial Catalog=Northwind;Integrated Security=SSPI"/>

    <add name="System.Data.OleDb" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=YourPath\Northwind.mdb"/>

  </< span>connectionStrings>

Note that I have used the InvariantNames that the DbProviderFactories class will require when calling the GetFactory method. This makes it easier to reference the connection strings and retrieve the DbProviderFactory concrete classes than providing your own logic to determine what provider is required and which connection string should be used. If you are interested however, ADO.NET 2.0 introduces a new DbConnectionStringBuilder class to aid in the creation of connection strings.

Retrieving Data

The next step is to retrieve some data based on the selected provider. I am assuming at this point that you still have your windows application open with the initial grid displaying the installed providers. Before we add the code necessary to retrieve data, copy and paste the following helper function that will retrieve the connection string from the App.Config file based on the selected provider in the initial grid of providers:

    1     Private Function GetConnectionString(ByVal connectionStringName As String) As String

    2 

    3         'Determine which provider to use and return the appropriate connection string

    4         'from the configuration settings.

    5         Dim returnValue As String = String.Empty

    6         Dim connectionString As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(connectionStringName)

    7 

    8         If connectionString IsNot Nothing Then returnValue = connectionString.ConnectionString

    9 

   10         Return returnValue

   11 

   12     End Function

Add another DataGridView to this form and also a button and then place the following code in the click event of the button:

    1     Private Sub retrieveCustomersButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles retrieveCustomersButton.Click

    2 

    3         'Retrieve the Invariant Name from the selected row in the grid of available providers

    4         Dim providerName As String = availableProvidersGrid.CurrentRow.Cells(2).Value.ToString

    5 

    6         'Retrieve a connection string based on the Invariant Name. Note that the app.config file

    7         'contains two connection strings with the names System.Data.OleDb and System.Data.SqlClient

    8         Dim connectionString As String = GetConnectionString(providerName)

    9 

   10         'If a connection string was obtained (i.e. SQL or OleDb were selected) then

   11         'retrieve a concrete provider factory based on the provider name and load

   12         'the customers into the Data Grid

   13         If connectionString IsNot Nothing AndAlso connectionString.Length > 0 Then

   14 

   15             Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory(providerName)

   16             LoadCustomersIntoGrid(providerFactory, connectionString)

   17 

   18         End If

   19 

   20     End Sub

And finally, add the following routine:

    1     Private Sub LoadCustomersIntoGrid(ByVal dbProvider As DbProviderFactory, ByVal connectionString As String)

    2 

    3         Dim customersSQL As String = "SELECT CustomerID, CompanyName, ContactName FROM Customers"

    4         Dim dt As New DataTable("Customers")

    5 

    6         'Retrieve a connection object

    7         Dim connection As DbConnection = dbProvider.CreateConnection

    8         connection.ConnectionString = connectionString

    9 

   10         'Retrieve a command object to execute the customersSQL statement

   11         Dim command As DbCommand = dbProvider.CreateCommand

   12 

   13         command.Connection = connection

   14         command.CommandText = customersSQL

   15         command.CommandType = CommandType.Text

   16 

   17         connection.Open()

   18         Dim dr As DbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

   19 

   20         dt.Load(dr)

   21         customersDataGridView.DataSource = dt

   22 

   23         connection.Dispose()

   24         command.Dispose()

   25 

   26     End Sub

If we first take a look at the retrieveCustomersButton click event code and dissect what is actually going on.  The first thing we do is to retrieve the selected provider's InvariantName from the grid that was populated using the DbProviderFactories.GetFactories methd. Once we have this name, we then use the GetConnectionString function to return the appropriate connection string from the App.Config file based on the connectionStringName passed (i.e. the InvariantName). Once we have this information, we then use the DbProviderFactories.GetFactory method to return an concrete implementation of the DbProviderFactory before then calling the LoadCustomersIntoGrid routine.

The LoadCustomersIntoGrid routine takes two arguments. The first is the DbProvider instance to use and the second is the connection string. Lines 3 and 4 are pretty standard in that they define an SQL statement and a DataTable that will be used to store the returned data. The fun really begins at Line 7 where we use the passed provider to create a connection object. If for example you selected the System.Data.OleDb InvariantName then the call to dbProvider.CreateConnection will return a System.Data.OleDb.OleDbConnection object. Line 11 asks the dbProvider for a command object (again, this will be of type System.Data.OleDb.OleDbCommand) and then continues to initialise the command object by setting its CommandText, CommandType and Connection properties. A DbDataReader object is then created and assigned the return values of the command's ExecuteReader method. Line 20 uses a new method of the ADO.NET 2.0 DataTable object to populate itself with the contents of the DbDataReader. This saves you having to write any iteration code to add rows to a DataTable using a DataReader. Finally, the DataTable is assigned to the source of the DataGrid and all objects are disposed of.

You can also limit the number of records returned by specifying a parameter in the SQL statement. The following routine will return one customer row based on the CustomerID passed to the routine:

    1     Private Sub LoadCustomerByIDIntoGrid(ByVal dbProvider As DbProviderFactory, ByVal customerID As String, ByVal connectionString As String)

    2 

    3         Dim connection As DbConnection = dbProvider.CreateConnection

    4         connection.ConnectionString = connectionString

    5 

    6         Dim customersSQL As String = "SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE CustomerID=@CustomerID"

    7 

    8         'Retrieve a command object from the dbProvider

    9         Dim command As DbCommand = dbProvider.CreateCommand

   10 

   11         'Setup the command object to execute the customersSQL statement

   12         With command

   13             .CommandText = customersSQL

   14             .CommandType = CommandType.Text

   15             .Connection = connection

   16         End With

   17 

   18         'Create a parameter object from the dbProvider

   19         Dim customerIDParameter As DbParameter = dbProvider.CreateParameter

   20         customerIDParameter.ParameterName = "@CustomerID"

   21         customerIDParameter.Value = customerID

   22 

   23         command.Parameters.Add(customerIDParameter)

   24 

   25         connection.Open()

   26         Dim dr As DbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

   27 

   28         Dim dt As New DataTable("Customer")

   29         dt.Load(dr)

   30 

   31         customersDataGridView.DataSource = dt

   32 

   33         connection.Dispose()

   34         command.Dispose()

   35 

   36     End Sub

The only differences in this routine are Lines 19 to 23 where we create a DbParameter object. Note that I have stuck to the Microsoft SQL Server parameter naming convention rather than the more traditional ? Microsoft Access place holders. The reason for this is that Microsoft SQL Server will use named place holders whereas Microsoft Access simply expects each parameter to be added to the parameters collection of the DbCommand object in the same order as they are defined in the SQL statement. This allows us the ability to use a 'one size fits all' approach but bear in mind that this technique may not work with other database providers and you may need to implement your own logic to handle these providers (maybe in the form of helper functions to pass the SQL statement).

The call to this function is very similar to the previous retrieveCustomersButton, the only difference being that you need to pass a CustomerID value to filter for.

 

Running action queries against the data store

The final example in this article is to demonstrate the ability to run action queries against the target data store. In order to test this, I decided on simply modifying the selected record that was loaded into the grid by adding a "_Mod" string to the end of the Contact and Company Name fields. I also added logic to determine if the selected field already contains this string and then remove it in order to maintain the original state. Now this method is certainly not foolproof as some fields may exceed their maximum lengths, but it is intended simply for demonstration purposes.

If you add another button to your form and place the following code in its click event. Some of it is already familiar to you, save for the logic described above:

    1     Private Sub modifyCustomerRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles modifyCustomerRecordButton.Click

    2 

    3         Dim providerName As String = availableProvidersGrid.CurrentRow.Cells(2).Value.ToString

    4         Dim connectionString As String = GetConnectionString(providerName)

    5 

    6         If customersDataGridView.Rows.Count > 0 Then

    7 

    8             Dim customerID As String = customersDataGridView.CurrentRow.Cells(0).Value.ToString

    9             Dim companyName As String = customersDataGridView.CurrentRow.Cells(1).Value.ToString

   10             Dim contactName As String = customersDataGridView.CurrentRow.Cells(2).Value.ToString

   11 

   12             'If the companyName or contactName end with _Mod then replace back to original state

   13             If companyName.EndsWith("_Mod") Then

   14                 companyName = companyName.Substring(0, companyName.Length - 4)

   15             Else

   16                 companyName &= "_Mod"

   17             End If

   18 

   19             If contactName.EndsWith("_Mod") Then

   20                 contactName = contactName.Substring(0, contactName.Length - 4)

   21             Else

   22                 contactName &= "_Mod"

   23             End If

   24 

   25             If connectionString IsNot Nothing AndAlso connectionString.Length > 0 Then

   26 

   27                 Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory(providerName)

   28                 ModifySelectedCustomerRecord(providerFactory, customerID, companyName, contactName, connectionString)

   29 

   30             End If

   31 

   32         Else

   33 

   34             MessageBox.Show("Please load data into the Grid before attempting to modify.", "No Data to Modify", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

   35 

   36         End If

   37 

   38     End Sub

The following routine will modify the record based on the arguments passed:

    1     Private Sub ModifySelectedCustomerRecord(ByVal dbProvider As DbProviderFactory, ByVal customerID As String, ByVal modifiedCompanyName As String, ByVal modifiedContactName As String, ByVal connectionString As String)

    2 

    3         Dim connection As DbConnection = dbProvider.CreateConnection

    4         connection.ConnectionString = connectionString

    5 

    6         Dim modifyCustomersSQL As String = "UPDATE Customers SET CompanyName=@CompanyName, ContactName=@ContactName WHERE CustomerID=@CustomerID"

    7 

    8         'Retrieve a command object from the dbProvider

    9         Dim command As DbCommand = dbProvider.CreateCommand

   10 

   11         'Setup the command object to execute the modifyCustomersSQL statement

   12         With command

   13             .CommandText = modifyCustomersSQL

   14             .CommandType = CommandType.Text

   15             .Connection = connection

   16         End With

   17 

   18         'Create a parameter object from the dbProvider

   19         Dim companyNameParameter As DbParameter = dbProvider.CreateParameter

   20         companyNameParameter.ParameterName = "CompanyName"

   21         companyNameParameter.Value = modifiedCompanyName

   22 

   23         Dim contactNameParameter As DbParameter = dbProvider.CreateParameter

   24         contactNameParameter.ParameterName = "ContactName"

   25         contactNameParameter.Value = modifiedContactName

   26 

   27         Dim customerIDParameter As DbParameter = dbProvider.CreateParameter

   28         customerIDParameter.ParameterName = "CustomerID"

   29         customerIDParameter.Value = customerID

   30 

   31 

   32         'Add the parameters to the Commands parameters collection in the same order as they are defined

   33         'in the SQL statement. This will resolve the MS Access issue whereby named parameters are not

   34         'supported. It also ensures that the correct fields are updated with the correct values.

   35         command.Parameters.Add(companyNameParameter)

   36         command.Parameters.Add(contactNameParameter)

   37         command.Parameters.Add(customerIDParameter)

   38 

   39         'Execute the command to modify the data

   40         connection.Open()

   41         command.ExecuteNonQuery()

   42         connection.Close()

   43 

   44         connection.Dispose()

   45         command.Dispose()

   46 

   47         'Reload the Grid to show the changes

   48         LoadCustomersIntoGrid(dbProvider, connectionString)

   49 

   50     End Sub

The above code is very similar to the other three routines demonstrated, but note again that the parameters declared in the SQL statement are added in the exact same order to the DbCommand objects parameter collection in order to satisfy Microsoft Access' parameter place holders.

Conclusion

As you can see, the ability to write provider independent code is made easier with the introduction of the DbProviderFactory and DbProviderFactories classes and the objects in the System.Data.Common Namespace.

As was previously stated, there may well be times when rolling your own provider independent code would make more sense, especially when dealing with more stringent providers. If however, you are only targeting a couple of databases that are not so stringent (as in the examples provided in this article) then these new classes will allow you to be more productive.