Datatable Grouping in .Net 2.0

This functionality is missing in the GridView control.

There are various ways to go about it. I thought I will do it without Ajax or other overheads.

The following code takes in a Datatable as input and returns the grouped Datatable as output.

Make the output datatable the source of any gridview!

The parameters allow multi column grouping and sorting

becomes

-----------------------------------------------------------------------

 

Public Shared Function GroupDataTable(ByVal T1 As DataTable, ByVal GroupByColumns As String(), _
    Optional ByVal SortOrder As Boolean() = Nothing, _
    Optional ByVal SumColumns As String() = Nothing, _
    Optional ByVal HeaderCell As Integer = 0 _
    ) As DataTable

        Dim T2 As New DataTable
        Dim sortStr As String = ""
        Dim i, j, N, T, m As Integer
        Dim dR1 As DataRow
        Dim drSum As DataRow
        Dim xS As Double

        Try
            Dim dView As DataView = T1.DefaultView
            For i = 0 To GroupByColumns.Length - 1
                If String.IsNullOrEmpty(sortStr) Then
                    sortStr = GroupByColumns(i)
                Else
                    sortStr = sortStr & ", " & GroupByColumns(i)
                End If

                If Not IsNothing(SortOrder) Then
                    If SortOrder.Length > i Then
                        If SortOrder(i) = False Then
                            sortStr = sortStr & " " & "DESC"
                        End If
                    End If
                End If
            Next
            dView.Sort = sortStr
            T2 = dView.ToTable
            ' Sorting Done

            Dim Gr1 As String = ""
            Dim Gr2 As String
            T = T2.Rows.Count

            drSum = T2.NewRow

            For j = 0 To T - 1
                m = m + 1
                Gr2 = T2.Rows(j + N)(GroupByColumns(0))
                For i = 1 To GroupByColumns.Length - 1
                    Gr2 = Gr2 & ">" & T2.Rows(j + N)(GroupByColumns(i))
                Next
                If Gr2 <> Gr1 Then   'Group changes
                    'Header row
                    Gr1 = Gr2
                    dR1 = T2.NewRow
                    dR1(HeaderCell) = Gr1
                    T2.Rows.InsertAt(dR1, j + N)
                    N = N + 1

                    'Summary row
                    If Not IsNothing(SumColumns) And j > 0 Then
                        drSum(HeaderCell) = "Rows: " & (m - 1)
                        T2.Rows.InsertAt(drSum, j + N - 1)
                        N = N + 1
                        drSum = T2.NewRow
                        xS = 0
                        m = 1
                    End If
                End If

                'create summary rows
                If Not IsNothing(SumColumns) Then
                    For i = 1 To SumColumns.Length
                        If Not IsDBNull(drSum(SumColumns(i - 1))) Then xS = drSum(SumColumns(i - 1))
                        If Not IsDBNull(T2.Rows(j + N)(SumColumns(i - 1))) Then xS = xS + T2.Rows(j + N)(SumColumns(i - 1))
                        drSum(SumColumns(i - 1)) = xS
                    Next
                End If
                '---------
            Next
            'Last summary row
            If Not IsNothing(SumColumns) Then
                drSum(HeaderCell) = "Rows: " & m
                T2.Rows.Add(drSum)
            End If

            'Row increment done

            T2.AcceptChanges()
            T2 = T2.Copy

        Catch ex As Exception
            Throw
        End Try

        Return T2

    End Function