In Part 1 we examined how to return summary information
from a SQL query using the ROLLUP syntax. In this final part, we'll look at how to display the
results of a SQL ROLLUP query in a nicely formatted ASP.NET DataGrid.
Displaying the Results of a ROLLUP Query in an ASP.NET DataGrid
In an earlier article of mine, Including Subheadings in a DataGrid,
I showed how to use the Northwinds database to display the list of products with subheadings for each
unique category. In this article we will modify some of the code from the previous article used to
create the subheadings, and then add some new code in order to display the summary information.
The below screenshot shows what our final ASP.NET DataGrid will look like:
In Including Subheadings in a DataGrid we discussed using the
Page_Load event and the DataGrid's ItemDataBound to create the subheadings.
For creating a DataGrid for the ROLLUP query results, we will again be concentrating on
these two events, adding some slight modifications. Specifically, we'll start with these two events'
handlers from the Including Subheadings in a DataGrid article, and modify them as needed.
First, the Page_Load event handler needs its SQL query modified to incorporate the
ROLLUP syntax. We'll use precisely the SQL query we examined in
Part 1 of this article.
Our complete Page_Load event handler appears as follows:
Sub Page_Load(Sender As Object, E As EventArgs) Handles MyBase.Load
' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "connection string"
Dim CommandText As String = "Select CASE WHEN (Grouping(CategoryName)=1) THEN " & _
"'MainTotal' ELSE categoryname END AS CategoryName, "
CommandText &= " CASE WHEN (Grouping(ProductName)=1) THEN 'SubTotal' ELSE " & _
"Productname END AS ProductName,"
CommandText &= " Sum(UnitPrice) as unitprice, "
CommandText &= " Sum(UnitsinStock) as UnitsinStock "
CommandText &= " from Products INNER JOIN Categories On Products.categoryID = " & _
" Categories.CategoryID"
CommandText &= " Group By Categoryname, ProductName WITh ROLLUP "
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet
myCommand.Fill(ds)
'Before DataBind - Add New Rows Here for Each Sub Heading
Dim curCat As String
Dim prevCat As String
Dim i As Integer = 0
Do While i <= ds.Tables(0).Rows.Count - 1
curCat = ds.Tables(0).Rows(i).Item(0)
If curCat <> prevCat Then
prevCat = curCat
Dim shRow As DataRow = ds.Tables(0).NewRow
shRow(1) = ds.Tables(0).Rows(i).Item(0)
'Change ItemDataBound marker to Negative Number
shRow(2) = -1
ds.Tables(0).Rows.InsertAt(shRow, i)
i += 1
End If
i += 1
Loop
'End Insert SubHeadings
'Change the Text in the Last Column so it can be formated in ItemDataBound
ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1).Item(1) = "Total"
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
The Page_Load event handler starts by filling a DataSet (ds) with the
results from the ROLLUP query. Next, it uses the same technique discussed in
Including Subheadings in a DataGrid to insert a new row into the DataSet to mark where category
boundaries exist.
While the Page_Load event handler marks what rows are summary rows, it is the responsibility
of the DataGrid's ItemDataBound event handler to format these rows so that they stand
out from the other rows.
The first modification to the ItemDataBound event handler from the previous article
is related to the second change in the Page_Load event handler, where we set the new subheading
column's UnitPrice value to a negative number. This is done so we can identify where to
display the subheading.
Since we now have SubTotal and MainTotal columns in our DataGrid, we need to ensure these columns
stand out from the non-total columns. We accomplish this by setting the text to bold and italic for
the subtotals, and bold for the main total, as well as changing the background color for the main
total column. These aesthetic changes are handled in the ItemDataBound event handler,
which is shown below:
Private Sub DataGrid1_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.AlternatingItem, ListItemType.Item
If e.Item.Cells(1).Text.Equals("-1") Then
'Format the SubHeading Columns
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).ColumnSpan = 3
e.Item.Cells(0).Font.Bold = True
e.Item.Cells.RemoveAt(2)
e.Item.Cells.RemoveAt(1)
e.Item.BackColor = Color.FromArgb(204,204,255)
End If
If e.Item.Cells(0).Text.Equals("Total") Then
'Format the Main total column
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).Font.Bold = True
e.Item.Cells(1).Font.Bold = True
e.Item.Cells(2).Font.Bold = True
e.Item.BackColor = Color.FromArgb(204,153,255)
End If
If e.Item.Cells(0).Text.Equals("SubTotal") Then
'Format the subtotal columns.
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).Text = "Sub Totals"
e.Item.Cells(0).Font.Bold = True
e.Item.Cells(1).Font.Bold = True
e.Item.Cells(2).Font.Bold = True
e.Item.Cells(0).Font.Italic = True
e.Item.Cells(1).Font.Italic = True
e.Item.Cells(2).Font.Italic = True
End If
End Select
End Sub
And that’s all there is to it. Be sure to check out the live demo
to see a working example and the complete source code.
Conclusion
In this article we examined the ROLLUP syntax in SQL. ROLLUP can be used to
nicely summarize aggregate query results. If you are interested in learning more about ROLLUP
be sure to check out some of these articles:
