Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  ADO.NET  »  Create generic database code using ADO.NET 2.0 Provider Factories  »  Running action queries against the data store
Create generic database code using ADO.NET 2.0 Provider Factories
by David Jeavons | Published  10/17/2006 | ADO.NET | Rating:
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. 

View all articles by David Jeavons...
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.

How would you rate the quality of this article?
1 2 3 4 5
Poor Excellent
Tell us why you rated this way (optional):

Article Rating
The average rating is: No-one else has rated this article yet.

Article rating:2.89690721649484 out of 5
 97 people have rated this page
Article Score32547
Sponsored Links