Using the CommandBuilder

for RuBoard

Using the CommandBuilder

Normally when you go about updating, deleting, or inserting data via the DataSet , you fill the dataset, make your changes to the DataSet , and then issue an explicit update ( DELETE or INSERT ) command. Listing 19.1 shows a typical update process:

Listing 19.1 The Update Process (19NormalUpd.vb)

 Dim dbConn As SqlConnection = New SqlConnection("Data Source=localhost;         Integrated Security=SSPI;Initial Catalog=northwind") Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, ContactName FROM Customers", dbConn) CustomerAdapter.UpdateCommand=New SqlCommand        ("UPDATE Customers SET ContactName=@ContactName " & _        "WHERE CustomerID = @oldCustomerID", dbConn) CustomerAdapter.UpdateCommand.Parameters.Add         ("@ContactName", SqlDbType.NVarChar, 30, "ContactName") Dim OldParms As SqlParameter OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID") OldParms.SourceVersion = DataRowVersion.Original Dim CustomerDataSet As DataSet = New DataSet() CustomerAdapter.Fill(CustomerDataSet, "Customers") Dim UpdtRow As DataRow = CustomerDataSet.Tables("Customers").Rows(0) UpdtRow("ContactName")= "Donald Renato" CustomerAdapter.Update(CustomerDataSet, "Customers") 

However, if your update operation to the database meets all of the criteria that we outlined earlier, the code could be greatly simplified by taking advantage of the automatically generated code from the CommandBuilder . Listing 19.2 is the same update processing but utilizing the SqlCommandBuilder instead. We will be able to remove the UpdateCommand logic, the old parameter-saving logic, and yield a tighter, smaller code result. You will see first the declaration of the SqlCommandBuilde r for the SqlDataAdapter . This will generate all of the needed INSERT , UPDATE , and DELETE commands automatically. Then, you simply open the connection, fill the dataset, update a value in the dataset, and push the update back to the database ( CustomerAdapter.Update.. ). That's it.

Listing 19.2 Updating Using the SqlCommandBuilder (19AGCUpd.vb)

 Dim dbConn As SqlConnection = New SqlConnection("Data Source=localhost;         Integrated Security=SSPI;Initial Catalog=northwind") Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, ContactName FROM Customers", dbConn)  Dim CustomerCMDBLDR As SqlCommandBuilder =   New SqlCommandBuilder (CustomerAdapter)  Dim CustomerDataSet As DataSet = New DataSet() dbConn.Open() CustomerAdapter.Fill(CustomerDataSet, "Customers") Dim UpdtRow As DataRow = CustomerDataSet.Tables("Customers").Rows(0) UpdtRow("ContactName")= "Donald Bertucci"  CustomerAdapter.Update(CustomerDataSet, "Customers")  dbConn.Close() 

Showing What Was Automatically Generated

If you want, you can display the exact SQL code that the CommandBuilder has generated using the Get..Command() methods . Listing 19.3 is a code example of displaying the generated commands out to the console and the execution results of this display.

Listing 19.3 Displaying the Generated Commands (19ShowUpd.vb)

 Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, ContactName FROM Customers", dbConn) Console.Writeline(CustomerCMDBLDR.GetUpdateCommand().CommandText) Console.Writeline(CustomerCMDBLDR.GetInsertCommand().CommandText) Console.Writeline(CustomerCMDBLDR.GetDeleteCommand().CommandText) 

The preceding code, when executed, displays the following results:

 UPDATE Customers SET CustomerID = @p1 , ContactName = @p2 WHERE ( (CustomerID = @p3) AND ((ContactName IS NULL AND @p4 IS NULL) OR (ContactName = @p5)) ) INSERT INTO Customers( CustomerID , ContactName ) VALUES ( @p1 , @p2 ) DELETE FROM  Customers WHERE ( (CustomerID = @p1) AND ((ContactName IS NULL AND @p2 IS NULL) OR (ContactName = @p3)) ) 

If the SELECT Statement Changes

If the SELECT statement that was originally defined in your program needs to change for any reason, you run the risk of executing automatically generated code that does not match the current SELECT statement's metadata. For example, your application might need a different set of columns than were selected earlier, or the SELECT statement could be dynamically provided as an input from the user . If something has changed in this SELECT statement, you must make sure you regenerate the automatically generated code.

What really is happening is that the metadata has changed (the different SELECT statement) and any automatically generated code would be out of sync with this new metadata. The approach for keeping the SELECT statement's metadata in sync with the automatically generated code is to issue a Refresh command after any changes have been made to the SELECT statement.

Listing 19.4 is an example of code that issues a first SELECT statement ( CustomerID and ContactName ), updates data based on that SELECT statement's automatically generated code, then changes the SELECT statement ( CustomerID and CompanyName ), refreshes the automatically generated code, and proceeds with the next update based on the newly generated code. Very clean implementation!

Listing 19.4 Refreshing the Automatically Generated Code (19RefreshUpd.vb)

 Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("  SELECT CustomerID, ContactName FROM Customers  ", dbConn) Dim CustomerCMDBLDR As SqlCommandBuilder = New SqlCommandBuilder                                                      (CustomerAdapter) Dim CustomerDataSet As DataSet = New DataSet() dbConn.Open() CustomerAdapter.Fill(CustomerDataSet, "Customers") Dim UpdtRow As DataRow = CustomerDataSet.Tables("Customers").Rows(0) UpdtRow("ContactName")= "Juliana Nicole" CustomerAdapter.Update(CustomerDataSet, "Customers") CustomerAdapter.SelectCommand.CommandText=                        "  SELECT CustomerID, CompanyName FROM Customers  "  CustomerCMDBLDR.RefreshSchema()   CustomerDataSet.Tables.Remove(CustomerDataSet.Tables("Customers"))  CustomerAdapter.Fill(CustomerDataSet, "Customers") Dim UpdtARow As DataRow = CustomerDataSet.Tables("Customers").Rows(0) UpdtARow("CompanyName")= "ABCDE Company" CustomerAdapter.Update(CustomerDataSet, "Customers") dbConn.Close() 

