Summary row in the DataGrid Control

View summary. CS

View summary. aspx

ASP. NETDataGridThe control presents a fully-templated grid with multiple columns. It is the most common and flexible control for all data-bound Web controls in the. NET Framework. To some extent, the DataGrid user interface is similar to a Microsoft Excel worksheet. Although DataGrid has advanced programming interfaces and complete attribute sets, it only generates HTML tables that contain interleaved superlinks to provide interoperability functions (such as sorting and paging commands ).

Using the DataGrid control, you can create simple data binding columns (displaying data retrieved from the data source) and templated columns (allowing you to design the layout of cell content ), last but most importantly, it is a command-based column (allowing you to add specific features to the grid ).

The DataGrid Control is suitable for reporting data, and it is flexible enough to allow you to build complex and professional data tables that allow you to freely implement functions such as paging and sorting. However, other functions (such as deepening and master/detail) only require a small amount of work. In this month's column, I will discuss a feature that cannot be provided by the control itself, but is quite popular with many people. Therefore, let's take a look at how to automatically generate complex reports. In these tables, a summary row with partial aggregate must be displayed.

DataGrid item

You can setDatasourceProperty to bind the actual data to the instance of the control. This attribute is normalObjectAnd supports two configuration schemes. Usually usedIcollectionInterface to set it. GenerallyDatatableAndDataviewObject. Another method is to set it with another object type (for example, data reader object. However, in this case, you must enable the custom paging mechanism; otherwise, an exception occurs. To put it simply, you must either bind the DataGrid to a paging data source (that is, a set object used to implement the enumeration number) or provide pagination for yourself.

For Web controls, data binding is enabled only whenDatabindMethod, the user interface is refreshed. During the refresh process, the control traverses the data source and copies some rows to itsItemsCollection.ItemsProperty indicates the content of the current display page. If the data source supports paging (that isIcollection), The DataGrid fromDatasourceSelect the correct row subset that fits the current page. Otherwise, it assumes that all the content of datasource is suitable for the current page and all of them are loaded into items. After filling in the items, the user interface of the control is displayed.

What are the lessons learned here? All the content that the DataGrid Control can safely and consistently display is the rows contained in the bound data source. Therefore, if you want to insert a summary row to group some records and display partial totals by using the public key, you must specify the method to insert these summary rows directly to the data source.

However, inserting a summary row into a data source is not enough. In fact, we must be able to distinguish between summary and general, and present the former with different visual styles.

Before attaching data to the control, make sure that the data source contains all the summary rows required by the control. Next, hookItemcreatedEvent, detect each summary row, and then draw them with different la S and styles. Let's take a look at how to insert summary rows between different SQL query rows. I will use a sample application based on the northwind SQL Server database to explain my point of view. This application lists all orders that each customer has placed in a given year. Orders are grouped by year and customer ID. Each customer has an additional line to summarize the total and total orders.

Data Group

The following SQL command Selects all orders issued by all customers in a given year. Only the total price of all items in each order is displayed.

SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price FROM Orders o, [Order Details] odWHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderidGROUP BY o.customerid, od.orderidORDER BY o.customerid

In the T-SQL language, the group by clause of the SELECT statement provides the ability to add predefined summary rows to the result setWith RollupClause. Of course, such a summary row has the layout of all other columns, but the content of each column can be customized to some extent. The following statement explains how to modify the preceding command so that it can use the summary line.

DECLARE @TheYear intSET @TheYear = 1998SELECT CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END AS MyCustomerID, CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID, SUM(od.quantity*od.unitprice) AS priceFROM Orders o, [Order Details] odWHERE Year(orderdate) = @TheYear AND od.orderid=o.orderidGROUP BY o.customerid, od.orderid WITH ROLLUPORDER BY o.customerid, price

If you copy the code snippet and paste it into the SQL query analyzer, you will see the content as shown in.

Figure 1. The with rollup clause adds the summary row to the result set.

GroupingIs a T-SQL aggregate function that works with rollup in the body of the Group by clause. Add a new column to the result set using the grouping operator. If the row has been added by the rollup operator and therefore becomes a summary row, the new column will contain the value 1. Otherwise, the column contains a value of 0. UseCase... When... endStatement can combine the new column with the group column.

In the preceding example,MycustomeridColumns are included in all rows created for grouping.CustomeridThe column value and the string "(total )". Similarly, when this row represents a small timer,MyorderidThe column contains the order ID and-1.

To summarize data, SQL Server also provides several options, for example,With cubeOperator andComputeClause. As you might imagine, although the functionality of one option is in a way that matches the functionality of another option, all these options are not exactly equivalent. In particular,With cubeA summary row is generated for each possible combination of the group and the sub-group in the result set. WhileWith RollupGrouping is performed according to the specified sequence of grouping columns. Finally,Compute(SQL Server 2000 only supports backward compatibility .)With RollupThe difference is that it returns multiple result sets and is not as efficient as rollup when processed by the query optimizer.

Show grouped data

When bound to the DataGrid Control, the result set returned by the SQL command looks as shown in.

Figure 2. Result set displayed through the DataGrid Control

The DataGrid Control Used in the example application is declared as follows:

<asp:DataGrid id="grid" runat="server" AutoGenerateColumns="false"AllowPaging="true" PageSize="15"Font-Size="xx-small"CellSpacing="0" CellPadding="4" GridLines="both"BorderStyle="solid" BorderColor="skyblue" BorderWidth="1" OnItemCreated="ItemCreated"OnPageIndexChanged="PageIndexChanged">
UseWith RollupThe data source obtained by the operator already contains all the information necessary to generate a valid report. You may have noticed that this statement adds a top line that contains the total number of orders issued by all customers. In useWith RollupIf you modify the order of grouping rows, the number and structure of the rows may change significantly. This additional line is the result of using the specific syntax. If you do not need this information, you only need to delete it from the result set before binding. Alternatively, you can move the row to the bottom of the dataset.
The code shown below demonstrates how to execute the rollup statement. The parameter read from the text box is the year to consider. The result set is temporarily stored inDatasetObject. In this example applicationSessionCache in slotDatasetObject. In the actual environment, this should be an important choice. TypicallySessionAny byte in has a reason to be there.
private DataSet PhysicalDataRead(){String strCnn = "SERVER=localhost;DATABASE=northwind;UID=sa;";SqlConnection conn = new SqlConnection(strCnn);// Command text using WITH ROLLUPStringBuilder sb = new StringBuilder("");sb.Append("SELECT ");sb.Append("  CASE GROUPING(o.customerid) WHEN 0 ");sb.Append("    THEN o.customerid ELSE '(Total)' END AS MyCustID, ");sb.Append("  CASE GROUPING(od.orderid) WHEN 0 ");sb.Append("    THEN od.orderid ELSE -1 END AS MyOrderID, ");sb.Append("  SUM(od.quantity*od.unitprice) AS price ");sb.Append("FROM Orders o, [Order Details] od ");sb.Append("WHERE Year(orderdate)=@nYear AND od.orderid=o.orderid ");sb.Append("GROUP BY o.customerid, od.orderid WITH ROLLUP ");sb.Append("ORDER BY o.customerid, price");String strCmd = sb.ToString();sb = null;SqlCommand cmd = new SqlCommand();cmd.CommandText = strCmd;cmd.Connection = conn;   SqlDataAdapter da = new SqlDataAdapter(strCmd, strConn);da.SelectCommand = cmd;// Set the "year" parameterSqlParameter p1 = new SqlParameter("@nYear", SqlDbType.Int);p1.Direction = ParameterDirection.Input;p1.Value = Convert.ToInt32(txtYear.Text);cmd.Parameters.Add(p1);DataSet ds = new DataSet();da.Fill(ds, "Orders");return ds;}
To make the summary row clearly displayed on the page of the grid, you need to change the style and layout of the summary row. This can be found inItemcreatedCompleted in the event handler. The design idea is to check the order ID to detect the summary rows, and then modify the layout and style of cells. In the result set, the characteristic of the summary row is that the Order ID is-1. Value-1 is the value of any statement used.
Case grouping (OD. orderid) When 0 then OD. orderid else-1 end as myorderid
If notOrderidColumn usageGroupingFor a summary row, the column value is null.
Modify layout and Style
The DataGrid allows you to modify the style and layout of the component cells, which can be linkedItemcreatedEvent. This event is triggered every time the control processes the subitem (header, footer, row, and page navigation. The receiving type of the event handler isDatagriditemeventargsYou can extract the type of the project from this parameter.
A summary row is a DataGrid row, and its type can beItemOrAlternatingitem. Therefore, when writing the itemcreated handler, make sure that the corresponding cell is processed only when the item type is correct. The following list summarizes the required code.
public void ItemCreated(Object sender, DataGridItemEventArgs e){// Get the type of the newly created itemListItemType itemType = e.Item.ItemType;if (itemType == ListItemType.Item || itemType == ListItemType.AlternatingItem) {// Get the data bound to the current rowDataRowView drv = (DataRowView) e.Item.DataItem;if (drv != null){// Check here the app-specific way to detect whether the // current row is a summary row:}}}
If the created item is a DataGrid item (or alternate item), you can useDataitemTo access the data bound to the row. Based on the type of the object to which the DataGrid is bound,DataitemThe property points to different row objects. If the mesh is boundDataview, Will getDatarowviewObject. If the source usesDatatableObject.DatarowObject. In this example application, I useDataviewThe object is filled with a grid. Later, the data object of a single row becameDatarowviewObject.
After you have a data row object, you can apply application-specific rules to determine whether the row is a summary row. In this example, the myorderid field of the summary row is set to-1.
if ((int) drv["MyOrderID"] == -1){   // Modify style and layout here.    //    --> Set the background color to white and use bold font   e.Item.BackColor = Color.White; e.Item.Font.Bold = true;    }
The DataGrid now looks as shown in.
Figure 3. Summary rows in bold and white background
The row in the DataGrid is actually only one row in the table. Similarly, you can use it to delete cells and adjust other cells. Let's take a look at how to present a summary row using a single cell that spans all existing columns.
if ((int) drv["MyOrderID"] == -1)
Figure 4. Summary rows with custom la s
In the three original cells, the first two are deleted, and the third (now including index 0) is correctly aligned and spans the width of the External table. If you want to display custom text on the summary line, you need to prepare for other problems.
Suppose you need to add some text to comment on the subtotal, and at the same time, let the subtotal and a single order amount appear in the same column. In this case, you only need to delete a cell.
e.Item.Cells.RemoveAt(1);         // remove the order # celle.Item.Cells[0].ColumnSpan = 2;      // span the custID celle.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;e.Item.Cells[0].Text = "Total is";
The result of this Code is as follows. As you can see, it is not exactly the same as your expected results. The text you just set does not exist in the first cell of the summary row. What's going on?
Figure 5. Summary rows with modified custom la s
The important thing to consider here is,ItemAndAlternatingitemAll rows are bound to rows. Their explicit text is only inOnitemdataboundEvents. As you may have guessed,OnitemdataboundThe event is triggered after this item is created. ThereforeItemcreatedAny text assigned to a cell is modified silently by an event later. You can setOnitemdataboundProperty to hookOnitemdataboundEvent.
<asp:DataGrid id="grid" runat="server" AutoGenerateColumns="false":OnItemCreated="ItemCreated"OnItemDataBound="ItemDataBound"OnPageIndexChanged="PageIndexChanged">The structure of the code for ItemDataBound is shown below.public void ItemDataBound(Object sender, DataGridItemEventArgs e){DataRowView drv = (DataRowView) e.Item.DataItem;if (drv == null)return;if ((int) drv["MyOrderID"] == -1){if (drv["MyCustomerID"].ToString() == "(Total)"){e.Item.BackColor = Color.Yellow;e.Item.Cells[0].Text = "Orders total";}elsee.Item.Cells[0].Text = "Customer subtotal";}}
The top line is drawn on the yellow background, which displays another text in other summary rows. The final DataGrid is shown as follows.
Figure 6. Final DataGrid
Summary
A good mix of SQL code and ASP. NET technology with specific application dose can achieve effective web database applications. The DataGrid Control is a cutting-edge tool that can be used to build perfect and powerful web applications for its programming functions, in addition, it supports more user-defined levels.
Dialog Box: Confirmation of key tasks
How can I display a dialog box that forces a user to confirm before starting a key task (for example, deleting a record?
Assume that you need to click a button to display such a dialog box. Okay, you only need to use someOnclickEvent client JavaScript code.
Any ASP. Net control can be followed by one or more HTML tags. The down button maps to the "button" tag. The link button maps to a script-driven hyperlink. Use the attributes set of the ASP. Net Control to register the script code segment for both tags. For example, if you have a button (whether it isLinkbuttonObject orButtonObject), you can define the JavaScript code to run after the button is clicked, as shown below:
String JS = "Return confirm ('Do you really want to delete the record? '); "; BTN. attributes [" onclick "] = JS; in this way, the HTML Tag contains an attribute: <? Onclick = "Return confirm ('? ') ">
When you click the link, The onclick client code is run. If you click NO, the event is automatically abandoned. This behavior is embedded into the logic of the browser, which is almost irrelevant to ASP. NET. If the onclick client processing program successfully exits_ DopostbackFunction, and the page is sent back to the server.