.Net資源 - 建立可加總的Datagrid

Summary.cs

// Summary.cs - code-behind file

namespace BWSLib
{
    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Text;

    public class MyPage : Page
    {
        // Declare as PUBLIC or PROTECTED members all
        // the controls in the layout
        protected DataGrid grid;
        protected Label lblMsg;
        protected DropDownList ddYears;

        // Page OnLoad
        protected override void OnLoad(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();
        }

        // EVENT HANDLER: ItemCreated            
        public void ItemCreated(Object sender, 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;
                    }

                }
            }                    
        }

        // EVENT HANDLER: PageIndexChanged
        public void PageIndexChanged(Object sender, DataGridPageChangedEventArgs e)
        {
            grid.CurrentPageIndex = e.NewPageIndex;
            UpdateDataView();
        }

        // EVENT HANDLER: ItemDataBound
        public void ItemDataBound(Object sender, 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 = "Orders total";
                }
                else
                    e.Item.Cells[0].Text = "Customer subtotal";
            }
        }

        public void OnLoadYear(Object sender, EventArgs e)
        {
            DataFromSourceToMemory("MyDataSet");
            UpdateDataView();
        }
    }
}

Summary.cs

‹%@ Page Language="C#" Inherits="BWSLib.MyPage" Src="Summary.cs" Trace="false" %›

‹html›
‹title›Summary Rows‹/title›
‹style›
  a                {behavior:url(....mouseover.htc);}
  hr            {height:2px;color:black;}
  .StdTextBox    {font-family:verdana;font-size:x-small;border:solid 1px black;filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true');}
  .StdText        {font-family:verdana;font-size:x-small;}
‹/style›


‹BODY bgcolor="ivory" style="font-family:verdana;font-size:small"›
‹h2›Orders and Customers‹/h2›

‹!-- ASP.NET Form --›
‹form runat="server"›

‹!-- 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"
    OnItemCreated="ItemCreated"
    OnItemDataBound="ItemDataBound"
    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="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="20px"›‹/td›
‹td valign="top"›
    ‹b›Year‹/b›
    ‹asp:dropdownlist runat="server" id="ddYears"›
        ‹asp:listitem runat="server" ›1998‹/asp:listitem›
        ‹asp:listitem runat="server" ›1997‹/asp:listitem›
        ‹asp:listitem runat="server" ›1996‹/asp:listitem›
    ‹/asp:dropdownlist>
    
    ‹asp:linkbutton runat="server" text="Load..." onclick="OnLoadYear" />
    ‹br>‹br>
    ‹asp:label runat="server" cssclass="StdText" id="lblMsg" />
‹/td>
‹/tr>‹/table>
‹hr>
‹/form>
‹/body>
‹/html›