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  ‘指示当前记录中产品所属的类别
    Dim prevCat As String  ‘指示上一条记录中产品所属的类别
    Dim i As Integer = 0   ‘要插入分类标题行的位置，用I表示
'遍历结果集，找出要插入分类标题的行
    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
    ‘将最后一行的标题改为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
  ’如果发现是分类标题行的话，则对其进行格式化
            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