Doing a Batch Update

In Chapter 9, "Editing the DataGrid Web Control," we'll examine how to use the DataGrid and DataList's editing capabilities. The built-in editing support provides editing on a row-level basis. That is, the entire contents of a DataGrid are displayed to the user with each row containing an Edit button. When the Edit button is clicked, the row whose Edit button was clicked has its various columns converted into TextBoxes or DropDownLists, or whatever editing interface you choose. Additionally, the row's Edit button is replaced by Update and Cancel buttons. After updating the data and clicking the Update button, the data is saved to the underlying data store and the DataGrid is displayed as it was originally, but with the updated row information.

This row-level editing is useful if you plan on only occasionally changing the data, or if when you plan on having to update the database table, you expect to only have to update a couple of rows at most. However, if you know that you will need to edit, say, 20 rows, it can be quite annoying to have to click the Edit button, make your changes, and then click the Update button 20 times. In such a scenario, it would be nice to have the entire data Web control appear in its edit mode, with one button on the page labeled Update ALL Changes Made.

Clearly, this Update ALL Changes Made command button is a standard ASP.NET Web control external to the DataGrid or DataList it operates on. When this button is clicked, the data Web control's contents need to be iterated through, updating the underlying data store to reflect the changes to each row. (This update would be an UPDATE SQL statement if the data store was a relational database; it might be some set of XML commands if the underlying data store were an XML file.)

Let's look at an example of this idea. The titles table in the pubs database contains a price field, which represents the selling price of the book. Imagine that your boss has asked you to construct a Web interface that displays all the books and their prices. From this interface, each price should be displayed as an editable TextBox, enabling the user to change the price of zero to many books at a time. At the top of the Web page should be an Update ALL Prices button that updates the underlying database with any changes to book prices.

As in the previous section's examples in which we used a DataGrid Web control, let's turn to the DataList Web control for this exercise. Listing 6.5 contains the code to allow for batch updates. The Page_Load event handler and BindData() functions have been omitted for brevity. Because we are not using filter buttons here, the BindData() method does not take any filtering parameter. The code for the Page_Load event handler and BindData() functions can be found by referring back to Listing 6.1, lines 4 8 and 11 27, respectively.

