<%@ Page language="c#" Codebehind="WebForm6.aspx.cs" AutoEventWireup="false" Inherits="c4.WebForm6" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<title>Summary Rows</title>
<style>
HR { COLOR: black; HEIGHT: 2px }
.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 }
.StdText { FONT-SIZE: x-small; FONT-FAMILY: verdana }
</style>
<BODY bgcolor="ivory" style="FONT-SIZE:small;FONT-FAMILY:verdana">
<!-- ASP.NET Form -->
<form runat="server" ID="Form1">
<!-- Grid and the remainder of the page -->
<table>
<tr>
<td valign="top">
<asp:DataGrid id="grid" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="15"
Font-Size="xx-small" CellSpacing="0" CellPadding="4" DataKeyField="MyCustomerId" BorderStyle="solid"
BorderColor="skyblue" BorderWidth="1" GridLines="both">
<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="MyCustomerId" 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>
</td>
<td valign="top" width="20"></td>
<td valign="top">
<b>Year</b>
<asp:dropdownlist runat="server" id="ddYears">
<asp:listitem runat="server" ID="Listitem1" NAME="Listitem1">1998</asp:listitem>
<asp:listitem runat="server" ID="Listitem2" NAME="Listitem2">1997</asp:listitem>
<asp:listitem runat="server" ID="Listitem3" NAME="Listitem3">1996</asp:listitem>
</asp:dropdownlist>
<asp:linkbutton runat="server" text="Load..." onclick="OnLoadYear" ID="Linkbutton1" NAME="Linkbutton1" />
<br>
<br>
<asp:label runat="server" cssclass="StdText" id="lblMsg" />
</td>
</tr>
</table>
<hr>
</form>
</BODY>
</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
{
/// <summary>
/// WebForm6 的摘要说明。
/// </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);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </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();
}
}
}
