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.