Real Life SQL and .NET : using SQL with C#: Part XI

The Complete Example with SQL Statements

 

Now, we will work on it as an example that it has several SQL statements, which used to manipulate data. We will use an ASP example and DataGrid functionality to show these important issues. Especially we will see these SQL issues in the data grouping section. As you remember the ASP.NET DataGrid control renders a multi-column, fully templated grid, and is the most versatile and flexible of all data-bound Web controls in the .NET Framework. The DataGrid's user interface resembles, to some extent, a Microsoft Excel worksheet. Despite its rather advanced programming interface and a full set of attributes, the DataGrid simply generates an HTML table with interspersed hyperlinks to provide interactive functionality, such as sorting and pagination commands.

 

With the DataGrid control, you can create simple data-bound columns that show data retrieved from a data source, templated columns that let you design the layout of cell contents, and, last but not least, command-based columns that allow you to add specific functionality to a grid.

 

The DataGrid control is great for data reporting and flexible enough to let you build complex, professional-looking tables of data in which functionalities like pagination and sorting are free. Instead, other functionalities, such as drill-down and master/details, only require a little effort. In this month's column, We will begin one function that the control itself does not provide, but that many people would heartily welcome. Accordingly, let's examine how to automate the production of complex reports in which you have to show summary rows with partial totals.

 

Our Helpers: DataGrid Items

 

You bind actual data to an instance of the DataGrid control by setting the control's DataSource property. This property is a generic Object type and supports two configuration scenarios. Normally you set it with a data object that implements the ICollection interface. Typical objects you would use are DataTable and DataView. Alternatively, you set it with another object type, for instance, data readers object. In this case, though, you must turn the custom paging mechanism on; otherwise, an exception would be thrown. In brief, the DataGrid is bound to a paged data source that is a collection object that implements an enumerator or you must provide pagination for yourself.

 

For a Web control, data binding is enabled, and the user interface is refreshed only when you call the DataBind method. When this happens, the control walks its way through the data source and copies some rows into its Items collection. The Items property represents the contents of the currently displayed page. If the data source supports pagination that is, implements ICollection—the DataGrid selects from DataSource the proper subset of rows that fits into the current page. Otherwise, it assumes that the whole contents of DataSource fits into the current page and loads all of it into Items. Once Items have been filled, the control's user interface is rendered.

 

What's the lesson here? All that a DataGrid control can safely and consistently display are rows contained in the bound data source. So, if you want to insert summary rows to group some records under a common key and display partial totals, you must figure out a way to insert those summary rows directly into the data source. Having summary rows in the data source is not enough though. In fact, you must be able to distinguish summary and ordinary rows and render the former with different visual styles.

 

You make sure that the data source contains all the summary rows it needs prior to attaching the data to the control. Next, you hook up the ItemCreated event, detect each summary row, and draw them with a different layout and style. Let's look at how to insert summary rows between the rows of a SQL query. The sample application I'll use to demonstrate my points is based on the Northwind SQL Server database. The application lists all the orders that each customer has issued in a given year. Orders are grouped by year and customer ID. For each customer, an extra row summarizes the total number of orders and their total amount.

 

Grouping Data with SQL SELECT

 

Given a year, the following SQL command selects all the orders issued by all customers. Only the sum of all item prices for each order is displayed.

SELECT o.customerid, od.orderid, SUM(od.quantity * od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = @TheYear AND od.orderid = o.orderid
GROUP BY o.customerid, od.orderid
ORDER BY o.customerid

The GROUP BY clause of the SELECT statement in T-SQL language provides the WITH ROLLUP clause that adds predefined summary rows to the result set. Of course, such a summary row has the layout of all other columns, but the content of each column can be customized to some extent. The following statements illustrate how to modify the previous command to allow for summary rows.

DECLARE @TheYear int
SET @TheYear = 1998

SELECT CASE GROUPING(o.customerid) WHEN 0

THEN o.customerid ELSE '(Total)'
END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0
THEN od.orderid ELSE - 1 END AS MyOrderID,
    SUM(od.quantity * od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear AND od.orderid = o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price

GROUPING is the T-SQL aggregate function that works in conjunction with ROLLUP in the body of a GROUP BY clause. The use of the GROUPING operator causes a new column to be added to the result set. This column contains a value of 1 if the row has been added by the ROLLUP operator, and therefore, is a summary row. Otherwise, the column takes a value of 0. By using a CASE, WHEN, END statement you can merge this new column with the grouping column.

 

In the example above, the MyCustomerID column contains the value of the CustomerID column and the string '(Total)' in all rows created by grouping on that column. Likewise, the MyOrderID column contains the order ID and –1 when the row represents a subtotal.

 

To summarize data, SQL Server provides a few more options, such as the WITH CUBE operator and the COMPUTE BY clause. As you can imagine, all these options are not completely equivalent, although the functionality of one somehow intersects the working of the other. In particular, WITH CUBE generates a summary row for every possible combination of groups and subgroups in the result set. WITH ROLLUP, instead, groups hierarchically according to the order in which the grouping columns are specified. Finally, COMPUTE BY, which SQL Server 2000 supports only for backward compatibility, works in much the same way WITH ROLLUP does, but returns multiple result sets and is not treated as efficiently as ROLLUP by the query optimizer.

 

Presenting Grouped Data using DataGrid

 

The data source you obtain using the WITH ROLLUP operator already contains all the information you may need to produce an effective report. As you may have noticed, the statement adds a top row that contains the total of all the orders issued by all customers. When you use the WITH ROLLUP operator, the number and the structure of the rows generated may change significantly if you modify the order of the grouping rows. The extra row is a consequence of the particular syntax I have chosen to use. If you don't need that piece of information, just drop it off the result set prior to bind. Alternatively, you could move that row at the bottom of the data set.

 

The code shown below demonstrates how to execute the rollup statement. The year to consider is a parameter read out of textbox. The result set is temporarily stored in a DataSet object. In this sample application, I'll cache the DataSet object in a Session slot. This is not a choice that should be taken lightly in a real-world context. In general, any byte you store in Session should have a good reason to be there.

<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">
     <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="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>
private DataSet PhysicalDataRead()
{
String strCnn = "SERVER=localhost;DATABASE=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 ");
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, strConn);
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;
}

