1@ Page language="c#" Codebehind="WebForm6.aspx.cs" AutoEventWireup="false" Inherits="c4.WebForm6"
1<html>
2<title>Summary Rows</title>
3<style>
4HR { COLOR: black; HEIGHT: 2px }
5.StdTextBox { BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-SIZE: x-small; FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true'); BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-FAMILY: verdana }
6.StdText { FONT-SIZE: x-small; FONT-FAMILY: verdana }
7</style>
8<body bgcolor="ivory" style="FONT-SIZE:small;FONT-FAMILY:verdana">
9<!-- ASP.NET Form -->
10<form id="Form1" runat="server">
11<!-- Grid and the remainder of the page -->
12<table>
13<tr>
14<td valign="top">
15<asp:datagrid allowpaging="true" autogeneratecolumns="false" bordercolor="skyblue" borderstyle="solid" borderwidth="1" cellpadding="4" cellspacing="0" datakeyfield="MyCustomerId" font-size="xx-small" gridlines="both" id="grid" pagesize="15" runat="server">
16<headerstyle backcolor="skyblue" font-bold="true" font-size="9pt"></headerstyle>
17<itemstyle backcolor="#eeeeee"></itemstyle>
18<pagerstyle backcolor="skyblue" font-name="webdings" font-size="10pt" nextpagetext="4" prevpagetext="3"></pagerstyle>
19<columns>
20<asp:boundcolumn datafield="MyCustomerId" headertext="Customer"></asp:boundcolumn>
21<asp:boundcolumn datafield="MyOrderId" headertext="Order #"></asp:boundcolumn>
22<asp:boundcolumn datafield="price" dataformatstring="{0:c}" headertext="Amount">
23<itemstyle horizontalalign="right"></itemstyle>
24</asp:boundcolumn>
25</columns>
26</asp:datagrid>
27</td>
28<td valign="top" width="20"></td>
29<td valign="top">
30<b>Year</b>
31<asp:dropdownlist id="ddYears" runat="server">
32<asp:listitem id="Listitem1" name="Listitem1" runat="server">1998</asp:listitem>
33<asp:listitem id="Listitem2" name="Listitem2" runat="server">1997</asp:listitem>
34<asp:listitem id="Listitem3" name="Listitem3" runat="server">1996</asp:listitem>
35</asp:dropdownlist>
36<asp:linkbutton id="Linkbutton1" name="Linkbutton1" onclick="OnLoadYear" runat="server" text="Load..."></asp:linkbutton>
37<br/>
38<br/>
39<asp:label cssclass="StdText" id="lblMsg" runat="server"></asp:label>
40</td>
41</tr>
42</table>
43<hr/>
44</form>
45</body>
46</html>
//cs代码
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Text;
namespace c4
{
///
1<summary>
2/// WebForm6 的摘要说明。
3/// </summary>
public class WebForm6 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid grid;
protected System.Web.UI.WebControls.DropDownList ddYears;
protected System.Web.UI.WebControls.LinkButton Linkbutton1;
protected System.Web.UI.WebControls.Label lblMsg;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
// Load data and refresh the view
DataFromSourceToMemory("MyDataSet");
UpdateDataView();
}
}
// DataFromSourceToMemory
private void DataFromSourceToMemory(String strDataSessionName)
{
// Gets rows from the data source
DataSet oDS = PhysicalDataRead();
// Stores it in the session cache
Session[strDataSessionName] = oDS;
}
// PhysicalDataRead
private DataSet PhysicalDataRead()
{
String strCnn = "server=localhost;initial catalog=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 THEN o.customerid ELSE '(Total)' END AS MyCustomerID, ");
sb.Append(" CASE GROUPING(od.orderid) WHEN 0 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) = @TheYear 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();
da.SelectCommand = cmd;
// Set the "year" parameter
SqlParameter p1 = new SqlParameter("@TheYear", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = Convert.ToInt32(ddYears.SelectedItem.Text);
cmd.Parameters.Add(p1);
// The DataSet contains two tables: Orders and Orders1.
// The latter is renamed to "OrdersSummary" and the two will be put into
// relation on the CustomerID field.
DataSet ds = new DataSet();
da.Fill(ds, "Orders");
return ds;
}
// Refresh the UI
private void UpdateDataView()
{
// Retrieves the data
DataSet ds = (DataSet) Session["MyDataSet"];
DataView dv = ds.Tables["Orders"].DefaultView;
// Re-bind data
grid.DataSource = dv;
grid.DataBind();
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
///
1<summary>
2/// 设计器支持所需的方法 - 不要使用代码编辑器修改
3/// 此方法的内容。
4/// </summary>
private void InitializeComponent()
{
this.grid.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemCreated);
this.grid.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.grid_PageIndexChanged);
this.grid.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemDataBound);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void grid_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// Get the newly created item
ListItemType itemType = e.Item.ItemType;
///////////////////////////////////////////////////////////////////
// ITEM and ALTERNATINGITEM
if (itemType == ListItemType.Item || itemType == ListItemType.AlternatingItem)
{
DataRowView 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 ((int) drv["MyOrderID"] == -1)
{
// Modify the row layout as needed. In this case,
// + change the background color to white
// + Group the first two cells and display company name and #orders
// + Display the total of orders
// Graphical manipulations can be done here. Manipulations that require
// data access should be done hooking ItemDataBound. They can be done
// in ItemCreated only for templated columns.
e.Item.BackColor = Color.White;
e.Item.Font.Bold = true;
e.Item.Cells.RemoveAt(1); // remove the order # cell
e.Item.Cells[0].ColumnSpan = 2; // span the custID cell
e.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;
}
}
}
}
private void grid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
UpdateDataView();
}
private void grid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// Retrieve the data linked through the relation
// Given the structure of the data ONLY ONE row is retrieved
DataRowView drv = (DataRowView) e.Item.DataItem;
if (drv == null)
return;
// Check here the app-specific way to detect whether the
// current row is a summary row
if ((int) drv["MyOrderID"] == -1)
{
if (drv["MyCustomerID"].ToString() == "(Total)")
{
e.Item.BackColor = Color.Yellow;
e.Item.Cells[0].Text = "订单总计";
}
else
e.Item.Cells[0].Text = "客户小计：";
}
}
public void OnLoadYear(Object sender, EventArgs e)
{
DataFromSourceToMemory("MyDataSet");
UpdateDataView();
}
}
}