Operating data in ASP.net 2.0 62: GridView Batch Update data _ self-study Process

Introduction:

In the previous tutorial, we extended the data Access layer to support database transactions. Database transactions ensure that a series of operations are either successful or fail. In this article we turn our attention to creating a batch update data interface.

In this article, we'll create a GridView control in which each row of records can be edited (see Figure 1), so we don't need to add one more column to contain the edit, update, and Cancel buttons, but instead include the 2 Update products buttons on the page. When clicked, iterate through all the products and update the database. Let's get started.

Figure 1:gridview Each row of records in the control can be edited

Note: In the 37th chapter "DataList batch Update" We created a batch editing interface with a DataList control, one of the differences between this article and this article is that this article uses the GridView control and uses transactions.

Review the steps to set all GridView rows editable

As in the 16th chapter, "Overview Insert, update, and delete data", the GridView control edits each row of records with built-in editing capabilities. Internally, the GridView control uses the Editindex property to determine which row is editable. Once the GridView is bound to the data source, it checks line by row to see which row's index matches the Editindex value and, if found, renders the row as an editing interface. If it is a bound column (BoundFields), it is rendered as a textbox, Its text value is the value of the corresponding BoundField DataField property, and if it is a template column (Templatefields), it is rendered as EditItemTemplate instead of ItemTemplate.

We know that when a user clicks on the Edit button of a row, the page generates a return, assigning the index value of the row to the Editindex property of the GridView control. And then rebind the data. When you click the Cancel button on a line to generate a page return, set the Editindex property to-1 before you rebind the data. Because, for rows of the GridView control, the index value at start 0, and the Editindex set to 1, becomes read-only mode. .

If you are only editing a row, the Editindex property works fine, but it does not support batch editing. To implement a batch of edits to the GridView, We have to make each row appear as an editing interface. The easiest way to do this is to convert the column you want to edit to TemplateField, and then create an editing interface in the ItemTemplate template. In the next few steps, we will create a full batch of editable GridView, in the first step, we will create a GR Idview and its ObjectDataSource, and convert BoundFields and CheckBoxField to Templatefields. In the second and third steps, we move the editing interface from the itemtemplates template to the edititemtemplates.

First step: Show product information

First of all Let's create a GridView that displays product information. Open the page in the Batchdata folder batchupdate.aspx, drag a GridView control from the toolbox to the page, set the ID value to Productsgrid, and bind it from its smart tag to a PRODUCTSDA Tasource ObjectDataSource, which sets the GetProducts method for calling the Productsbll class class.

Figure 2: Setting the OBJECTDATASOURC call Productsbll class

Figure 3: Obtaining product information using the GetProducts method

Like the GridView, the method invoked by this ObjectDataSource can only work on each row of records. In order to update the records, we have to write more code in the background code class of the ASP.net page, batch process the data and pass it to BLL. Therefore, in ObjectDataSource update, INSERT, and delete tag, select (None). Click Finish to complete the setup.

Figure 4: Select (None) in Update, INSERT, and delete tags

When the settings are complete, the declaration code for the ObjectDataSource control looks similar to the following:

<asp:objectdatasource id= "Productsdatasource" runat= "Server"
 oldvaluesparameterformatstring= "original_{0"} "
 selectmethod=" getproducts "typename=" PRODUCTSBLL ">
</asp:ObjectDataSource>

When the settings are complete, Visual studio adds BoundFields and a CheckBoxField to the GridView control. For the purposes of this article, we only allow users to view and edit product names, categories, prices, and discontinued state. Remove all columns except ProductName, CategoryName, UnitPrice, and discontinued, and set the HeaderText property of the first 3 columns to "Product", respectively. Category "," Price ". Finally, enable the GridView paging, sorting function.

At this point, the GridView control contains 3 BoundFields (Productname,categoryname, and UnitPrice) and a CheckBoxField (discontinued). We want to convert these 4 columns to templatefields and transfer the editing interface from the TemplateField edititemtemplate template to the ItemTemplate template.

