Last time I described how LINQ can be used to custom filter ADO.NET DataTable. This time I will demonstrate how this technique can replace built-in server-side filtering in Infragistics UltraWebGrid.

By default if server-side filtering is enabled in UltraWebGrid controls, it displays a small “funnel” icon in the column header, if you click this icon – a list of unique values from this column is displayed and selecting a value filters the grid by displaying only rows that have this value.

This default behavior works, sort of – it has many issues. If your grid has paging enabled – only values from the current page will be displayed. If your columns or values has characters that grid doesn’t like (commas, square brackets etc.) an exception will be thrown (this happens because under the hood grid converts your filters into a string that is passed to DataTable’s RowFilter), there’re other issues as well.

Why leave anything to chance when you can provide filtering yourself?

First, using method described here we can collect filter values for columns across all the pages. This is the only part of the grid we will be using for filtering – just to hold filter values, but not to perform actual filtering – this we will do ourselves.

Second, we need to collect selected filter values (you can have more than 1 filter applied, since every column can be filtered by). The place to do it is grid’s RowFilterApplying event handler and we can store values in a Generic Dictionary (using filter’s Column Index as key and filter’s value as, well, value). This is how it looks in code:

Protected Sub xmyGrid_RowFilterApplying(sender As Object, e As UltraWebGrid.FilterEventArgs) Handles xmyGrid.RowFilterApplying
   
   'canceling event since we will be doing filtering ourselves
   e.Cancel = True

   'aLinqFilter Dictionary contains custom filters for grid's underlying table
   Dim aLinqFilter As Generic.Dictionary(Of Integer, String) = Session("LinqFilters")
   'if no filters exist - create new holder
   If aLinqFilter Is Nothing Then aLinqFilter = New Generic.Dictionary(Of Integer, String)

   'if custom filter collection already contains currently filtered column - remove old filter
   If aLinqFilter.ContainsKey(e.ActiveColumnFilter.Column.Index) Then aLinqFilter.Remove(e.ActiveColumnFilter.Column.Index)

   'depending on selected filter condition - create custom filter. Format {Column Index, Filter Value }
   Select Case e.AppliedFilterCondition.ComparisonOperator
      Case FilterComparisionOperator.Empty
         'Storing filter Value as "{isnull}" for "Empty" condition
         aLinqFilter.Add(e.ActiveColumnFilter.Column.Index, "{isnull}")
      Case FilterComparisionOperator.NotEmpty
         'Storing filter Value = "{isnotnull}" for "NotEmpty" condition
         aLinqFilter.Add(e.ActiveColumnFilter.Column.Index, "{isnotnull}") 
      Case 3 'Equal
         'otherwise storing actual filter value
         aLinqFilter.Add(e.ActiveColumnFilter.Column.Index, e.AppliedFilterCondition.CompareValue.ToString)
   End Select

    'storing resulting filter in session variabe - it will be used on later onevery grid datbind
   Session("LinqFilters") = aLinqFilter

End Sub

When user selects a value in filter drop-down this event handler is called. We’re canceling it immediatly so not to allow grid perform it’s build-in filtering and at this time simple collect filter value and index of the column it belongs to.

Next we need to finish filtering in RowFilterApplied applied event by binding grid to the DataTable:

Protected Sub xmygrid_RowFilterApplied(sender As Object, e As UltraWebGrid.FilterEventArgs) Handles xmygrid.RowFilterApplied

   xmyGrid.Clear() '-- removing old data from grid so new filter can be applied correclty
   BindDataTableToGrid() ' -- calling method to bind data to the grid

End Sub

And now the most interesting part. Using method, described in previous post we will filter our DataTable during databinding in BindDataTableToGrid method:

 Sub BindDataTableToGrid()

   'reading table with data from Session
   Dim dtTable As DataTable = Session("MyData")

   'aLinqFilter holds custom filters for the grid, it gets created/updated in RowFilterPopulating event
   Dim aLinqFilter As Generic.Dictionary(Of Integer, String) = Session("LinqFilters")

   'IF aLinqFilter (custom filters) is populated - applying filters to DataTable via LINQ WHERE clause
   If aLinqFilter IsNot Nothing AndAlso aLinqFilter.Count > 0 Then
      xmyGrid.DataSource = dtTable.AsEnumerable.Where(Function(dr As DataRow) FilterDataRows(dr, aLinqFilter)).AsDataView
   Else 'if there're no filters - binding grid to original DataTable
      xmyGrid.DataSource = dtTable
   End If

   xmyGrid.DataBind()

End Sub

Here first we getting reference to original unfiltered DataTable (in this case from Session variable, but it can come from other sources), then we reading Dictionary with filters created in RowFilterPopulatingEvent. If filters exist – we use them in DataTable’s WHERE extension method using our own custom filtering function FilterDataRows. If, on the other hand there’re no filters – we binding UltraWebGrid to original unfiltered DataTable.

And the final touch – custom filtering function FilterDataRows:

Function FilterDataRows(i_oRow As DataRow, i_aLinqFilter As Generic.Dictionary(Of Integer, String)) As Boolean
   Dim bTest As Boolean
   Dim oColValue As Object

   'Looping thru custom filters
   For Each oFilterPair In i_aLinqFilter

      'reading current row's cell value that corresponds filter columns
      oColValue = i_oRow(oFilterPair.Key)

      Select Case oFilterPair.Value
         Case "{isnull}" 'if filter is checking for "Empty" value
            bTest = (oColValue Is DBNull.Value) 'comparing value to NULL
         Case "{isnotnull}" 'if filter is checking for "NotEmpty" value
            bTest = (oColValue IsNot DBNull.Value) 'comparing value to NOT NULL
         Case Else 'otherwise comparing cell value to actual filter value
            bTest = (oColValue IsNot DBNull.Value AndAlso oColValue = oFilterPair.Value)
      End Select

      'if at least one filter condition failed - return False and current row will be hidden
      If bTest = False Then Return False 

   Next

   Return True 'if all filter condition pass - return True and current row will be visible

End Function

This function is called for every row in the DataTable, it compares row values to filter values in our filter Dictionary and if row passes all conditions, TRUE is returned and row remains visible, otherwise FALSE is returned and row doesn’t make the cut.

So, let’s review the flow:

1. User clicks “funnel” icon in the column header and selects value to filter by from the dropdown
2. RowFilterPopulating event handler is called and filter value is collected in Dictionary
3. RowFilterPopulated event handler is called which calls method to bind the grid to data
4. This method applies Dictonary with filters collected in RowFilterPopulating to DataTable
5. During filtering process a custom filtering function is called to determine every row eligibility

Therefore if you use this approach – every step of the filtering process is in your hands. For example if you need to include some additional logic into FilterDataRows comparasing function – you’re free to do so. Also, BindDataTableToGrid method can be called from other grid events (paging, sorting etc.) and current filter will always be applied automatically.

The only drawback of this method – filter icons in column headers do not automatically change image to indicate that “filter was applied”, but this can be corrected by method similar to one described hereX9YY7N3UYA8A