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-16.
Example 12-16. 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 ID="btnUpdateDataSet"
Text="Update DataSet" Runat="server" /></td>
<td><asp:Button ID="btnUpdateDataBase"
Text="Update Database" Runat="server" /></td>
</tr>
</table>
<br>
<asp:datagrid id="DataGrid1" 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 ID="DataGrid2Panel" Runat="server" >
<asp:datagrid id="DataGrid2" 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 id="CountUpdatedRows"
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);
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-11:
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( );You then create a data adapter and a SqlCommandBuilder:
SqlDataAdapter dataAdapter =
new SqlDataAdapter("select * from Bugs", connection);
SqlCommandBuilder bldr = 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( );
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;
With that done, you are ready to call Update on the dataAdapter, just as you did previously:
int rowsUpdated = 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);
This will display the parameterized commands in the Trace window, as shown in Figure 12-10.
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 my web site. See the the preface for details.