　　Note: We are in the 20th chapter "Custom Data Modification Interface" Discusses how to create and customize Templatefields. We convert BoundFields and CheckBoxField to Templatefields, and then customize their editing interface in the ItemTemplates template. If there is anything unclear, refer to the previous article.

From the smart tag of the GridView, click Edit Column, which opens the Fields dialog box, selects each column, and clicks Convert This field into a TemplateField.

Figure 5: Converting existing BoundFields and CheckBoxField to TemplateField

Now each column is TemplateField, we will transfer the editing interface from the edititemtemplates template to the itemtemplates template.

Step 2nd: Create an editing interface for ProductName, UnitPrice, and discontinued columns

Creating ProductName, UnitPrice, and discontinued these 3-column editing interfaces is simpler because they are all defined in the TemplateField edititemtemplate template While creating a CategoryName editing interface is cumbersome because we need to create a DropDownList control to display the available categories, which we will implement in step 3rd.

We first create the ProductName editing interface. Click Edit Template in the smart tag of the GridView control, and then click ProductName TemplateField edititemtemplate Item. Select the textbox, copy it, paste it into ProductName TemplateField the ItemTemplate template. Set the ID property of the textbox to ProductName.

Then, add a RequiredFieldValidator control to the ItemTemplate template to ensure that the product name entered by the user is not empty. Set its ControlToValidate property to "ProductName" The ErrorMessage property is "You must provide the product ' name." The Text property is "*". When you finish adding, the screen should look like Figure 6:

Figure 6:productname TemplateField now contains a TextBox control and a RequiredFieldValidator control

For the UnitPrice editing interface, copy the textbox from the EditItemTemplate template to the ItemTemplate template. Then, place a "$" match in front of the textbox and set its ID property to "UnitPrice" ; The Columns property is set to "8".

Then add a CompareValidator control to ensure that the user enters a currency value that is greater than or equal to $0.00. Set its ControlToValidate property to "UnitPrice"; ErrorMessage property is "you must Enter a valid currency value. Please omit any currency symbols. " The Text property is "*"; the Type property is the Currency;operator property is the Greaterthanequal;valuetocompare property is "0".

Figure 7: Adding a CompareValidator control to ensure that the user enters a non-negative currency value

For discontinued TemplateField, the direct use of a checkbox already defined in the ItemTemplate template requires only that its ID is "discontinued" and the Enabled property is true.

Step three: Create a CategoryName editing interface

The editing interface in the EditItemTemplate template of CategoryName TemplateField contains a textbox to display the values of the CategoryName column We want to replace it with a DropDownList control to display the categories.
Note: In the 20th chapter, "Customizing the Data Modification Interface" We discussed in detail how to replace a TextBox control with a DropDownList control. Here we will skip over the process, and specifically create and set the details of the DropDownList control to refer to chapter 20th.

Drag a DropDownList control from the Toolbox to the Categorynametemplatefield ItemTemplate template, Set its ID to categories. Typically, we define a dropdownlists data source through its smart tag to create a new ObjectDataSource. However, this will add a new ObjectDataSource to the ItemTemplate template, with consequences Is every one of the GridView Row creates a ObjectDataSource instance. Therefore, we create ObjectDataSource outside the Templatefields of the GridView. End template Editing, drag a ObjectDataSource from the toolbox to the page, and place it in the name Produ Ctsdatasource the ObjectDataSource control below. The new O is named Categoriesdatasource with the GetCategories method Bjectdatasource, which is used to getcategories the Categoriesbll class class.

Figure 8: Setting the ObjectDataSource using the CATEGORIESBLL class

Figure 9: Getting data from the GetCategories method

Because the ObjectDataSource is only used to retrieve data, select "(None)" In the Update and delete tabs. Click Finish to complete the setup.

Figure 10: Select (None) in the Update and delete tabs

After the setup is complete, the Categoriesdatasource declaration code looks almost the same as the root:

<asp:objectdatasource id= "Categoriesdatasource" runat= "Server"
 oldvaluesparameterformatstring= "original_{" 0} "
 selectmethod=" getcategories "typename=" CATEGORIESBLL >
</asp:ObjectDataSource>

Once set, return to the ItemTemplate template of CategoryName TemplateField, point "Choose data Source" in the DropDownList smart tag, in the Data Sources Setup Wizard, Select Categoriesdatasource in the first Drop-down list, and select CategoryName and CategoryID in the following 2 Drop-down lists respectively.

Figure 11: Bind the DropDownList control to the Categoriesdatasource

At this point, the DropDownList control lists all the categories, but for the products that are bound to GridViewRow, It does not automatically select the product corresponding to the category. For this reason, we set the DropDownList SelectedValue value to the CategoryID value of the product. Click "Edit DataBindings" in the DropDownList smart tag and assign the SelectedValue property to CategoryID, as shown in Figure 12:

Figure 12: Binding the product's CategoryID value to the DropDownList SelectedValue property

The last problem is that if the product's CategoryID is empty, the data binding to SelectedValue throws an exception. Because DropDownList only lists those products that specify CategoryID values, they do not list products that have CategoryID values of NULL. How to solve it? Set the DropDownList Appenddataboundit property to rue and add a new item to DropDownList, ignoring its Value property as the following declaration code:

<asp:dropdownlist id= "Categories" runat= "Server" appenddatabounditems= "True"
 datasourceid= " Categoriesdatasource "datatextfield=" CategoryName "
 datavaluefield=" CategoryID "Selectedvalue=" <%# Bind (" CategoryID ")%> ' >
 <asp:listitem value=" ">--Select one--</asp:listitem>
</asp:d Ropdownlist>

We note that <asp:listitem value= ">"--Select one--"sets the Value property to an empty string. Why do I have to add this item to handle a null-value case? Why do you want to set the Value property to an empty string? These questions can refer to the previous 20th "Customizing data Modification Interface"

