How to implement an incremental search using a grid column (UltraWinGrid)

• Download demo - 84.5 Kb
• Download source code - 46.9 Kb

Introduction

This code offers you a solution for this question: How to implement an incremental search doing double click in a WinGrid column? Perhaps to code it has some deficiencies, but I believe that it will give you an idea of how to make this process.

Notes:

This new version includes a button for clearing the Filter/Incremental Search textbox. The previous version, Clear/Remove this element when the cursor mouse leave the column. The search has been improved, redundant code has been removed and now the incremental search box only is added when the cursor mouse is over a column header. The method ElementWithMouseCapture is used for code optimization.

When the textbox for incremental search or filter data was placed on a column and you made a scroll, this textbox was placed over the cells. This issue was fixed in this version.

Panorama(Background)

Using the Infragistics.Win.UIElement, object you can verify the type of object of the Interface of user, on whom is the cursor. This object also has a GetContext method, that returns the main UI of the object. If you write the code, you will realize that this object is TextUIElement. Also you will see that the objects ColumnHeader and SortIndicatorUIElement are present in an object column. I detect if the object on which it is the cursor is a column and I add via code a UltraTextEditor object to him. This object has a button to filter (the filter only is performed when the user clicks on this button), a button for remove the filter and code in the TextChanged event for the incremental search. Because grid works different ways when the characteristic is used to group by columns, it has also been considered. If you check the code you will realize that it verifies if the row of grid pretends to a group (using UltraGridGroupByRow and ChildBands). The code that I made works in two ways: The user has the possibility of making a filtration or of making the incremental search (implicit).

This article shows how to display tooltips on the column and how to bind data to textboxes for navigation across the records too.

Code Overview

The following namespaces are required for a correct performance.

Imports System.IO
Imports System.String
Imports System.Data
Imports System.Data.SqlClient
Imports Infragistics.Win.UltraWinGrid
Imports Infragistics.Win
Imports Infragistics.Shared

The following variables are used and must be declared:

#Region " V A R I A B L E S "
    Dim bindman As BindingManagerBase 'For bind the text boxes to the row's grid.

    'A local data set. You need modify the connection string

    Dim localdataset As New System.Data.DataSet 
    Dim _activeColumn As String 
    Dim ui As Infragistics.Win.UIElement 
    Dim aColumnHeader As Infragistics.Win.UltraWinGrid.ColumnHeader
    Dim nRowByGroupOriginal As Long
#End Region

In the Load event, the connection to the SQL database is performed. You will need modify the string connection to a valid database and table for the correct execution. The grid have an OutLook style view. The last code row from this event, shows how to add an image to a cell in the grid, in this case, to the first cell.

Private Sub Itemsforupdate_Load(ByVal sender As System.Object, _ 
         ByVal e As System.EventArgs) Handles MyBase.Load
    Me.grdItems.DisplayLayout.ViewStyleBand = _ 
            Infragistics.Win.UltraWinGrid.ViewStyleBand.OutlookGroupBy

    Dim sqlAdapter As New SqlDataAdapter( _
       "SELECT * FROM customers", _
       New SqlConnection("Initial Catalog=northwind;Data Source= _
                                  (local);Integrated Security=SSPI;"))
    sqlAdapter.Fill(localdataset, "customers")
    Me.UltraTextEditor1.DataBindings.Add("text", _
                         localdataset.Tables("customers"), "customerid")
    Me.UltraTextEditor2.DataBindings.Add("text", _
                         localdataset.Tables("customers"), "contactname")
    bindman = Me.BindingContext(localdataset.Tables("customers"))
    Me.grdItems.DataSource = localdataset.Tables("customers")
    Me.grdItems.Rows(0).Cells(0).Appearance.Image = _ 
          System.Drawing.Image.FromFile(Application.StartupPath & "\clock.png")
End Sub

The next code in the Paint event, set a cool fill to the form. Nice!

Private Sub Itemsforupdate_Paint(ByVal sender As Object,  _
          ByVal e As System.Windows.Forms.PaintEventArgs) _
          Handles MyBase.Paint
    Dim FillBrush As New Drawing2D.LinearGradientBrush(ClientRectangle, _
    Color.Khaki, Color.WhiteSmoke, _
    Drawing2D.LinearGradientMode.ForwardDiagonal)
    e.Graphics.FillRectangle(FillBrush, ClientRectangle)
    FillBrush.Dispose()
End Sub

In the MouseEnterElement event, I check the control that have the cursor over it. This is necessary because the incremental textbox is added on a header or column. The first code line from this event, do a cast for the UI element, and converts it to an ColumnHeader element (control). The commented code allows to add the textbox for the incremental search when the mouse enter over the column header. This event shows how to display a tooltip over each column. This feature is useful when you have many columns and this have a small size.

Below Code revised and updated!

Private Sub grdItems_MouseEnterElement(ByVal sender As Object, _
          ByVal e As Infragistics.Win.UIElementEventArgs) _
          Handles grdItems.MouseEnterElement
     aColumnHeader = _
         CType(e.Element.GetContext( _
         GetType(Infragistics.Win.UltraWinGrid.ColumnHeader)), _
         Infragistics.Win.UltraWinGrid.ColumnHeader)
     If Not aColumnHeader Is Nothing And TypeOf e.Element Is HeaderUIElement Then
         _activeColumn = aColumnHeader.Column.Key
         MyToolTip.Active = True
         MyToolTip.SetToolTip(CType(sender, System.Windows.Forms.Control), _
              aColumnHeader.Column.Key)
         MyTextSearchBox.Size = _
             New Size(aColumnHeader.SizeResolved.Width, _
             aColumnHeader.SizeResolved.Height)
         ui = e.Element
         aColumnHeader.Band.ColHeaderLines = 1
         Exit Sub
     End If
 End Sub

The code in the MouseLeaveElement event, remove the tooltip's text.

Below Code revised and updated!

Private Sub grdItems_MouseLeaveElement(ByVal sender As Object, _
         ByVal e As Infragistics.Win.UIElementEventArgs) _
         Handles grdItems.MouseLeaveElement
     Dim ui As Infragistics.Win.UIElement
     ui = e.Element
     If TypeOf e.Element Is HeaderUIElement Then
          MyToolTip.SetToolTip(sender, String.Empty)
     End If
 End Sub

The code to filter the grid's rows is placed in the EditorButtonClick event of the Textbox control.

Below Code revised and updated!

Private Sub MyTextSearchBox_EditorButtonClick(ByVal sender As Object, _ 
            ByVal e As Infragistics.Win.UltraWinEditors.EditorButtonEventArgs) _
            Handles MyTextSearchBox.EditorButtonClick
  Dim band As UltraGridBand = Me.grdItems.DisplayLayout.Bands(0)
  Select Case e.Button.Key.ToLower
    Case "clearfilter"
      RemoveFilter()
    Case Else
      band.Override.AllowRowFiltering = DefaultableBoolean.True
      band.Override.RowFilterMode = RowFilterMode.AllRowsInBand
      band.ColumnFilters(_activeColumn).FilterConditions.Add( _
                                        FilterComparisionOperator.Like, _ 
                                        "*" & MyTextSearchBox.Text.Trim & "*")
   End Select
End Sub

The code for incremental search is placed in the TextChanged event of the Textbox control. The code is different when the grid have rows grouped by column or have childbands (like outlook style).

Below Code revised and changed!

Private Sub MyTextSearchBox_TextChanged(ByVal sender As Object, _
                 ByVal e As System.EventArgs) Handles MyTextSearchBox.TextChanged
  If MyTextSearchBox.Text <> "" Then
  Dim myCellValue As String
  Static gRow As UltraGridRow
  Dim nRow As Long
  Me.grdItems.SuspendLayout()
  grdItems.BeginUpdate()
  Me.grdItems.DisplayLayout.Override.MaxSelectedCells = 1
  Me.grdItems.DisplayLayout.Override.SelectTypeCell = SelectType.Single
  If (Not TypeOf grdItems.ActiveRow Is _
       Infragistics.Win.UltraWinGrid.UltraGridGroupByRow _ 
       And IsNothing(grdItems.Rows(nRowByGroupOriginal).ChildBands)) Then
  For Each gRow In Me.grdItems.Rows
    grdItems.ActiveRow = gRow
    myCellValue = grdItems.ActiveRow.Cells(_activeColumn).Text
    If [String].CompareOrdinal(myCellValue.ToLower, _
                LCase(Me.MyTextSearchBox.Text.Trim)) > 0 Then
      gRow.Cells(_activeColumn).Activate()
      gRow.Cells(_activeColumn).Selected = True
      Exit For
    End If
  Next
  Else
    For Each gRow In grdItems.Rows(nRowByGroupOriginal).ChildBands(0).Rows
    myCellValue = _
       grdItems.Rows(nRowByGroupOriginal).ChildBands(0).Rows( _
                                    gRow.Index).Cells(_activeColumn).Text
    If [String].CompareOrdinal(myCellValue.ToLower, LCase( _
                                    Me.MyTextSearchBox.Text.Trim)) > 0 Then
      grdItems.Rows(nRowByGroupOriginal).ChildBands(0).Rows( _
                                    gRow.Index).Cells(_activeColumn).Activate()
      grdItems.Rows(nRowByGroupOriginal).ChildBands(0).Rows( _
                                    gRow.Index).Cells(_activeColumn).Selected =_
                                    True
      Exit For
    End If
    Next
  End If
  Me.grdItems.ResumeLayout()
  grdItems.EndUpdate()
  End If
End Sub

The next code is used for forcing a scroll to the first row in the grid (when the filter or incremental search is executed).

Private Sub grdItems_BeforeRowActivate(ByVal sender As Object, _
          ByVal e As Infragistics.Win.UltraWinGrid.RowEventArgs) _
          Handles grdItems.BeforeRowActivate
    ' determine which row should be the top row

    Dim rowTop As UltraGridRow
    If e.Row.Band.Index = 0 Then
        rowTop = e.Row
    Else
        rowTop = e.Row.ParentRow
    End If
    With grdItems.DisplayLayout
       ' scroll the row into the RowScrollRegion

       .RowScrollRegions(0).ScrollRowIntoView(rowTop)
       ' turn off display refresh during update

       grdItems.BeginUpdate()
       ' scroll the top row to the top

       Do Until .RowScrollRegions(0).VisibleRows(0).Row Is rowTop
          .RowScrollRegions(0).Scroll(RowScrollAction.LineDown)
       Loop
       ' turn display refresh back on

       grdItems.EndUpdate()
    End With
End Sub

In the DoubleClick event adds the control for the incremental search. The textbox is added when the user does a double click over a columnheader element (border). The main context element is TextUIElement, but the grid have for default sort the column for column header when the user does a click. For this reason, I don't added the textbox when the user clicks over the TextUIElement.

Below code revised and updated!

Private Sub grdItems_DoubleClick(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles grdItems.DoubleClick
  If Not IsNothing(ui) Then
    If _activeColumn <> "" And _
       TypeOf ui.ControlElement.ElementWithMouseCapture Is HeaderUIElement Then
      grdItems.DisplayLayout.Bands(0).Columns(_activeColumn).SortIndicator _
                                     = SortIndicator.None
      grdItems.PerformAction(UltraGridAction.FirstRowInBand)
      ' If  Then

      MyTextSearchBox.Location = ui.Rect.Location
      ui.Control.Controls.Add(MyTextSearchBox)
      MyTextSearchBox.Visible = True
      MyTextSearchBox.Focus()
      'End If

    End If
  End If
End Sub

The next code is very crucial when the grid have groups (like outlook style). Get the current group expanded.

Private Sub grdItems_AfterRowExpanded(ByVal sender As Object, _
           ByVal e As Infragistics.Win.UltraWinGrid.RowEventArgs) _
           Handles grdItems.AfterRowExpanded
    nRowByGroupOriginal = e.Row.Index
End Sub

For removing the filter or textbox for incremental search, I use the Sub RemoveFilter. This Sub is invoked when the user do a click over the button ClearFilter (the second button).

New code added:

Private Sub RemoveFilter()
        Dim band As UltraGridBand = Me.grdItems.DisplayLayout.Bands(0)
        band.Override.AllowRowFiltering = DefaultableBoolean.False
        band.ColumnFilters(_activeColumn).FilterConditions.Clear()
        MyToolTip.SetToolTip(ui.Control, String.Empty)
        MyTextSearchBox.Visible = False
        ui.Control.Controls.Clear()
        _activeColumn = ""
End Sub

To avoid the textbox for incremental search become placed in anywhere over the grid, I wrote this new events:

Private Sub grdItems_BeforeRowRegionScroll(ByVal sender As Object, _
        ByVal e As Infragistics.Win.UltraWinGrid.BeforeRowRegionScrollEventArgs) _
        Handles grdItems.BeforeRowRegionScroll
 grdItems.SuspendLayout()
 If Not IsNothing(ui) Then
     If TypeOf ui.ControlElement.LastElementEntered Is _
         Infragistics.Win.UltraWinScrollBar.ScrollArrowUIElement Or _
         TypeOf ui.ControlElement.ElementWithMouseCapture Is _
         Infragistics.Win.UltraWinScrollBar.ScrollBarUIElement Then
         ui.Control.Controls.Clear()
     End If
 End If
End Sub

Private Sub grdItems_BeforeColRegionScroll(ByVal sender As Object, _
     ByVal e As Infragistics.Win.UltraWinGrid.BeforeColRegionScrollEventArgs) _
     Handles grdItems.BeforeColRegionScroll
 grdItems.SuspendLayout()
 If Not IsNothing(ui) Then
     If TypeOf ui.ControlElement.LastElementEntered Is _
         Infragistics.Win.UltraWinScrollBar.ScrollArrowUIElement Or _
         TypeOf ui.ControlElement.ElementWithMouseCapture Is _
         Infragistics.Win.UltraWinScrollBar.ScrollBarUIElement Then
         ui.Control.Controls.Clear()
     End If
 End If
End Sub

Private Sub grdItems_AfterColRegionScroll(ByVal sender As Object, _
          ByVal e As Infragistics.Win.UltraWinGrid.ColScrollRegionEventArgs) _
          Handles grdItems.AfterColRegionScroll
        grdItems.ResumeLayout()
End Sub

Private Sub grdItems_AfterRowRegionScroll(ByVal sender As Object, _
          ByVal e As Infragistics.Win.UltraWinGrid.RowScrollRegionEventArgs) _
          Handles grdItems.AfterRowRegionScroll
        grdItems.ResumeLayout()
End Sub

Resumen

In this code, you see how (Version 2.0, 02/10/2005, 1652):

• to add a control at run-time over the object that have the focus (or the mouse is over).
• to add a tooltip over each column.
• to filter the rows for a column specific.
• to incremental search over data in a column.
• to use the CType(), GetContext() functions.
• to connect an a database using SQL server 2000 : sqlConnection, sqlDataAdapter, DataSets.
• to add an image to a specific cell.
• to verify if the grid have row groups (like outlook style)
• to scroll the grid for set visible the element indicated in the filter condition or criteria text for incremental search.
• to filter data using filterconditions.
• to use the function CompareTo for comparing two strings.
• to use the Infragistics.Win.UIElement to detect a control or element specific: HeaderUIElement, SortIndicatorUIElement, TextUIElement, ElementWithMouseCapture etc.
• to apply a LinearGradient fill to the form, for a cool effect.

Version 2.0

• Adds a button to remove the textbox for filtering or incremental search.
• Implements the events before/after col/row ScrollRegion to remove the textbox when the user do a scroll. This avoids that the control become 'placed' (only visual) over the cells.
• Improved the code in the DoubleClick event. Using ElementWithMouseCapture, the double click event only is performed when the ui.ControlElement become for type HeaderUIElement.
• The text is searching using CompareOrdinal method instead of CompareTo method.
• Fixed an unhandled exception error; when the user do a scroll immediately that the form starts, without moving the cursor mouse over a column (the element ui was not created).

Notice

This code assumes that you are using Infragistics UltraWinGrid and that you are a licensed user for this great components suite. You can download a trial version for NetAdvantage 2004 vol 3 from here. This code is only for demonstrative purposes. You need to connect to SQL server 2000 NorthWind database too.

I hope that this code will become useful for you. Download an Trial version for NetAdvantage 2004 Vol 3 from here.

Best regards,

CFQ�eb