Solved

VB.net Problem with ParamArray

Posted on 2014-01-29
4
842 Views
Last Modified: 2014-01-30
Hi

The class further down below the line of +'s contains different functions with the same name "PivotData". I am trying to gather information into variabes so that I can call the function using  
Me.DataGridView1.DataSource = pvt.PivotData(RowFields, DataField, oAggregate, ColumnFields)
but I get errors with Paramater Arrays where more than one item is included
How do I declare each variable so that the code handles any one of the following scenarios
in the following lines?

           'Me.DataGridView1.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, "Year") 'Works
            'Me.DataGridView1.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Min, "Year") 'Works
            'Me.DataGridView1.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, "Company", "Year") 'Works
 
            ''Me.DataGridView1.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Average, "Company", "Year")

            'Me.DataGridView1.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, "Company", "Department", "Year") 'Works
            'Me.DataGridView1.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Sum, True, "Company", "Year") 'Works
            ''Example of Pivot on Both the Axés.//
            ' Me.DataGridView1.DataSource = pvt.PivotData("CTC", AggregateFunction.Max, New String() {"Designation", "Year"}, New String() {"Company", "Department"})


'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Data

Public Enum AggregateFunction
    Count = 1
    Sum = 2
    First = 3
    Last = 4
    Average = 5
    Max = 6
    Min = 7
    Exists = 8
End Enum