Listing 6.5 Clicking the Update ALL Prices Button Updates All Rows in the DataList

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="C#">    4:  ' ... The Page_Load event handler and BindData functions   5:  '  have been omitted for brevity. See Listing 6.1 for details ...   6:   7:   void updatePrices(object sender, EventArgs e)   8:   {   9:    // 1. Create a connection  10:    const string strConnString = "server=localhost;uid=sa;pwd=;database=pubs";  11:    SqlConnection objConn = new SqlConnection(strConnString);  12:  13:    // Create an appropriate UPDATE statement  14:    const string strSQL = "UPDATE titles SET price = @price WHERE title_id = @titleID";  15:  16:    // Create a command object for the query  17:    SqlCommand objCmd = new SqlCommand(strSQL, objConn);  18:    objConn.Open();  19:  20:    string titleID, strPrice;  21:  22:    // iterate through the DataList's TextBoxes  23:    foreach (DataListItem dlItem in dlTitles.Items)  24:    {  25:     // reference the TextBox  26:     TextBox txtPrices = (TextBox) dlItem.FindControl("txtPrice");  27:  28:     // Grab the value  29:     strPrice = txtPrices.Text;  30:  31:     // Get the title_id from DataKeys  32:     titleID = dlTitles.DataKeys[dlItem.ItemIndex].ToString();  33:  34:     // Now, update the parameters  35:     SqlParameter priceParam = new SqlParameter("@price", SqlDbType.Money);  36:     if (strPrice == String.Empty)  37:      // we need to insert a NULL  38:      priceParam.Value = DBNull.Value;  39:     else  40:      priceParam.Value = strPrice;  41:     objCmd.Parameters.Add(priceParam);  42:  43:     SqlParameter titleIDParam = new SqlParameter("@titleID", SqlDbType.VarChar, 6);   44:     titleIDParam.Value = titleID;  45:     objCmd.Parameters.Add(titleIDParam);  46:  47:     // now, execute the query  48:     objCmd.ExecuteNonQuery();  49:  50:     // Clear the parameters  51:     objCmd.Parameters.Clear();  52:    }  53:  54:    objConn.Close();  // close the connection  55:   }  56: </script>  57:  58: <form runat="server">  59:  <asp:Button Text="Update ALL Prices" runat="server"  60:     OnClick="updatePrices" />  61:  <p>  62:  <asp:DataList runat="server"   63:    CellPadding="5" BorderStyle="Inset"  64:    Font-Name="Verdana" Font-Size="9pt"  65:    AlternatingItemStyle-BackColor="#dddddd"  66:    DataKeyField="title_id">  67:  68:   <ItemTemplate>  69:    <b>Title:</b> <%# DataBinder.Eval(Container.DataItem, "title") %>  70:    <br /><b>Price:</b>  71:    <asp:TextBox runat="Server"  Columns="5"  72:     Text='<%# DataBinder.Eval(Container.DataItem, "price") %>' />  73:   </ItemTemplate>  74:  </asp:DataList>  75: </form> 

Let's start by dissecting the DataList declaration starting at line 62. First notice that we set the DataKeyField property to the titles table's primary key field, title_id (line 66). In the ItemTemplate, we use data-binding syntax to display the title of the book, along with a TextBox listing its price. For some reason, the titles table allows NULLs in the price field. If the price field is NULL, the value returned by the DataBinder.Eval() method will be a blank string.

When the user visits the page, he will be shown a list of the titles and prices of all the books in the titles database. The price will be listed in an editable TextBox. After the user has updated any price values, he needs a way to save the changes back to the database. The Update ALL Prices Button control on lines 59 and 60 provides this functionality. This Button control has its Click event wired up to the updatePrices event handler (lines 7 55), which we will examine in a moment. For now, realize that when this button is clicked, the ASP.NET page is posted back and the code in the updatePrices event handler is executed.

The task the updatePrices event handler is faced with sounds like a simple one it must update the database with the new values entered by the user. This task, however, requires a bit of code. First, we must create a connection to the database. Lines 9 through 18 create a connection to the pubs database and prepare a SqlCommand object with a SQL UPDATE command. The UPDATE string (line 14) uses parameters to specify the price and title_id field values.

After we have established a connection, we are ready to iterate through the rows of the DataList, reading in the value of the txtPrice TextBox and updating the database with the corresponding value. To iterate through the rows of the DataList, we iterate through the DataListItem instances returned by the Items property of the DataList (line 23). For each iteration, we need to perform the following tasks:

1. Read in the value of the txtPrice TextBox.

2. Determine the title_id value for the row being updated.

3. Update the database.

On line 26 we reference the txtPrice TextBox through the FindControl method. As we examined in Chapter 5, the FindControl method returns the child control whose ID is equal to the string parameter passed into the FindControl method. The return type of FindControl is Control, which is the base type of all ASP.NET Web controls. However, we are assigning the result of the FindControl method to a variable of type TextBox. In Visual Basic .NET, we do not need to explicitly cast the return value of FindControl to a TextBox, but Listing 6.5 is written in C#, which requires such practices. The (TextBox) code on line 26 casts the return value of FindControl to that of type TextBox.

After we have this TextBox control referenced by the txtPrices variable, we can find the value of the TextBox control by referencing its Text property (see line 29).

NOTE

If FindControl cannot find a control with the ID value specified as its input parameter, a value of null (or Nothing in Visual Basic .NET) is returned. A more robust implementation of Listing 6.5 would include a conditional statement to check whether txtPrices was equal to null before attempting to de-reference it on line 29.

With these two lines of code (lines 26 and 29), we have completed step 1. Now we must determine the value of the title_id field for the particular row. We do this by consulting the DataKeys collection. Line 32 retrieves the proper item from the DataKeys collection and assigns it to the titleID local variable. This concludes step 2.

All that remains now is to update the database. Because we are using a parameterized query, the first step is to add the parameters to our SqlCommand object (objCmd). If the txtPrice TextBox is empty, we want to update the row with a NULL value. This check is made on line 36. After we've added the parameters to the objCmd (lines 41 and 45), we can execute the query using the ExecuteNonQuery() method of the SqlCommand class (line 48). (The ExecuteNonQuery() method is optimized for database calls that do not return any values.) Finally, on line 51 we clear the SqlCommand object's Parameters collection, because we are reusing the same SqlCommand object through each iteration of the loop.

Figure 6.3 shows a screenshot of Listing 6.5 when viewed through a browser.

Figure 6.3. A DataList displays each book's price in an editable TextBox.

Note that the code in Listing 6.5 makes a database call for each row in the DataList, regardless of whether it has been changed. In cases where the user has made a change to only one or two of the rows out of perhaps 50, it might seem grossly inefficient to perform 50 database updates calls when in fact only two were needed.

Realistically, these additional database accesses are unlikely to negatively affect the overall performance of your Web site. After all, how often are you going to be performing batch updates of this kind? Most likely, you'll only allow a small subset of total users to even access this page, and even those users will likely only use such functionality rarely.

If for some reason the extraneous database calls are of chief concern, one option would be to store the values of the prices before being edited, so that they can be compared to the new values. One way to accomplish this would be to create a DataList identical to the one that displays the titles and editable prices of each book. Set this DataList's Visible property to False, so that it won't appear on the ASP.NET Web page. Then, in the updatePrices event handler, you can check the value of the current DataList row being iterated through, with its corresponding row in the invisible DataList. If the values are the same, you can forgo the database update call.