
<%@ 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(); } } }

|