Windows Form Designer-Generated Code ”VS .NET

Many places in Visual Studio .NET Enterprise Architect have been enhanced with automatic code generation. The area that corresponds to that of the CommandBuilder can be found when you create the DataAdapter for database access for a form when using the Windows Form Designer. You do not have to use the CommandBuilder object to set this capability. You simply choose to have the Data Adapter Configuration Wizard generate INSERT , UPDATE , and DELETE statements for you. These then become part of the Windows Form Designer “generated code used by the form you are building. They will also follow the optimistic concurrency model if you direct the wizard to do so. Figure 19.1 shows the Data Adapter Configuration Wizard with the Use SQL statements option checked and the Advanced SQL Generation Options dialog box. As you can see, these correspond exactly with what CommandBuilder would generate.

Figure 19.1. Data Adapter Configuration Wizard and the Advanced SQL Generation Options dialog box.

The next wizard dialog box (see Figure 19.2) reviews the list of tasks the Data Adapter Confirmation Wizard has performed, which includes the generation of the INSERT , UPDATE , and DELETE statements (just as we saw with the CommandBuilder object).

Figure 19.2. Data Adapter Configuration Wizard ”View Wizard Results.

Then, if you jump back over to the Code Editor tab in the Windows Form Designer, as you can see in Figure 19.3, you will see a node that contains the "Windows Form Designer generated code". Expand this node and you can walk through all the generated INSERT , UPDATE , and DELETE code that was generated for you.

Figure 19.3. Code Editor tab ”Windows Form Designer “generated code.

The generated code is quite extensive and pretty much bulletproof as well. Again, this example follows the optimistic concurrency model for updates. A snippet of code that was generated is shown in Listing 19.5.

Listing 19.5 Generated Code (19Vsdesigner.vb)

 '  SqlInsertCommand1  Me.SqlInsertCommand1.CommandText = "INSERT INTO Customers                (CustomerID, CompanyName, ContactName, ContactTitle, Address" & _         ", City, Region, PostalCode, Country, Phone, Fax) VALUES                               (@CustomerID, @CompanyName, " & _         "@ContactName, @ContactTitle, @Address, @City, @Region,                @PostalCode, @Country, @Phone, @Fax); " & _         "SELECT CustomerID, CompanyName, ContactName,                                              ContactTitle, Address," & _         "City, Region, PostalCode, Country, Phone, Fax                       FROM Customers WHERE (CustomerID = @CustomerID)"_ '  SqlUpdateCommand1  Me.SqlUpdateCommand1.CommandText = "UPDATE Customers                SET CustomerID = @CustomerID, CompanyName = @CompanyName,                ContactName = @ContactName, ContactTitle = @ContactTitle, " & _         " Address = @Address, City = @" & _         "City, Region = @Region, PostalCode = @PostalCode, Country = @Country,"&         "Phone = @P hone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) " &         "         "AND (Address = @Original_Address OR @Original_Address IS NULL AND " &         " Address IS NULL) AND (City = @Original_City OR " &     _         "@Original_City IS NULL AND City IS NULL) " & _         " AND (CompanyName = @Original _CompanyName) AND (ContactName = " &         " @Original_ContactName OR @Original_ContactName" & _         " IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original" & " &         "ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle" &         " IS NULL) AND (Country = @Original_Country OR @Original " & _         " _Country IS NULL AND Country IS NULL) AND (Fax = @Original" & _         "_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND " &         " (Phone = @Original_Phone OR @" & _         "Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = " &         " @Original_PostalCode OR @Original_PostalCode " & _         " IS NULL AND PostalCode IS NULL) AND (Region = @Original" & _         "_Region OR @Original_Region IS NULL AND Region IS NULL); " &         " SELECT CustomerID, CompanyName, ContactName, ContactTitle " & _         ", Address, City, Region, PostalCode, Country, " & _         "Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)" '  SqlDeleteCommand1  Me.SqlDeleteCommand1.CommandText = "DELETE FROM Customers " &         WHERE (CustomerID = @Original_CustomerID) AND (Address = " & _         "@Original_Address OR @Original_Address IS NULL AND Address " &         " IS NULL) AND (City = @Original_City OR @Original_City " & _         "IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName)" &         "AND (ContactName = @Original_ContactName OR @Original_Contact" & _         "Name IS NULL AND ContactName IS NULL) AND (ContactTitle = " &         "@Original_ContactTitle OR @Original_ContactTitle IS NULL " & _         " AND ContactTitle IS NULL) AND (Country = @Original_Country " & _         " OR @Original_Country IS NULL AND Country IS NULL) AND " & _         "(Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL)" &         " AND (Phone = @Original_Phone " & _         "OR @Original_Phone IS NULL AND Phone IS NULL) AND" &         " (PostalCode = @Original_Postal Code OR @Original_PostalCode " & _         " IS NULL AND PostalCode IS NULL) AND (Region = @Original " & _         " _Region OR @Original_Region IS NULL AND Region IS NULL)" 

You can see the WHERE clause comparisons for guaranteeing your update or delete is being used (for optimistic concurrency). Just be glad that you don't have to code all of this yourself!

for RuBoard