Public Class Pivot

    Private _SourceTable As New DataTable()
    Private _Source As IEnumerable(Of DataRow) = New List(Of DataRow)()

    Public Sub New(SourceTable As DataTable)
        _SourceTable = SourceTable
        _Source = SourceTable.Rows.Cast(Of DataRow)()
    End Sub

    ''' <summary>
    ''' Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
    ''' </summary>
    ''' <param name="rowField">The column name of the Source Table which you want to spread into rows</param>
    ''' <param name="dataField">The column name of the Source Table which you want to spread into Data Part</param>
    ''' <param name="aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
    ''' <param name="columnFields">The List of column names which you want to spread as columns</param>
    ''' <returns>A DataTable containing the Pivoted Data</returns>
    Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, ParamArray columnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim rowList As List(Of String) = _Source.[Select](Function(x) x(rowField).ToString()).Distinct().ToList()
        ' Gets the list of columns .(dot) separated.
        Dim colList = _Source.[Select](Function(x) (columnFields.[Select](Function(n) x(n)).Aggregate(Function(a, b) a & Separator & b.ToString())).ToString()).Distinct().OrderBy(Function(m) m)

        dt.Columns.Add(rowField)
        For Each colName As Object In colList
            dt.Columns.Add(colName)
        Next
        ' Cretes the result columns.//
        For Each rowName As String In rowList
            Dim row As DataRow = dt.NewRow()
            row(rowField) = rowName
            For Each colName As String In colList
                Dim strFilter As String = (Convert.ToString(rowField & Convert.ToString(" = '")) & rowName) + "'"
                Dim strColValues As String() = colName.Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To columnFields.Length - 1
                    strFilter += " and " + columnFields(i) + " = '" + strColValues(i) + "'"
                Next
                row(colName) = GetData(strFilter, dataField, aggregate)
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, showSubTotal As Boolean, ParamArray columnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim rowList As List(Of String) = _Source.[Select](Function(x) x(rowField).ToString()).Distinct().ToList()
        ' Gets the list of columns .(dot) separated.
        Dim colList As List(Of String) = _Source.[Select](Function(x) columnFields.Aggregate(Function(a, b) (x(a).ToString() & Separator) + x(b).ToString())).Distinct().OrderBy(Function(m) m).ToList()

        If showSubTotal AndAlso columnFields.Length > 1 Then
            Dim totalField As String = String.Empty
            For i As Integer = 0 To columnFields.Length - 2
                totalField += Convert.ToString(columnFields(i) + "(Total)") & Separator
            Next
            Dim totalList As List(Of String) = _Source.[Select](Function(x) totalField & x(columnFields.Last()).ToString()).Distinct().OrderBy(Function(m) m).ToList()
            colList.InsertRange(0, totalList)
        End If

        dt.Columns.Add(rowField)
        colList.ForEach(Function(x) dt.Columns.Add(x))

        For Each rowName As String In rowList
            Dim row As DataRow = dt.NewRow()
            row(rowField) = rowName
            For Each colName As String In colList
                Dim filter As String = (Convert.ToString(rowField & Convert.ToString(" = '")) & rowName) + "'"
                Dim colValues As String() = colName.Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To columnFields.Length - 1
                    If Not colValues(i).Contains("(Total)") Then
                        filter += " and " + columnFields(i) + " = '" + colValues(i) + "'"
                    End If
                Next
                row(colName) = GetData(filter, dataField, If(colName.Contains("(Total)"), AggregateFunction.Sum, aggregate))
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    Public Function PivotData(DataField As String, Aggregate As AggregateFunction, RowFields As String(), ColumnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim RowList = _SourceTable.DefaultView.ToTable(True, RowFields).AsEnumerable().ToList()
        For index As Integer = RowFields.Count() - 1 To 0 Step -1
            RowList = RowList.OrderBy(Function(x) x.Field(Of Object)(RowFields(index))).ToList()
        Next
        ' Gets the list of columns .(dot) separated.
        Dim ColList = (From x In _SourceTable.AsEnumerable() _
                             Select New With {.Name = ColumnFields.[Select](Function(n) x.Field(Of Object)(n)).Aggregate(Function(a, b) a & Separator & b.ToString()) _
        }).Distinct().OrderBy(Function(m) m.Name)

        'dt.Columns.Add(RowFields)
        For Each s As String In RowFields
            dt.Columns.Add(s)
        Next

        'For Each col As Object In ColList
        'dt.Columns.Add(col.Name.ToString())
        'Next

        'CODE CORRECTION
        On Error Resume Next

        For Each col In ColList
            dt.Columns.Add(col.Name.ToString())
        Next

        ' Creates the result columns.//
        For Each RowName As Object In RowList
            Dim row As DataRow = dt.NewRow()
            Dim strFilter As String = String.Empty

            For Each Field As String In RowFields
                row(Field) = RowName(Field)
                strFilter += (Convert.ToString(" and ") & Field) + " = '" + RowName(Field).ToString() + "'"
            Next
            strFilter = strFilter.Substring(5)

            For Each col As Object In ColList
                Dim filter As String = strFilter
                Dim strColValues As String() = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To ColumnFields.Length - 1
                    filter += " and " + ColumnFields(i) + " = '" + strColValues(i) + "'"
                Next
                row(col.Name.ToString()) = GetData(filter, DataField, Aggregate)
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    ''' <summary>
    ''' Retrives the data for matching RowField value and ColumnFields values with Aggregate function applied on them.
    ''' </summary>
    ''' <param name="Filter">DataTable Filter condition as a string</param>
    ''' <param name="DataField">The column name which needs to spread out in Data Part of the Pivoted table</param>
    ''' <param name="Aggregate">Enumeration to determine which function to apply to aggregate the data</param>
    ''' <returns></returns>
    Private Function GetData(Filter As String, DataField As String, Aggregate As AggregateFunction) As Object
        Try
            Dim FilteredRows As DataRow() = _SourceTable.[Select](Filter)
            Dim objList As Object() = FilteredRows.[Select](Function(x) x.Field(Of Object)(DataField)).ToArray()

            Select Case Aggregate
                Case AggregateFunction.Average
                    Return GetAverage(objList)
                Case AggregateFunction.Count
                    Return objList.Count()
                Case AggregateFunction.Exists
                    Return If((objList.Count() = 0), "False", "True")
                Case AggregateFunction.First
                    Return GetFirst(objList)
                Case AggregateFunction.Last
                    Return GetLast(objList)
                Case AggregateFunction.Max
                    Return GetMax(objList)
                Case AggregateFunction.Min
                    Return GetMin(objList)
                Case AggregateFunction.Sum
                    Return GetSum(objList)
                Case Else
                    Return Nothing
            End Select
        Catch ex As Exception
            Return "#Error"
        End Try
    End Function

    Private Function GetAverage(objList As Object()) As Object
        Return If(objList.Count() = 0, Nothing, DirectCast(Convert.ToDecimal(GetSum(objList)) / objList.Count(), Object))
    End Function
    Private Function GetSum(objList As Object()) As Object
        Return If(objList.Count() = 0, Nothing, DirectCast(objList.Aggregate(New Decimal(), Function(x, y) x & Convert.ToDecimal(y)), Object))
    End Function
    Private Function GetFirst(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.First())
    End Function
    Private Function GetLast(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Last())
    End Function
    Private Function GetMax(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Max())
    End Function
    Private Function GetMin(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Min())
    End Function

End Class
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39819816
In all that code, there is probably something to highlight it, but maybe I am too tired to look for it.  It might be easier if you showed just the method signatures...
0
 
LVL 27

Accepted Solution

by:
Ark earned 500 total points
ID: 39819957
Since you'r using paramarray of array you need following declaration
Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, ParamArray columnFields As String()()) As DataTable (see additional bold brackets for ParamArray declaration)
Note that currently u'r using an array of string (columnFields As String()) in declaration while pass an array of array of strings. To retrieve data form an array of array:
        For i = 0 To columnFields.Length - 1
            Dim cf = columnFields(i) ' an array of 2 string you passed
'{"Designation", "Year"} for i=0 and {"Company", "Department"} for i=1
            For j = 0 To cf.Length - 1
                Debug.Print(cf(j))
            Next
        Next

Open in new window

BTW, you can use a paramarray of strings like you have now, but in this case pass csv strings, for example and split inside function:
 ' Me.DataGridView1.DataSource = pvt.PivotData("CTC", AggregateFunction.Max, _
    "Designation, Year", "Company, Department")
 Public Function PivotData(rowField As String, dataField As String, _
    aggregate As AggregateFunction, _
    ParamArray columnFields As String()) As DataTable
    
    For Each strField In columnFields
         dim cf=strField.Split(","c)
         If cf.Length=2 Then'contains comma separated pair
             'Buid filter here
         End if
    Next
End Function

Open in new window

Or you can use ParamArray of structures/classes:
Public Class ColumnFileldData
    Public Shared Property fieldName1 As String
    Public Shared Property fieldName2 As String
    Public Shared Property fieldName3 As String
End Class
'Function
Public Function PivotData(rowField As String, dataField As String, _
    aggregate As AggregateFunction, _
    ParamArray columnFields As ColumnFileldData()) As DataTable
End Function
'Call
Me.DataGridView1.DataSource = pvt.PivotData("CTC", AggregateFunction.Max, _
    New ColumnFileldData With { .fieldName1 = "Designation",  .fieldName2 = "Year"}, _
    New ColumnFileldData With  {.fieldName1 = "Company", .fieldName2 = "Department"})

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 39820931
Thanks very much for all your help
0
 
LVL 27

Expert Comment

by:Ark
ID: 39822365
Glad I could help. If you choose an array of classes, please remove Shared keyword from properties - it's my mistake. Should be
Public Class ColumnFileldData
    Public Property fieldName1 As String
    Public Property fieldName2 As String
    Public Property fieldName3 As String
End Class

Open in new window

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Retain selection in datagridview 2 23
Vb.Net Menu 1 22
Hide Tab Page 3 19
Recommendation vb6 to vb.net or others 14 41
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now