To make summary rows clearly stand out from the grid's page, you need to change the style and the layout of the rows. This can be accomplished from within the ItemCreated event handler. The idea is to detect the summary row by checking the order ID and then modify the cell layout and the style. In the result set, a summary row is characterized by an order ID of –1. The value of –1 is arbitrary and stems from the statement used.

CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID

If you don't use the GROUPING operator on the orderid column, the column value would be NULL for all summary rows.

 

How We can Modify Layout and Style?

 

A DataGrid allows you to modify both the style and layout of the constituent cells. You do this by hooking the ItemCreated event. The event fires each time the control processes a child item—header, footer, rows, and pager. The event handler receives a parameter of type DataGridItemEventArgs from which you extract the type of the item being processed. A summary row is a DataGrid row, and as such, it can be of type Item or AlternatingItem. So, when writing the ItemCreated handler, make sure you process the cell only if the item is of the right type. The following listing outlines the code you need.

public void ItemCreated(Object sender, DataGridItemEventArgs e) {
     // 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 the item being created is a DataGrid item (or an alternating item), you access the data bound to the row through the DataItem property. Depending on the type of the object the DataGrid is bound to, the DataItem property points to a different row object. You get a DataRowView object if the grid is bound to a DataView and a DataRow object if the source is expressed in terms of a DataTable object. In the sample application, I used a DataView object to populate the grid. Subsequently, the data object for a single row is a DataRowView object. Once you hold the data row object, you apply some application-specific rules to determine whether that row is or is not a summary row. In the sample application, a summary row has the MyOrderID field set to –1.

if ((int) drv["MyOrderID"] == -1) {
     // Modify style and layout here.
     // --> Set the background color to white and use bold font
     e.Item.BackColor = Color.White;
     e.Item.Font.Bold = true;
}

Actually, a DataGrid row is merely a table row. As such, it lends itself well to cell removal and other adjustments. Let's see how to render a summary row using a single cell that spans over all existing columns.

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
     // Span and right-align the cell left
     e.Item.Cells[0].ColumnSpan = 3;
     e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
}

Of the original three cells, the first two are removed and the latter—now with an index of 0—is right-aligned and spanned over the width of the outer table. If you want to display some custom text on the summary row, then be ready to face an additional problem.

 

Suppose that you want to add some text to comment on the subtotal and, at the same time, have the subtotal appear in the same column as the amount of the single order. In this case, you only need to remove one cell.

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";

The key fact to consider here is that both Item and AlternatingItem rows are bound rows. Their definitive text is set only during the OnItemDataBound event. As you may have guessed already, the OnItemDataBound event fires after that the item has been created. As a result, any text you assign to cells while processing ItemCreated has silently overwritten one event later. You hook up the OnItemDataBound event by setting the DataGrid's OnItemDataBound property.

<asp:DataGrid id="grid" runat="server" AutoGenerateColumns="false" : OnItemCreated="ItemCreated" OnItemDataBound="ItemDataBound" OnPageIndexChanged="PageIndexChanged">

The structure of the code for ItemDataBound is shown below.

public void ItemDataBound(Object sender, DataGridItemEventArgs e) {
     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";
     }
}

Here is full source listing for this application:

<%@ 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 lack;
          filter: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2,
               olor='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" ID="Form2">
               <!-- Grid and the remainder of the page -->
               <table>
                    <tr>
                         <td valign="top">
                              <asp:DataGrid id="Datagrid1" 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" revPageText="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" 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" AME="Linkbutton1" />
                              <br><br>
                              <asp:label runat="server" cssclass="StdText" id="lblMsg" />
                         </td>
                    </tr>
               </table>
               <hr>
          </form>
     </body>

</html>

Here is full source listing for this application:

<%@ Page Language="C#"Inherits="BWSLib.MyPage"src="Summary.cs"Trace="false"%>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;
}

And here is C# code-behind file:

// 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)' ND AS MyCustomerID, ");
               sb.Append(" CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS yOrderID, ");
               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();
          }
     }
}

continue article