Code Corner

If you're binding an ADO.NET DataTable to Infragistics UltraWebGrid and then programmaticaly sort the grid (e.g. add a column to a band's SortedColumns collection) you may get an error:

Cannot find column My Column Name.

with stack trace starting from grid databinding and finishing in datatable's sorting:

at System.Data.DataTable.ParseSortString(String sortString)
at System.Data.DataView.CheckSort(String sort)
at System.Data.DataView.set_Sort(String value)
at Infragistics.WebUI.UltraWebGrid.DBBinding.ProcessDataViewForFillRows(DataView dataView, RowsCollection rows)
at Infragistics.WebUI.UltraWebGrid.DBBinding.FillRows(UltraWebGrid grid, RowsCollection rows, IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.BindList(IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.DataBind(Object dataSource, String dataMember)
at Infragistics.WebUI.UltraWebGrid.UltraWebGrid.DataBind()

If the grid binds OK without sorting and grouping, but fails with either - most likely the culprit is one of the columns in data table. Read more...

Often you have to operate with flattened data that in reality contains multiple levels of hierarchy. For example it can come as a result of several SQL JOIN statement and look like this:

In this example data consist of static root column, region, site, type and state. And the data has clearly defined hierarchy (e.g. Region "India" has site "Bangalore", site "Bangalore" has types "Application" and "Area", type "Application" has states "N/A" and "Testing").

To load this data into Infragistics UltraWebTree I put together a small procedure: Read more...

LINQ is truly integrated into VB.NET. This allows not only to use LINQ-specific query language in a standard VB.NET code, but use VB.NET code in a LINQ query. Consider function from the previous post. To make it universal we can pass one more parameter
"Aggregate Type" and based on that parameter return Min, Max, Avg, Sum or Count

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable, i_iAggregateType as Integer) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Choose(i_iAggregateType, Group.Min(Function(row) row(i_sAggregateColumn)), Group.Max(Function(row) row(i_sAggregateColumn)), Group.Sum(Function(row) row(i_sAggregateColumn)), Group.Avg(Function(row) row(i_sAggregateColumn)), Group.Count(Function(row) row(i_sAggregateColumn)))

    return aQuery.toDataTable

End Function

In this example VB.NET function Choose is used inside of a LINQ query's Select Statement. If i_iAggregateType parameter is equal 1 - Minimum value, will be calculated, 2 - Maximum etc.

I've described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above - that entire block of code can be replaced with a single LINQ query:

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Group.Count(Function(row) row(i_sAggregateColumn))

    return aQuery.toDataTable

End Function

The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.

There is one caveat though. Read more...

Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:

Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

        'getting distinct values for group column
        Dim dv As New DataView(i_dSourceTable)

        'adding column for the row count
        Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn}) 

        dtGroup.Columns.Add("Count", GetType(Integer)) 

        'looping thru distinct values for the group, counting
        For Each dr As DataRow In dtGroup.Rows
            dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
        Next        

        'returning grouped/counted result
        Return dtGroup
End Function

The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s "ToTable" method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s "Compute" method - Count in this case, but it can easily be replaced with other aggregates.