Skip to main content

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

image

becomes

image

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

 

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

Comments

Popular posts from this blog

Siemens Washing Machine : Kill the Buzzer

The washing machine we have has the habit of sending out an irritating beep after it finishes the wash cycle. Ok enough; but these intermittent beeps go on and on and on till it is sure everyone came back home after your attending your funeral. One awful engineer who programmed the chip. Anyway, been looking around the net if there is a work around. Found this written by some Russian. I didn’t have patience to correct the grammar entirely. If the below does  not work, there is always the option of an Axe and ‘hey Siemens,..Heeeere is Johnyyy!) -- text You can change the volume of the buzzer according to your requirement. The operation procedure: 1. Switch on the machine,turn the program selector to Off . 2. Turn the program selector to cold Easy-care , press the additional function button Intensive stains and dont let go. You can hear the volume of the buzzer from minimum to maximum to off cycled (I didnt hear this!). If you decide the volume that you wa

HTML Sidebar menu without jQuery

Most examples I found on the net for a responsive sidebar had a reference to jQuery. For two lines of code, I find it an unnecessary overhead. So this is a basic page with no dependencies that has a toggle-able sidebar as well as a media query javascript that hides the sidebar below a breakpoint screen width. Modify as necessary. Maybe tricks could be added such as the one to change the hamburger to X when the sidebar is visible. https://jsbin.com/romiqov/2/edit?html,output <!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8">     <title>SideBar test</title>     <style>         * {             margin: 0;             padding: 0;             font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;         }         #sidebar {             position: fixed;             width: 200px;             height: 100%;             top: 50px;             left: 0px;             transition

Sudoku Puzzle Generator

Sudoku puzzles can be generated by a switching the rows and columns of a valid puzzle. The switch has to be done between the 123, 456 and 789 rows/columns. ie; 1 cannot be switched with 7 for example. A random pair can be generated and if a loop is run say 50 times, we get a new puzzle. Then the cells can be hidden again randomly. The following code generates the puzzle from a base character string which is converted to a 81 length Char array. The output is the solution as well as the puzzle with blank (or 0s)               Dim stdArray As String = "317849265245736891869512473456398712732164958981257634174925386693481527528673149"         Dim charArray() As Char = stdArray.ToCharArray         Dim rng As New Random         Dim m As Integer         Dim row1 As Integer         Dim row2 As Integer         Dim col1 As Integer         Dim col2 As Integer         Dim i As Integer         Dim cell1 As Integer         Dim cell2 As Integer         Dim tempC A