　　Note: There is a potential problem with performance that needs to be mentioned. Because each row contains a DropDownList, its data source is categoriesdatasource. Every time you log on to the page, the GetCategories method of the Categoriesbll class class is called n times, Here n is the number of rows in the GridView control. The N-time call to GetCategories results in an n-query of the database. We can cache the results of the return to mitigate the impact on the database; In the way, you can use the Per-request caching policy or SQL cache dependencies in the cache layer caching layer (SQL caching dependency Or a policy based on a short time cache period (a very shorter time-based expiry). For more information on the Per-request caching strategy, refer to the article "Httpcontext.items–a per-request Cache Store" (http://aspnet.4guysfromrolla.com/ articles/060904-1.aspx)

Fourth step: Improve the editing interface

View the page in the browser, as shown in Figure 13, where each row uses the ItemTemplate template to include its edit page.

Figure 13: Each gridview row is editable

But there are still some problems. First, the UnitPrice value is four decimal points, and for this reason, return the ItemTemplate template of the UnitPrice TemplateField, point "Edit DataBindings" in the TextBox's smart tag, and then, Specifies the Text property format as number.

Figure 14: Specify the text format as number

Then, center the CheckBox control of the Discontinued column (instead of left), click Edit column in the smart tag of the GridView, and select the discontinued in the left box. Then set the HorizontalAlign property to center in the ItemStyle in the right-hand box, as shown in Figure 15:

Figure 15: The checkbox to the Discontinued column is left

Next, add a Validationsummar control to the page and set its ShowMessageBox property to the True;showsummary property set to False. Add a button Web control at the same time to update what the user is doing. Specifically, add 2, one above the GridView control, one below, and set their Text property to "Update products." Since we have defined the editing interface in the Templatefields template, Then the edititemtemplates template is redundant and deleted.

After you complete the above changes, your page declaration code should look similar to the following:

<p> <asp:button id= "UpdateAllProducts1" runat= "Server" text= "Update products"/> </p> <p> <a Sp:gridview id= "Productsgrid" runat= "Server" autogeneratecolumns= "False" datakeynames= "ProductID" datasourceid= " Productsdatasource "allowpaging=" true "allowsorting=" true "> <Columns> <asp:templatefield headertext=" Product "sortexpression=" ProductName "> <ItemTemplate> <asp:textbox id=" ProductName "runat=" Server "Tex T= ' <%# Bind ("ProductName")%> ' ></asp:TextBox> <asp:requiredfieldvalidator id= '
   RequiredFieldValidator1 "controltovalidate=" ProductName "errormessage=" You must provide the product ' s name. " runat= "Server" >*</asp:RequiredFieldValidator> </ItemTemplate> </asp:TemplateField> <asp: TemplateField headertext= "Category" sortexpression= "CategoryName" > <ItemTemplate> <asp:dropdownlist id= "Categories" runat= "Server" appenddatabounditems= "True" DatasouRceid= "Categoriesdatasource" datatextfield= "CategoryName" datavaluefield= "CategoryID" SelectedValue= "<%#" Bind ( "CategoryID")%> ' > <asp:listitem>--Select one--</asp:listitem> </asp:DropDownList> </ itemtemplate> </asp:TemplateField> <asp:templatefield headertext= "Price" sortexpression= "UnitPrice" > <ItemTemplate> $<asp:textbox id= "UnitPrice" runat= "Server" columns= "8" text= ' <%# Bind ("UnitPrice" , "{0:n}")%> ' ></asp:TextBox> <asp:comparevalidator id= ' CompareValidator1 ' runat= ' server ' Controltova
     Lidate= "UnitPrice" errormessage= "You must enter a valid currency value.
   Please omit any currency symbols. " Operator= "Greaterthanequal" type= "Currency" valuetocompare= "0" >*</asp:CompareValidator> </ itemtemplate> </asp:TemplateField> <asp:templatefield headertext= "discontinued" sortexpression= " Discontinued "> <ItemTemplate> <asp:checkbOx id= "Discontinued" runat= "Server" checked= ' <%# Bind ("discontinued")%> '/> </ItemTemplate> <ite Mstyle horizontalalign= "Center"/> </asp:TemplateField> </Columns> </asp:GridView> </p> &L t;p> <asp:button id= "UpdateAllProducts2" runat= "Server" text= "Update products"/> <asp:objectdatasource ID = "Productsdatasource" runat= "Server" oldvaluesparameterformatstring= "original_{0}" selectmethod= "GetProducts" Typename= "PRODUCTSBLL" > </asp:ObjectDataSource> <asp:objectdatasource id= "Categoriesdatasource" runat=
 "Server" oldvaluesparameterformatstring= "original_{0}" selectmethod= "GetCategories" typename= "CategoriesBLL" > </asp:ObjectDataSource> <asp:validationsummary id= "ValidationSummary1" runat= "Server" showmessagebox= "

 True "showsummary= False"/> </p>

When you add the button Web control and modify the related formatting, the page looks like this:

Figure 16: The page now contains 2 Update products buttons

Fifth step: More New products

When the user logs on to the page for modification and clicks on the "Update products" button, we need to save the user input value as a Productsdatatable instance instance, and then pass the instance to a BLL method. The example is then passed to the Updatewithtransaction method of the DAL layer. This method is created in the previous article to ensure that the batch is atomic.

Create a method named BatchUpdate in the BatchUpdate.aspx.cs file, the code is as follows:

private void BatchUpdate () {//Enumerate the GridView ' s Rows collection and create a productrow PRODUCTSBLL Productsap
 I = new PRODUCTSBLL ();

 Northwind.productsdatatable products = productsapi.getproducts (); foreach (GridViewRow gvrow in productsgrid.rows) {//find "productsrow instance in", "Maps to Gvrow int ProductID = Convert.ToInt32 (Productsgrid.datakeys[gvrow.rowindex].

 Value); Northwind.productsrow Product = products.
 Findbyproductid (ProductID); if (product!= null) {//programmatically access the form field elements in the//current GridViewRow TextBox Pro
  Ductname = (TextBox) gvrow.findcontrol ("ProductName");
  DropDownList categories = (DropDownList) gvrow.findcontrol ("categories");
  TextBox UnitPrice = (textbox) Gvrow.findcontrol ("UnitPrice");

  CheckBox discontinued = (checkbox) Gvrow.findcontrol ("discontinued"); Assign the user-entered values to the current Productrow product.
  ProductName = ProductName.Text.Trim (); if (categories.SelectedIndex = = 0) product.
  Setcategoryidnull (); else product. CategoryID = Convert.ToInt32 (categories.
  SelectedValue); if (UnitPrice.Text.Trim (). Length = = 0) product.
  Setunitpricenull (); else product.
  UnitPrice = Convert.todecimal (Unitprice.text); Product. Discontinued = discontinued.
 Checked;
}//now have the BLL update the products data using a transaction productsapi.updatewithtransaction (products);

 }

This method invokes the GetProducts method of the BLL layer to obtain all the products through a productsdatatable. Then iterate through the Rows collection set of the GridView control, which rows The collection set contains GridViewRow instance instances for each row in the GridView. Because there are up to 10 rows per page in the GridView, the Rows collection set of the GridView control contains no more than 10 barcodes.

The ProductID from each row is derived from the DataKeys collection set and the corresponding Productsrow is selected from Productsdatatable. These 4 TemplateField The value of the input control is assigned to the properties of the Productsrow instance instance. When the productsdatatable update completes, it goes to the Updatewithtransaction method of the BLL business logic layer, as we saw in the previous tutorial, This method only invokes the Updatewithtransaction method of the DAL data access layer.

The batch update strategy used in this article is to update all rows in the productsdatatable that correspond to each row in the GridView, regardless of whether the user has changed the product information. This blind change is not a problem to execute, but will cause the database Extra records appear in the table. In the previous 37th chapter, "DataList batch Update", we examined the batch update interface of the DataList control, in which we used the hungry code to update only those records that were actually changed by the user. If you want, you can use the 37 chapter method.
Note: When you bind a data source through the smart tag of the GridView, Visual Studio automatically assigns the primary key value of the data source as the DataKeyNames property of the GridView. If you don't bind ObjectDataSource with the smart tag of the GridView, we need to manually set the GridView Control DataKeyNames property to "ProductID" to access ProductID values through the DataKeys collection set.

The code in the BatchUpdate method is very similar to the code in the UpdateProduct methods method in the BLL business logic layer, and the main difference is that the UpdateProduct method simply gets a single methods Instance instance. The code for assigning properties to Productrow in the Updateproducts methods method is exactly the same as the code in the Foreach loop in the BatchUpdate method.

Finally, when you click on any of the "Update products" button, the BatchUpdate method is invoked to create an event handler for the Click events event for these 2 buttons, adding the following code in it:

BatchUpdate ();

Clientscript.registerstartupscript (this. GetType (), "message",
 "alert" (' The products have been updated. '); ", true);

The above code first calls the BatchUpdate () method, and then uses the ClientScript property attribute to inject JavaScript to display a MessageBox that prompts "the products have been updated."

Take a few minutes to test the code. In the browser login batchupdate.aspx page, edit a few lines of records, click any "Update products" button. Assuming the input is correct, you will see a message box showing "the products have been updated." To test atomic operations, you can add any check constraint, such as "1234.56", which does not accept the UnitPrice value. Then log on to the Batchupdate.aspx page, edit a few lines of records, and make sure that the UnitPrice value for one of the records is set to "1234.56". When the "Update Products" button is clicked, an error occurs. The result is that all operations are rolled back and back to their original values.

Another alternative method of BatchUpdate

Above we explore the BatchUpdate method from the BLL business Logic layer GetProducts method to obtain all the products.
If the GridView does not have paging enabled, everything will be perfect. What if paging is enabled? For example, there may be a total of hundreds of, thousands of, tens of thousands of product records, and the GridView only shows 10 records per page. In this case, the method gets all the records, but only 10 of them are updated, which is hard to say perfect.

In the face of this situation, consider using the following batchupdatealternate instead:

private void Batchupdatealternate () {//Enumerate the GridView ' s Rows collection and create a Productrow PRODUCTSBLL p
 Roductsapi = new PRODUCTSBLL ();

 Northwind.productsdatatable products = new northwind.productsdatatable (); foreach (GridViewRow gvrow in productsgrid.rows) {//Create a new productrow instance int productID = Convert.ToInt32 ( Productsgrid.datakeys[gvrow.rowindex].
 
 Value);
 Northwind.productsdatatable currentproductdatatable = Productsapi.getproductbyproductid (ProductID);

  if (CurrentProductDataTable.Rows.Count > 0) {northwind.productsrow product = currentproductdatatable[0]; Programmatically access the form field elements in the//current gridviewrow TextBox productName = (textbox) Gvrow.
  FindControl ("ProductName");
  DropDownList categories = (DropDownList) gvrow.findcontrol ("categories");
  TextBox UnitPrice = (textbox) Gvrow.findcontrol ("UnitPrice");

  CheckBox discontinued = (checkbox) Gvrow.findcontrol ("discontinued"); Assign theUser-entered values to the current Productrow product.
  ProductName = ProductName.Text.Trim (); if (categories. SelectedIndex = = 0) product.
  Setcategoryidnull (); else product. CategoryID = Convert.ToInt32 (categories.
  SelectedValue); if (UnitPrice.Text.Trim (). Length = = 0) product.
  Setunitpricenull (); else product.
  UnitPrice = Convert.todecimal (Unitprice.text); Product. Discontinued = discontinued.

  Checked; Import the Productrow into the products of the DataTable products.
 ImportRow (product); }//now have the BLL update the product data using a transaction productsapi.updateproductswithtransaction
s);

 }

The method first creates a blank productsdatatable named Products, and then passes through the getproductbyproductid of the BLL business Logic layer (ProductID) method to obtain specific product information. The Productsrow instance instance that is obtained updates its properties, as BatchUpdate () does. After the update is completed, the row is imported to the productsdatatable named Products by the ImportRow (DataRow) method.

After the Foreach Loop completes, the products will contain productsrowinstance instances that correspond to each row of records in the GridView, as these instances are added (rather than updated) to the products, If we blindly pass the Updatewithtransaction method, Productstableadatper inserts each record into the database. Here, we must declare that only those rows are updated (not added).

To do this, we need to add a method named Updateproductswithtransaction to the business logic layer to achieve the above goal. The method, as shown in the following code, sets the RowState of each Productsrow instances instance in productsdatatable to modified, The productsdatatable is then passed to the Updatewithtransaction method of the DAL data access layer.

public int updateproductswithtransaction (northwind.productsdatatable products)
{
 //Mark each product as Modified Products
 . AcceptChanges ();
 foreach (Northwind.productsrow product in products)
 product. SetModified ();

 Update the data via a transaction return
 Updatewithtransaction (products);
}

Summarize:

The built-in editing capabilities of the GridView control can only be edited for each row and powerless for batch edits. As this article explores, to create a batch interface we need to do more work. To do this, we need to convert the columns in the GridView to Templatefields and define the editing interface in the itemtemplates template, plus the Update All button on the page, which is separate from the GridView. The click of the button Event events must be sure to traverse the collection set of the GridView, store the change information in a productsdatatable, and then pass it to the appropriate BLL business logic layer.

In the next installment, we'll look at how to create a batch-deletion interface, specifically, each GridView row will contain a checkbox. In addition, we will replace the "Update all" button with a "Delete Selected Rows" button.

I wish you a happy programming!

Author Introduction

The author of this series of tutorials, Scott Mitchell, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.