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.