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 = "server=localhost;database=northwind;UID=sa"

    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)

    Dim curCat As String  ‘指示當(dāng)前記錄中產(chǎn)品所屬的類別
    Dim prevCat As String  ‘指示上一條記錄中產(chǎn)品所屬的類別
    Dim i As Integer = 0   ‘要插入分類標(biāo)題行的位置，用I表示
'遍歷結(jié)果集，找出要插入分類標(biāo)題的行
    Do While i <= ds.Tables(0).Rows.Count - 1
        curCat = ds.Tables(0).Rows(i).Item(0)
        If curCat <> prevCat Then  ‘如果發(fā)現(xiàn)前后兩記錄的所屬類別不一樣
            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  ‘‘設(shè)置一個臨時的標(biāo)記
                ds.Tables(0).Rows.InsertAt(shRow, i)
                      i += 1
        End If
             i += 1
     Loop
    ‘將最后一行的標(biāo)題改為total
    ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1).Item(1) = "Total"
    DataGrid1.DataSource = ds
    DataGrid1.DataBind()
End Sub

　Private Sub DataGrid1_ItemDataBound(sender As Object,  e As DataGridItemEventArgs)
Select Case e.Item.ItemType
        Case ListItemType.AlternatingItem, ListItemType.Item
  ’如果發(fā)現(xiàn)是分類標(biāo)題行的話，則對其進(jìn)行格式化
            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
     ‘合拼為一個新的分類標(biāo)題行，移除其中的單元格
                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

 '如果是每個分類的小計的話，則設(shè)置相應(yīng)的顯示格式
            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