#region Summary Rows in DataGrid Controls very good
<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" OnItemDataBound="grid_ItemDataBound">
<headerstyle backcolor="skyblue" font-size="9pt" font-bold="true" />
<itemstyle backcolor="#eeeeee" />
<pagerstyle backcolor="skyblue" Font-Names="webbings"
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>
/// <summary>
/// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data01102002.asp
/// </summary>
/// <returns></returns>
private DataSet PhysicalDataRead()
{
string strCnn = "SERVER=localhost;DATABASE=northwind;Integrated Security=SSPI;";
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, strCnn);
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;
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void ItemCreated(Object sender, DataGridItemEventArgs e)
{
//foreach(DataGridItem e in grid.Items)
//{
// Get the type of the newly created item
ListItemType itemType = e.Item.ItemType;
if (itemType == ListItemType.Item ||
itemType == ListItemType.AlternatingItem)
{
// Get the data bound to the current row
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 style and layout here.
e.Item.BackColor = Color.White;
e.Item.Font.Bold = true;
//e.Item.Cells.RemoveAt(0); // remove CustID
//e.Item.Cells.RemoveAt(0); // remove Order #, now the first
// 利用语句e.Item.ItemType = ListItemType.Header来找到DataGrid的Header；
//2、 清除Header中的缺省控件；
// e.Item.Controls.Clear()
//// Span and right-align the cell left
//e.Item.Cells[0].ColumnSpan = 3;
//e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
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;
e.Item.Cells[0].Text = "Total is";
}
}
//}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
//public void ItemDataBound(Object sender,DataGridItemEventArgs e)
//{
// //foreach (DataGridItem e in grid.Items)
// //{
// DataRowView drv = (DataRowView)e.Item.DataItem;
// if (drv == null)
// return;
// 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";
// }
// //}
//}
protected void grid_ItemDataBound(object sender, DataGridItemEventArgs e)
{
DataRowView drv = (DataRowView)e.Item.DataItem;
if (drv == null)
return;
if ((int)drv["MyOrderID"] == -1)
{
if (drv["MyCustID"].ToString() == "(Total)")
{
e.Item.BackColor = Color.Yellow;
e.Item.Cells[0].Text = "Orders total";
}
else
e.Item.Cells[0].Text = "Customer subtotal";
}
}
protected void PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
}
protected void btSum_Click(object sender, EventArgs e)
{
grid.DataSource = PhysicalDataRead();
grid.DataBind();
}
#endregion
