Manipulating data in asp.net 2.0: displaying statistics in the footer of the GridView _ self-study process

Introduction

In addition to the need to understand the product unit price, inventory and order volume, and ranked by rank, users may also be interested in statistical information, such as average price, inventory volume and so on. These statistics are often displayed in one of the following statistics lines in the report. The GridView control can contain a footer row, and we can programmatically insert the statistics into its cells. This task gives us the following 3 challenges:

1. Configure the GridView to display its footer row

2. Determination of statistical data. How should we calculate the average price and the total inventory?

3. Insert statistics into the corresponding cells in the footer row

In this tutorial, we will see how to conquer these challenges. In addition, we will create a page that contains a drop-down box listing all categories, and select a category to display the product in the GridView. The GridView contains a footer row that shows the average price, total inventory, and total order of such products.

Figure one: Statistics displayed in the footer row of the GridView

All of this section is based on the concepts discussed in the previous "Use DropDownList for master/Detail Filtering" section, due to the master/detail interface with "categories" to "products". If you have not seen the section, before continuing this section of the study, or to see better.

Section one: Adding the Categories Drop-down box and the products GridView

Before adding a footer row to the GridView, let's start by simply building a master/from report. Once we have completed this first step, we can look at how to add statistics.

First open the Summarydatainfooter.aspx page in the Customformatting folder. Add a DropDownList control and set its ID to categories. Then, on the Smart tab of this DropDownList, click on "Select Data Source (Choose)" To add a new call to the CATEGORIESBLL class GetCategories () The ObjectDataSource control of the method, naming this ObjectDataSource as Categoriesdatasource.

Figure II: Adding a new ObjectDataSource control named Categoriesdatasource

Figure Three: The GetCategories () method that makes this ObjectDataSource control call the CATEGORIESBLL class

Once the ObjectDataSource is configured, the wizard will return us to the DropDownList Data Source Configuration Wizard, where we can specify which field to display and which field should be the value of the DropDownList ListItem. We'll show the CategoryName field and take the CategoryID as a value.

Figure IV: Using CategoryName and CategoryID respectively as the text and value of ListItem

Now we have a DropDownList in the system that can list the categories. Now we need to add a GridView that lists the products according to the selected category. Before that, however, let's take a moment to check the "Enable automatic postback (enabled AutoPostBack)" checkbox in the Smart tab of DropDownList. As we discussed in the previous section "Using DropDownList for primary/Lidong6 filtering", when the AutoPostBack property of DropDownList is set to true, the page is sent back as soon as the value of DropDownList has changed. This allows the GridView to be refreshed to display the products of the newly selected category. If the AutoPostBack property is set to False (the default), changing the category will not result in a postback, so the product list cannot be refreshed.

Figure Five: Check the "Enable automatic postback" checkbox in the Smart tab of DropDownList

Add a GridView control to the page so that you can display the product based on the selected category. Set the ID of this gridview to productsincategory and bind it to a new ObjectDataSource named Productsincategorydatasource.

Figure VI: Add a new ObjectDataSource named Productsincategorydatasource

Configure this ObjectDataSource so that it invokes the Getproductsbycategoryid (CategoryID) method of the Productsbll class.

Figure VII: Make this ObjectDataSource call the Getproductsbycategoryid (CategoryID) method

Since the Getproductsbycategoryid (CategoryID) method requires a parameter, in the final step of the wizard, we can specify the data source for the parameter value. This parameter should be obtained from the Categories Drop-down box in order to display the product based on the display of the selected category.

Figure eight: Getting the CategoryID parameter from the Catefories dropdown box

Once the wizard is complete, the GridView will contain some BoundField that correspond to each of the attributes of the product. Let's clean up these BoundField, leaving the ProductName, UnitPrice, UnitsInStock and UnitsOnOrder to be shown. And then you can just give the rest of these BoundField. Add some field-level settings (for example, to format the UnitPrice as currency). Once these changes have been made, the declaration mark for this gridview should look like this:

<asp:gridview id= "productsincategory" runat= "Server" autogeneratecolumns= "False" datakeynames= "ProductID" Datasourceid= "Productsincategorydatasource" enableviewstate= "False" > <Columns> <asp:boundfield Datafield= "ProductName" headertext= "Product" sortexpression= "ProductName"/> <asp:boundfield "datafield=" Tprice "dataformatstring=" {0:c} "headertext=" Price "htmlencode=" False "sortexpression=" UnitPrice "> &L
     T;itemstyle horizontalalign= "right"/> </asp:BoundField> <asp:boundfield datafield= "UnitsInStock" Headertext= "Units in the Stock" sortexpression= "UnitsInStock" > <itemstyle horizontalalign= "right"/> </ asp:boundfield> <asp:boundfield datafield= "UnitsOnOrder" headertext= "Units on Order" sortexpression= "Units Onorder "> <itemstyle horizontalalign=" right "/> </asp:BoundField> </Columns> </asp:gri Dview>

