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