DataFilter for WinForms | ComponentOne
Walkthrough / Using DataFilter with FlexGrid
In This Topic
    Using DataFilter with FlexGrid
    In This Topic

    FlexGrid provides built-in filtering support with the help of column filters. However, consider a scenario where you want a filtering UI similar to the one seen in different e-commerce websites where a grid displaying the product listing shows only few attributes such as the product image, product name and product price. Although, the user is allowed to filter the product listing by many other attributes such as brand, category, price etc. It is not possible to set such kind of filters from column filters in data grid controls. Hence, this walkthrough explains how you can use the DataFilter control to filter FlexGrid content using a filtering UI similar to any online store’s website.

    The following image shows a list of cars filtered by a specific brand, category and price in FlexGrid using the DataFilter control.

    DataFilter with FlexGrid

    To create an application for filtering a car list by brand, category, and price, complete the following steps:

    Step 1: Setting up the application UI

    1. Create a new Windows Forms App.
    2. To create separate panels for placing controls, drag and drop the MS SplitContainer from the Toolbox onto your form.
    3. To add an icon or a logo representing a company or business, drag and drop the MS PictureBox control from the Toolbox onto Panel1 of the SplitContainer control. Set its Dock property to Top and add an image to the control using its Image property.
    4. To show different filtering criterias such as brand, category, etc., drag and drop the DataFilter control from the Toolbox onto Panel1 of the SplitContainer control. Set its Dock property to Bottom.
    5. To display a list of cars, drag and drop the FlexGrid control from the Toolbox onto Panel2 of the SplitContainer control. Set its Dock property to Fill.

    Back to Top

    Step 2: Creating data source for FlexGrid and DataFilter

    1. Define a method, for example CreateDataSource, to create datasource for the FlexGrid and DataFilter controls. 
      Private Sub CreateDataSource()
        Dim con As OleDbConnection = New OleDbConnection(("provider=microsoft.jet.oledb.4.0;Data Source="  _
        + (Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\ComponentOne Samples\Common\C1NWind.mdb")))
        _carsTable = New DataTable
        (New OleDbDataAdapter("Select * from Cars", con) + Fill(_carsTable))
End Sub

      private void CreateDataSource()
{
    OleDbConnection con = new OleDbConnection(
        "provider=microsoft.jet.oledb.4.0;Data Source=" +
        Environment.GetFolderPath(Environment.SpecialFolder.Personal) +
        "\\ComponentOne Samples\\Common\\C1NWind.mdb");
    _carsTable = new DataTable();
    new OleDbDataAdapter("Select * from Cars", con).Fill(_carsTable);
}

      Note that _carsTable is declared as a global variable of type DataTable.

      Note: DataBinding is also possible in the DataFilter control using the DataEngine library. Please refer the DataFilterAndDataEngine sample in \Documents\ComponentOne Samples\WinForms\v4.5.2\DataFilter\CS
    2. Invoke the method from the Form’s Load event handler to generate the data for FlexGrid and DataFilter controls. 
      Private Sub CreateDataSource()
        Dim con As OleDbConnection = New OleDbConnection(("provider=microsoft.jet.oledb.4.0;Data Source="  _
        + (Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\ComponentOne Samples\Common\C1NWind.mdb")))
        _carsTable = New DataTable
        (New OleDbDataAdapter("Select * from Cars", con) + Fill(_carsTable))
End Sub

      private void CreateDataSource()
{
    OleDbConnection con = new OleDbConnection(
        "provider=microsoft.jet.oledb.4.0;Data Source=" +
        Environment.GetFolderPath(Environment.SpecialFolder.Personal) +
        "\\ComponentOne Samples\\Common\\C1NWind.mdb");
    _carsTable = new DataTable();
    new OleDbDataAdapter("Select * from Cars", con).Fill(_carsTable);
}

    Back to Top

    Step 3: Configuring FlexGrid control

    1. To bind the FlexGrid control to a data source and set its properties, add the following code to the Form Load event handler. 
      'Configure FlexGrid
c1FlexGrid1.AutoGenerateColumns = false
c1FlexGrid1.DataSource = _carsTable
'Hide the topmost row displaying the column names
c1FlexGrid1.Rows.Fixed = 0
'Remove the columns added by by default
c1FlexGrid1.Cols.Count = 0
'Setting additional properties of the grid
c1FlexGrid1.Styles.Normal.Font = New Font(FontFamily.GenericSansSerif, 10!, FontStyle.Regular)
c1FlexGrid1.Rows.DefaultSize = 208
c1FlexGrid1.Cols.DefaultSize = 263

      //Configure FlexGrid
c1FlexGrid1.AutoGenerateColumns = false;
c1FlexGrid1.DataSource = _carsTable;

//Hide the topmost row displaying the column names
c1FlexGrid1.Rows.Fixed = 0;

//Remove the columns added by by default
c1FlexGrid1.Cols.Count = 0;

//Setting additional properties of the grid
c1FlexGrid1.Styles.Normal.Font = new Font(FontFamily.GenericSansSerif,
                                 10f, FontStyle.Regular);
c1FlexGrid1.Rows.DefaultSize = 208;
c1FlexGrid1.Cols.DefaultSize = 263;

      Note that in order to filter the data-aware control, FlexGrid, using the DataFilter control, both the controls must share a common data source.

    2. To display the car image and information in a cell, add unbound columns to the FlexGrid control, for example, we have added three unbound columns to the FlexGrid control and subscribed the GetUnboundValue event to populate these columns with data. 
      'Add unbound columns to the FlexGrid 
c1FlexGrid1.Cols.Add(_colCount)
'Retrieve data for the unbound cells
c1FlexGrid1.GetUnboundValue = (c1FlexGrid1.GetUnboundValue + _flex_GetUnboundValue)

      //Add unbound columns to the FlexGrid 
c1FlexGrid1.Cols.Add(_colCount);

//Retrieve data for the unbound cells
c1FlexGrid1.GetUnboundValue += _flex_GetUnboundValue;

      Note that _colCount is defined as a global variable with value equal to 3.

    3. To populate the unbound columns with car data along with the images, add the following code to the GetUnboundValue event handler. 
      Private Sub _flex_GetUnboundValue(ByVal sender As Object, ByVal e As UnboundValueEventArgs)
'Fetch the cell style to get the UserData property value which returns
'the car Id.
        Dim cs As CellStyle = c1FlexGrid1.Styles(("CellStyle"  _
        + (e.Row + ("," + e.Col))))
'Display car image and information in the cell depending on car Id
'fetched above
        If ((Not (cs) Is Nothing)  _
        AndAlso (Not (cs.UserData) Is Nothing)) Then
        Dim carId As Integer = (Convert.ToInt32(cs.UserData) - 1)
        c1FlexGrid1.SetCellImage(e.Row, e.Col, GetImagesFromByteArray(CType(_carsTable.Rows(carId)("Picture"),Byte())))
        e.Value = ("Model: "  _
        + (_carsTable.Rows(carId)("Model") + (""& vbLf&"Brand:"  _
        + (_carsTable.Rows(carId)("Brand") + (""& vbLf&"Price:" + _carsTable.Rows(carId)("Price"))))))
End If
End Sub

      private void _flex_GetUnboundValue(object sender, UnboundValueEventArgs e)
{
    //Fetch the cell style to get the UserData property value which returns
    //the car Id.
    CellStyle cs = c1FlexGrid1.Styles["CellStyle" + e.Row + "," + e.Col];

    //Display car image and information in the cell depending on car Id
    //fetched above
    if (cs != null && cs.UserData != null)
    {
        int carId = Convert.ToInt32(cs.UserData) - 1;
        c1FlexGrid1.SetCellImage(e.Row, e.Col, 
            GetImagesFromByteArray((Byte[])_carsTable.Rows[carId]["Picture"]));
        e.Value = "Model: " + _carsTable.Rows[carId]["Model"] +
            "\nBrand:" + _carsTable.Rows[carId]["Brand"] + "\nPrice:" +
            _carsTable.Rows[carId]["Price"];
    }
}

      Note that the GetUnboundValue event handler uses the ‘GetImagesFromByteArray’ method to retrieve the car images stored as OLE objects in the Access database.

    4. Define the GetImagesFromByteArray’ method using the following code. 
      Private Function GetImagesFromByteArray(ByVal picData() As Byte) As Image
'Generate car image using the image data saved as byte array
        If (picData Is Nothing) Then
                Return Nothing
        End If
        
        Dim bmData As Integer = 78
'TODO: Warning!!!, inline IF is not supported ?
        ((picData(0) = 21)  _
        AndAlso (picData(1) = 28))
        0
        Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream(picData, bmData, (picData.Length - bmData))
        Return Image.FromStream(ms)
End Function

      private Image GetImagesFromByteArray(byte[] picData)
{
    //Generate car image using the image data saved as byte array
    if (picData == null)
    {
        return null;
    }
   
    int bmData = (picData[0] == 0x15 && picData[1] == 0x1c) ? 78 : 0;                   
    System.IO.MemoryStream ms = new System.IO.MemoryStream(picData, 
                                bmData, picData.Length - bmData);                               
    return Image.FromStream(ms);
}

    Back to Top

    Step 4: Configuring DataFilter control

    1. To automatically generate the filters, set the AutoGenerateFilters property of DataFilter to true in the Form Load event handler. 
      'Automatically generates the filters
c1DataFilter1.AutoGenerateFilters = true

      //Automatically generates the filters
c1DataFilter1.AutoGenerateFilters = true;

    2. To define the filtering criterias such as the minimum /maximum values for the RangeFilter and the checklist items for the CheckListFilter, subscribe to the FilterAutoGenerating event in the Form_Load event. 
      'Modify the automatically generated filters
c1DataFilter1.FilterAutoGenerating = (c1DataFilter1.FilterAutoGenerating + C1DataFilter1_FilterAutoGenerating)

      //Modify the automatically generated filters
c1DataFilter1.FilterAutoGenerating += C1DataFilter1_FilterAutoGenerating;

    3. Add the following code to the FilterAutoGenerating event handler to customize the automatically generated filters. 
      Private Sub C1DataFilter1_FilterAutoGenerating(ByVal sender As Object, ByVal e As C1.DataFilter.FilterAutoGeneratingEventArgs)
  'Modify the automatically generated filters
        Select Case e.[Property].Name
        'Set the checklist items for Category filter
        Case "Category"
            Dim categoryFilter = CType(e.Filter, C1.Win.DataFilter.ChecklistFilter)
            categoryFilter.ItemsSource = _carsTable
            categoryFilter.ValueMemberPath = "Category"
            categoryFilter.SelectAll()
        'Set the checklist items for Brand filter
        Case "Brand"
            Dim brandFilter = CType(e.Filter, C1.Win.DataFilter.ChecklistFilter)
            brandFilter.ItemsSource = _carsTable
            brandFilter.ValueMemberPath = "Brand"
            brandFilter.SelectAll()
        'Set the minimum/maximum value for the Liter filter
        Case "Liter"
            Dim literFilter = CType(e.Filter, C1.Win.DataFilter.RangeFilter)
            literFilter.Maximum = _carsTable.AsEnumerable().Max(Function(x) x.Field(Of Double)("Liter"))
            literFilter.Minimum = _carsTable.AsEnumerable().Min(Function(x) x.Field(Of Double)("Liter"))
            literFilter.Increment = 0.01
            literFilter.Digits = 3
        'Set the minimum/maximum value for the Price filter
        Case "Price"
            Dim priceFilter = CType(e.Filter, C1.Win.DataFilter.RangeFilter)
            priceFilter.Maximum = _carsTable.AsEnumerable().Max(Function(x) x.Field(Of Double)("Price"))
            priceFilter.Minimum = _carsTable.AsEnumerable().Min(Function(x) x.Field(Of Double)("Price"))
            priceFilter.Increment = 1000
            priceFilter.Digits = 0
        Case Else
            e.Cancel = True
    End Select
End Sub

      private void C1DataFilter1_FilterAutoGenerating(object sender,
    C1.DataFilter.FilterAutoGeneratingEventArgs e)
{
    //Modify the automatically generated filters
    switch (e.Property.Name)
    {
        //Set the checklist items for Category filter
        case "Category":
            var categoryFilter = (C1.Win.DataFilter.ChecklistFilter)e.Filter;
            categoryFilter.ItemsSource = _carsTable;
            categoryFilter.ValueMemberPath = "Category";
            categoryFilter.SelectAll();
            break;
        //Set the checklist items for Brand filter
        case "Brand":
            var brandFilter = (C1.Win.DataFilter.ChecklistFilter)e.Filter;
            brandFilter.ItemsSource = _carsTable;
            brandFilter.ValueMemberPath = "Brand";
            brandFilter.SelectAll();
            break;
        //Set the minimum/maximum value for the Liter filter
        case "Liter":
            var literFilter = (C1.Win.DataFilter.RangeFilter)e.Filter;
            literFilter.Maximum = 
                _carsTable.AsEnumerable().Max(x => x.Field<double>("Liter"));
            literFilter.Minimum = 
                _carsTable.AsEnumerable().Min(x => x.Field<double>("Liter"));
            literFilter.Increment = 0.01;
            literFilter.Digits = 3;
            break;
        //Set the minimum/maximum value for the Price filter
        case "Price":
            var priceFilter = (C1.Win.DataFilter.RangeFilter)e.Filter;
            priceFilter.Maximum = 
                _carsTable.AsEnumerable().Max(x => x.Field<double>("Price"));
            priceFilter.Minimum = 
                _carsTable.AsEnumerable().Min(x => x.Field<double>("Price"));
            priceFilter.Increment = 1000;
            priceFilter.Digits = 0;
            break;
        default:
            e.Cancel = true;
            break;
    }
}

    4. To handle the FilterChanged event and reset the FlexGrid to display the car information appropriately, subscribe to the FilterChanged event.
      Note that on applying the filter to the FlexGrid, the grid is rendered with the default settings. So, the FilterChanged event needs to be handled. 
      'This event is fired when the filter changes hence it is used retrieve the
'relevant information regarding the filtered rows
c1DataFilter1.FilterChanged += C1DataFilter1_FilterChanged

      //This event is fired when the filter changes hence it is used retrieve the
//relevant information regarding the filtered rows
c1DataFilter1.FilterChanged += C1DataFilter1_FilterChanged;

    5. Add the following code to the FilterChanged event handler to reset FlexGrid to display the car information. 
      Private Sub C1DataFilter1_FilterChanged(ByVal sender As Object, ByVal e As EventArgs)
        'Reconfigure FlexGrid
        c1FlexGrid1.Styles.Clear
        c1FlexGrid1.Row = -1
        ' Clear default selection from FlexGrid
        Dim col As Integer = 0
        Do While (col < _colCount)
            c1FlexGrid1.Cols(col).ImageAlign = ImageAlignEnum.CenterTop
            c1FlexGrid1.Cols(col).TextAlign = TextAlignEnum.CenterBottom
            col = (col + 1)
        Loop
        
        'Get the list of filtered rows            
        Dim filteredCars As List(Of DataRowView) = c1DataFilter1.View.Cast(Of DataRowView).ToList
        'Save the car Id using the UserData property of CellStyle
        Dim row As Integer = 0
        Do While (row < filteredCars.Count)
            Dim cs As CellStyle = c1FlexGrid1.Styles.Add(("CellStyle"  _
                            + ((row / _colCount) + (","  _
                            + (row Mod _colCount)))))
            cs.UserData = filteredCars(row).Row("ID")
            row = (row + 1)
        Loop
        'Since the data from the filtered rows will be rendered in
        'three columns, so hide the extra rows in FlexGrid
        Dim rowCount As Integer = CType(Math.Ceiling((filteredCars.Count / CType(_colCount,Double))),Integer)
        Dim i As Integer = rowCount
        Do While (i < c1FlexGrid1.Rows.Count)
            c1FlexGrid1.Rows(i).Visible = false
            i = (i + 1)
        Loop
End Sub

      private void C1DataFilter1_FilterChanged(object sender, EventArgs e)
{
    //Reconfigure FlexGrid
    c1FlexGrid1.Styles.Clear();
    c1FlexGrid1.Row = -1; // Clear default selection from FlexGrid
    for (int col = 0; col < _colCount; col++)
    {
        c1FlexGrid1.Cols[col].ImageAlign = ImageAlignEnum.CenterTop;
        c1FlexGrid1.Cols[col].TextAlign = TextAlignEnum.CenterBottom;
    }

    //Get the list of filtered rows            
    List<DataRowView> filteredCars = c1DataFilter1.View.Cast<DataRowView>().ToList();

    //Save the car Id using the UserData property of CellStyle
    for (int row = 0; row < filteredCars.Count; row++)
    {
        CellStyle cs = c1FlexGrid1.Styles.Add("CellStyle" +
                       row / _colCount + "," + row % _colCount);
        cs.UserData = filteredCars[row].Row["ID"];
    }

    //Since the data from the filtered rows will be rendered in
    //three columns, so hide the extra rows in FlexGrid
    int rowCount = (int)Math.Ceiling(filteredCars.Count / (double)_colCount);
    for (int i = rowCount; i < c1FlexGrid1.Rows.Count; i++)
        c1FlexGrid1.Rows[i].Visible = false;
}

    6. In the Form_Load event, bind the DataFilter control to the same datasource which was assigned to the FlexGrid control. 
      c1DataFilter1.DataSource = _carsTable

      c1DataFilter1.DataSource = _carsTable;

    7. Run the application and observe how the cars data with image appear in the FlexGrid control while all the car attribute filters are added to the DataFilter control.
      Now, you can change the filter values in the DataFilter UI and see how the FlexGrid renders the filtered data.

    Back to Top

    Note: The above mentioned sample codes are only supported in WinForms .NET Framework.