Summary Rows in DataGrid Controls Sample 1

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