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.