ASP. NET DataGrid Control Data grouping operation

ASP. NET DataGrid Control Data grouping operation

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] od  
  
  
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid  
  
  
GROUP BY o.customerid, od.orderid  
  
  
ORDER 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 int 
  
  
SET @TheYear = 1998  
  
  
 
  
  
SELECT   
  
  
    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 price  
  
  
FROM Orders o, [Order Details] od  
  
  
WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid  
  
  
GROUP BY o.customerid, od.orderid WITH ROLLUP 
  
  
ORDER 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.

ASP. NET DataGrid Control: 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,COMPUTESQL 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.

ASP. NET DataGrid Control: displays grouped data

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

ASP. NET DataGrid Control: 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"> 
  
  
 
  
  
< headerstyle backcolor="skyblue" font-size="9pt" font-bold="true" /> 
  
  
< itemstyle backcolor="#eeeeee" /> 
  
  
< pagerstyle backcolor="skyblue" font-name="webdings"   
  
  
font-size="10pt" PrevPageText="3" NextPageText="4" /> 
  
  
 
  
  
< Columns> 
  
  
< asp:BoundColumn DataField="MyCustId" HeaderText="Customer" /> 
  
  
< asp:BoundColumn DataField="MyOrderId" HeaderText="Order #" /> 
  
  
< asp:BoundColumn DataField="price" HeaderText="Amount"   
  
  
DataFormatString="{0:c}"> 
  
  
< itemstyle horizontalalign="right" /> 
  
  
< /asp:BoundColumn> 
  
  
< /Columns> 
  
  
< /asp:DataGrid> 
 
 

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 ROLLUP  
  
  
StringBuilder 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" parameter  
  
  
SqlParameter 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.

The preceding section summarizes how to use the ASP. NET DataGrid Control to group data.

1. What is the ASP. NET DataGrid Control: Fully templated Grid
2. Comparison between ASP. NET's GridView and DataGrid controls
3. . NET beginner's Guide: easily customize the DataGridView Control
4. C # Some common operations on the DatagridView
5. ASP.net exports the DataGrid content as an excel file