Now we have a full-featured master/from report that can display the name, Unit price, inventory, and order quantity of the related product according to the selected category.

Figure Nine: The effect of the present (translator Note: The original estimate is wrong here, written as shown in Figure eight.) The original text here is "Figure 9:get the CategoryID Parameter Value from the Selected Categories", the original of Figure Eight is "DropDownList Figure t He CategoryID Parameter Value from the Selected Categories DropDownList ")

Step two: Show the footer in the GridView

The GridView control can display header and footer rows. Whether the rows are displayed depends on the Showheader and Showfooter properties, and by default the Showheader value is true, and the Showfooter value is false. To display the footer row, we simply set the Showfooter property to True.

Figure 10: Set the Showfooter property of the GridView to True

Each field defined in the GridView has a corresponding cell in the footer row, but the cells are empty by default. Take some time to look at our results in the browser. Since we set the Showfooter property of the GridView to True, the GridView now contains an empty footer row.

Figure 11: Now, the GridView has a footer row

The footer row in Figure Xi is not obvious because it has a white background. Let's create a CSS class called FooterStyle in Styles.css that specifies a crimson background and configures Gridview.skin this skin file in the DataWebControls theme (Skin File) To assign this CSS class to the FooterStyle CssClass property of this gridview. If you need to review the skin and subject matter, refer to "Using ObjectDataSource to display data."

First, add the following CSS class to Styles.css:

. FooterStyle
{
  background-color: #a33;
  Color:white;
  Text-align:right;
}

FooterStyle This CSS class is the same as the HeaderStyle class, except that the HeaderStyle background color is a bit darker and the text is shown in bold. In addition, the footer text is right-aligned, and the text of the header is centered. Then, to associate this CSS class with the footer of each gridview, open the Gridview.skin file in the DataWebControls topic and set the CssClass property of the FooterStyle. With this addition, the markup code for the file should look like this:

<asp:gridview runat= "Server" cssclass= "Datawebcontrolstyle" >
  <alternatingrowstyle cssclass= " AlternatingRowStyle "/>
  <rowstyle cssclass=" RowStyle "/>  
 

As shown in the screenshot below, this change makes the footer appear clearly.

Figure 12: The footer of the GridView now has a red background color

Step three: Calculate statistical data

After displaying the footer for the GridView, the next challenge is to calculate the statistics. There are two ways to compute statistical information:

1. Through an SQL query--we can send an extra query to the database to compute the statistics for a particular category. SQL contains a series of aggregate functions, and the GROUP BY clause specifies what data should be statistically based. The following SQL query will return the information we need:

SELECT CategoryID, AVG (UnitPrice), sum (UnitsInStock), sum (UnitsOnOrder) from the products
WHERE CategoryID = CategoryID
GROUP by CategoryID

Of course, you may not like to directly execute the query directly on the summarydatainfooter.aspx page, but you want to create a method in ProductsTableAdapter and PRODUCTSBLL to do it.

2. Since this information has been added to the GridView, it can be computed directly--as discussed in "Custom formatting based on data", after the data binding of the GridView, The RowDataBound event-handling method of the GridView is executed once for each row of data added. After creating an event-handling method for this event, we can maintain a cumulative aggregate value. After the last row of data is bound to the DataGrid, we have an aggregate value and the information that needs to be averaged.

Generally speaking, I prefer the second method, because it saves a round-trip to the database, and to achieve this effect also needs to be in the data access layer and the business logic layer to achieve statistical functions, but then, in fact, both methods are OK. In this tutorial, let's use the second option and use the RowDataBound event-handling method to record this cumulative total.

To create a new RowDataBound event handling method for the GridView, you can select the GridView in the designer and click on the icon with the lightning symbol in the Properties window to find the RowDataBound event and double-click it. This adds a new event-handling method named Productsincategory_rowdatabound to the back code class of the Summarydatainfooter.aspx page.

protected void Productsincategory_rowdatabound
  (object sender, GridViewRowEventArgs e)
{
}

In order to maintain a cumulative total, we need to define some variables outside of this event-handling method. Create the following 4 page-level variables:

_totalunitprice, type is decimal

_totalnonnullunitpricecount, type int

_totalunitsinstock, type int

_totalunitsonorder, type int

Then, write some code in the RowDataBound event-handling method so that each row of data can increase the value of those variables.

