asked on

VB.net Problem with ParamArray

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

Bob Learned

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...

ASKER CERTIFIED SOLUTION

Ark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Thanks very much for all your help

Ark

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