12.5 Command Builder | Programming ASP.NET 3.5

In the previous section, you painstakingly created the update, insert, and delete commands. You first created stored procedures, and then you created command objects for each procedure, passing in the necessary parameters. ASP.NET will do a lot of this work for you, if the update, insert, and delete commands are simple enough.

ASP.NET provides a Command Builder (SqlCommandBuilder and OleDbCommandBuilder) to generate the necessary delete, update, and insert commands without your writing stored procedures. To take advantage of these objects, the following conditions must be met:

The rows in the table you are generating must have come from a single table in the database.
The table must have a primary key or a field with values guaranteed to be unique.
The unique value column must be returned by the query used to fill the dataset (the select command).
The name of the table must not have spaces, periods, quotation marks, or other special characters.

To see how using the command builder classes simplifies the task when these conditions are met, you'll modify the program to build the dataset only from the Bugs table. Your user interface will be much simpler because you'll use a very simple Select statement, Select * from Bugs.

Strip down the .aspx page, and do not use custom columns; allow the data grid to get its value right from its source table. Discard the BugHistory data grid, since you won't be using it for this example. The complete .aspx page is shown in Example 12-17.

Example 12-17. The simpler .aspx file

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false"  Inherits="BugHistoryUpdateAutoGenerated.WebForm1" trace="true" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML>   <HEAD> <meta content="Microsoft Visual Studio 7.0" name=GENERATOR> <meta content=C# name=CODE_LANGUAGE> <meta content="JavaScript (ECMAScript)" name=vs_defaultClientScript> <meta content=http://schemas.microsoft.com/intellisense/ie5 name=vs_targetSchema>   </HEAD> <body> <form id=Form1 method=post runat="server">    <table>       <tr>          <td><asp:Button                 Text="Update DataSet" Runat="server" /></td>          <td><asp:Button                 Text="Update Database" Runat="server" /></td>       </tr>    </table>    <br>    <asp:datagrid  runat="server" EnableViewState="true"     HeaderStyle-Font-Bold AlternatingItemStyle-BackColor="LightGrey"     BorderColor="#000099"     BorderWidth="5px" HeaderStyle-BackColor="PapayaWhip"         CellPadding="5" DataKeyField="BugID">    </asp:datagrid></FORM>    <asp:Panel  Runat="server" >       <asp:datagrid  runat="server"                DataKeyField="BugID" CellPadding="5"        HeaderStyle-BackColor="PapayaWhip"                BorderWidth="5px" BorderColor="#000099"        AlternatingItemStyle-BackColor="LightGrey" HeaderStyle-Font-Bold        EnableViewState="true">       </asp:datagrid>       <asp:Label                 Runat="server" visible="False"></asp:Label>       </asp:Panel>    </body> </HTML>

You must toss out all the code that deals with the BugHistory table in the code-behind page. Notice that the event handling has been removed from the data grid as well. To keep things simple, you'll just display the Bugs table, modify it, and then update the database.

You must modify btnUpdateDataSet_Click so that you are updating and adding fields only in Bugs:

DataRow newRow = bugTable.NewRow(  ); newRow["Product"] = 2;  newRow["Version"] = "0.01"; newRow["Description"] = "New bug test"; newRow["Reporter"] = 3; bugTable.Rows.Add(newRow);

In VB .NET, it is:

Dim newRow as DataRow = bugTable.NewRow() newRow("Product") = 2 newRow("Version") = "0.01" new Row("Description") = "New bug test" newRow("Reporter") = "3" bugTable.Rows.Add(newRow)

The important change is in btnUpdateDataBase_Click, which is now far simpler. You simply retrieve the dataset and set up the connection object, exactly as you did earlier in Example 12-12:

DataSet ds = (DataSet) Session["BugsDataSet"]; string connectionString =     "server=YourServer; uid=sa; " +               "pwd=YourPassword; database=ProgASPDotNetBugs"; System.Data.SqlClient.SqlConnection connection =     new System.Data.SqlClient.SqlConnection(connectionString); connection.Open(  );

In VB .NET, it is:

Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet) Dim connectionString As String = _    "server=YourServer; uid=sa; " & _               "pwd=YourPassword; database=ProgASPDotNetBugs" Dim connection As New _    System.Data.SqlClient.SqlConnection(connectionString) connection.Open(  )

You then create a data adapter and a SqlCommandBuilder:

SqlDataAdapter dataAdapter =     new SqlDataAdapter("select * from Bugs", connection); SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);

In VB .NET, it is:

Dim dataAdapter As _    New SqlDataAdapter("select * from Bugs", connection) Dim bldr As New SqlCommandBuilder(dataAdapter)

You use the Command Builder to build the DeleteCommand, UpdateCommand, and InsertCommand objects required by the data adapter, which you previously built by hand:

dataAdapter.DeleteCommand = bldr.GetDeleteCommand(  ); dataAdapter.UpdateCommand = bldr.GetUpdateCommand(  ); dataAdapter.InsertCommand = bldr.GetInsertCommand(  );

The VB .NET code is identical (without the semicolons).

That's it! You are ready to enlist the commands in the transaction:

SqlTransaction transaction; transaction = connection.BeginTransaction(  ); dataAdapter.DeleteCommand.Transaction = transaction; dataAdapter.UpdateCommand.Transaction = transaction; dataAdapter.InsertCommand.Transaction = transaction;

In VB .NET, it is:

Dim transaction As SqlTransaction transaction = connection.BeginTransaction(  ) dataAdapter.DeleteCommand.Transaction = transaction dataAdapter.UpdateCommand.Transaction = transaction dataAdapter.InsertCommand.Transaction = transaction

With that done, you are ready to call Update on the dataAdapter, just as you did previously:

int rowsUpdated = dataAdapter.Update(ds,"Bugs");

In VB .NET, it is:

Dim rowsUpdated As Integer = dataAdapter.Update(ds,"Bugs")

The CommandBuilder object has created the necessary commands on your behalf. You can see what these are by adding Trace statements to the btnUpdateDataBase_Click method:

Trace.Write(dataAdapter.InsertCommand.CommandText); Trace.Write(dataAdapter.UpdateCommand.CommandText); Trace.Write(dataAdapter.DeleteCommand.CommandText);

The VB .NET code is identical (without the semicolons). This will display the parameterized commands in the Trace window, as shown in Figure 12-10.

Figure 12-10. Trace statements showing generated commands

Pay particular attention to the Update command; you can see that the Where clause is built exactly as you built it by hand in the earlier section.

To save space, the complete program is not reproduced here, but it is available for download from our web site. See the Preface for details.