//Class-scope, running total variables ... decimal _totalunitprice = 0m; int _totalnon
Nullunitpricecount = 0;
int _totalunitsinstock = 0;
int _totalunitsonorder = 0; protected void Productsincategory_rowdatabound (object sender, GridViewRowEventArgs e) {if (E.row.rowtype = = Datacontro 
     Lrowtype.datarow) {//Reference the Productsrow via the E.row.dataitem property Northwind.productsrow Product = (Northwind.productsrow) ((System.Data.DataRowView) e.row.dataitem).
    Row;
    Increment The running totals (if they are not null!) if (!product. Isunitpricenull ()) {_totalunitprice = = product.
      UnitPrice;
    _totalnonnullunitpricecount++; } if (!product. Isunitsinstocknull ()) _totalunitsinstock + = product.
    UnitsInStock; if (!product. Isunitsonordernull ()) _totalunitsonorder + = product.
  UnitsOnOrder; }
} 

In the RowDataBound event-handling approach, we should first make sure that we are manipulating a DataRow. Once this is confirmed, the Northwind.productsrow instance in E.row that just binds to the GridViewRow object can be assigned to the product variable. The cumulative totals are then added to the value of the current product (and, of course, we should make sure that they do not contain a database null value). We also recorded the cumulative sum of UnitPrice and the number of UnitPrice records, since the average price is the quotient of these two numbers.

Step Fourth: Show the statistics in the footer

After calculating the statistics, the final step is to display it on the footer of the GridView. Similarly, this task can be accomplished by RowDataBound event-handling methods. Recall the RowDataBound event handling method, which is triggered when each row is bound to the GridView, and the footer row is no exception. So we can extend our event-handling method so that it can display the data in the footer row in the following code:

protected void Productsincategory_rowdatabound
  (object sender, GridViewRowEventArgs e)
{
  if (e). Row.rowtype = = Datacontrolrowtype.datarow)
  {
   ... Increment The running totals ...
  }
  else if (E.row.rowtype = = Datacontrolrowtype.footer)
  {
   ... Display the summary data in the footer ...
  }
}

Because the footer row is added to the GridView after all the rows of data have been added, we can believe that the cumulative totals have been computed before the statistics are displayed in the footer. The final step is to put the values in the footer cell.

To display text in a specific cell in the footer, you can use E.row.cells[index. Text = value, where the index of the cell starts at 0. The following code calculates the average price (the total price divided by the number of products) and displays it to the corresponding cell in the GridView footer, along with inventory and order quantities.

protected void Productsincategory_rowdatabound
  (object sender, GridViewRowEventArgs e)
{
  if (e). Row.rowtype = = Datacontrolrowtype.datarow)
  {
   ... <i>increment the running totals</i> ...
  }
  else if (E.row.rowtype = datacontrolrowtype.footer)
  {
   //Determine the average UnitPrice
   decimal Avgunitprice = _totalunitprice/(decimal) _totalnonnullunitpricecount;
   Display the summary data in the appropriate cells
   e.row.cells[1]. Text = "AVG.:" + avgunitprice.tostring ("C");
   E.ROW.CELLS[2]. Text = "Total:" + _totalunitsinstock.tostring ();
   E.ROW.CELLS[3]. Text = "Total:" + _totalunitsonorder.tostring ();
  }
}

Figure 13 shows how this report looks after adding this code. Notice how ToString ("C") makes the average price this statistic formatted in currency form.

Figure 13: The effect of the present (translator Note: The original estimate is wrong here again, written as shown in Figure 12.) The original text here is "Figure 13:the GridView's Footer Row now has a reddish Background Color", as shown in Figure 12. The original text here is "Figure 13:the GridView's Footer row now has a reddish B is" Figure 12:the GridView ' s Footer row now has a reddish Background Color ")

Summarize

Displaying statistics is a common reporting requirement, and the GridView control can contain such information in the footer row, and it makes this operation very simple. The footer row can be displayed when the Showfooter property of the GridView is set to true, and the information can be displayed in its different cells through the RowDataBound event-handling method. You can either requery the database's data or programmatically compute the statistics in the back code class of the ASP.net page.

This tutorial concludes our study of custom formatting for GridView, DetailsView, and FormView controls. In the next tutorial, we'll start a quest to use these controls for the incremental pruning operation.

Happy programming!

About the author

Scott Mitchell, with six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. Scott is an independent technical consultant, trainer, writer, recently completed a new book to be published by Sams Press, proficient in asp.net 2.0 within 24 hours. His contact email is mitchell@4guysfromrolla.com, or he can contact him through his blog Http://ScottOnWriting.NET.