RadGrid for ASP.NET AJAX

Contents
RadControls Send comments on this topic.
Google-like filtering
Controls > RadGrid > Filtering > Google-like filtering

Glossary Item Box

This example shows how to implement 'Google-like style' filtering in RadGrid.

      

It allows displaying the available options while the user types in a RadComboBox in RadGrid filtering item. Filtering is confirmed when item is selected or Enter is pressed.

 

Here is the list of actions needed to achieve this:

  • Extend the default GridBoundColumn to remove the default textbox and embed RadComboBox inside the filtering cell.
  • Configure the options for RadComboBox control as per your requirements (the essential parts are to set ShowToggleImage = false, ID for the combobox, EnableLoadOnDemand=true, MarkFirstMatch = true and attach the ItemsRequested/SelectedIndexChanged events). These operation should take place in the overriden SetupFilterControls(TableCell cell) method.
  • Override the SetCurrentFilterValueToControl(TableCell cell) / GetCurrentFilterValueFromControl(TableCell cell) methods to set/get the user input.
  • Filter the combobox items in the ItemsRequested handler depending on the UniqueName of the currently filtered column.
  • Raise command event for the GridFilteringItem calling its FireCommandEvent(commandName, new Pair(filterFunctionName, columnUniqueName)) method.

This example is expanded by enabling the AJAXmechanism of the grid along with its built-in paging and sorting features. The filtering action will be triggered when you choose item from a look-up textbox or type inside filtering input and press Enter from the keyboard.

In addition, there is Clear filter button on the page which restores the initial grid content.

Note: In this case search is performed for matches in the default grid source on each filter command. That is why each column's CurrentFilterFunction is set to GridKnownFunction.NoFilter and CurrentFilterValue to String.Empty on filter operation.


Google-like filtering

 

ASPX/ASCX Copy Code
<asp:ScriptManager ID="ScriptManager1" runat="server" />
       <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
           <AjaxSettings>
               <telerik:AjaxSetting AjaxControlID="RadGrid1">
                   <UpdatedControls>
                       <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                   </UpdatedControls>
               </telerik:AjaxSetting>
               <telerik:AjaxSetting AjaxControlID="clrFilters">
                   <UpdatedControls>
                       <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                       <telerik:AjaxUpdatedControl ControlID="clrFilters" />
                   </UpdatedControls>
               </telerik:AjaxSetting>
           </AjaxSettings>
       </telerik:RadAjaxManager>
       <telerik:RadGrid ID="RadGrid1" AutoGenerateColumns="false" AllowPaging="true" AllowSorting="true"
           AllowFilteringByColumn="true" Width="560px" Skin="Office2007" runat="server"
           OnColumnCreating="RadGrid1_ColumnCreating" OnItemCommand="RadGrid1_ItemCommand"
           OnNeedDataSource="RadGrid1_NeedDataSource">
           <PagerStyle Mode="NumericPages" />
       </telerik:RadGrid>
       <br />
<asp:Button ID="clrFilters" runat="server" Text="Clear filters" CssClass="button"
OnClick="clrFilters_Click"></asp:Button>

 

C# Copy Code
DataTable dt;
       private void Page_Load(object sender, System.EventArgs e)
       {
           if (!IsPostBack)
           {
               dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers");
               this.RadGrid1.MasterTableView.Columns.Clear();
               foreach (DataColumn dataColumn in dt.Columns)
               {
                   MyCustomFilteringColumnCS gridColumn = new MyCustomFilteringColumnCS();
                   this.RadGrid1.MasterTableView.Columns.Add(gridColumn);
                   gridColumn.DataField = dataColumn.ColumnName;
                   gridColumn.HeaderText = dataColumn.ColumnName;
               }
           }
       }
       protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
       {
           dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers");
           this.RadGrid1.DataSource = dt;
       }

       protected void RadGrid1_ColumnCreating(object sender, GridColumnCreatingEventArgs e)
       {
           if ((e.ColumnType == typeof(MyCustomFilteringColumnCS).Name))
           {
               e.Column = new MyCustomFilteringColumnCS();
           }
       }
       protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
       {
           if ((e.CommandName == "Filter"))
           {
               foreach (GridColumn column in e.Item.OwnerTableView.Columns)
               {
                   column.CurrentFilterValue = string.Empty;
                   column.CurrentFilterFunction = GridKnownFunction.NoFilter;
               }
           }
       }
       protected void clrFilters_Click(object sender, EventArgs e)
       {
           foreach (GridColumn column in RadGrid1.MasterTableView.Columns)
           {
               column.CurrentFilterFunction = GridKnownFunction.NoFilter;
               column.CurrentFilterValue = string.Empty;
           }
           RadGrid1.MasterTableView.FilterExpression = string.Empty;
           RadGrid1.MasterTableView.Rebind();
       }
       public static DataTable GetDataTable(string query)
       {
           string ConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
           SqlConnection conn = new SqlConnection(ConnString);
           SqlDataAdapter adapter = new SqlDataAdapter();
           adapter.SelectCommand = new SqlCommand(query, conn);
           DataTable myDataTable = new DataTable();
           conn.Open();
           try
           {
               adapter.Fill(myDataTable);
           }
           finally
           {
               conn.Close();
           }
           return myDataTable;
       }


 

VB.NET Copy Code
Private dt As DataTable
        Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
            If Not IsPostBack Then
                dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers")
                Me.RadGrid1.MasterTableView.Columns.Clear()
                For Each dataColumn As DataColumn In dt.Columns
                    Dim gridColumn As New MyCustomFilteringColumnCS()
                    Me.RadGrid1.MasterTableView.Columns.Add(gridColumn)
                    gridColumn.DataField = dataColumn.ColumnName
                    gridColumn.HeaderText = dataColumn.ColumnName
                Next
            End If
        End Sub
        Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As GridNeedDataSourceEventArgs)
            dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers")
            Me.RadGrid1.DataSource = dt
        End Sub
        Protected Sub RadGrid1_ColumnCreating(ByVal sender As Object, ByVal e As GridColumnCreatingEventArgs)
            If (e.ColumnType = GetType(MyCustomFilteringColumnCS).Name) Then
                e.Column = New MyCustomFilteringColumnCS()
            End If
        End Sub
        Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs)
            If (e.CommandName = "Filter") Then
                For Each column As GridColumn In e.Item.OwnerTableView.Columns
                    column.CurrentFilterValue = String.Empty
                    column.CurrentFilterFunction = GridKnownFunction.NoFilter
                Next
            End If
        End Sub
        Protected Sub clrFilters_Click(ByVal sender As Object, ByVal e As EventArgs)
            For Each column As GridColumn In RadGrid1.MasterTableView.Columns
                column.CurrentFilterFunction = GridKnownFunction.NoFilter
                column.CurrentFilterValue = String.Empty
            Next
            RadGrid1.MasterTableView.FilterExpression = String.Empty
            RadGrid1.MasterTableView.Rebind()
        End Sub
        Public Shared Function GetDataTable(ByVal query As String) As DataTable
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
            Dim conn As New SqlConnection(ConnString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(query, conn)
            Dim myDataTable As New DataTable()
            conn.Open()
            Try
                adapter.Fill(myDataTable)
            Finally
                conn.Close()
            End Try
            Return myDataTable
        End Function


 

MyCustomFilteringColumn.cs Copy Code
public class MyCustomFilteringColumnCS : GridBoundColumn
   {
       public static string ConnectionString
       {
           get { return ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; }
       }
       //RadGrid will call this method when it initializes the controls inside the filtering item cells
       protected override void SetupFilterControls(TableCell cell)
       {
           base.SetupFilterControls(cell);
           cell.Controls.RemoveAt(0);
           RadComboBox combo = new RadComboBox();
           combo.ID = ("RadComboBox1" + this.UniqueName);
           combo.ShowToggleImage = false;
           combo.Skin = "Office2007";
           combo.EnableLoadOnDemand = true;
           combo.AutoPostBack = true;
           combo.MarkFirstMatch = true;
           combo.Height = Unit.Pixel(100);
  combo.ItemsRequested += this.list_ItemsRequested;
           combo.SelectedIndexChanged += this.list_SelectedIndexChanged;
           cell.Controls.AddAt(0, combo);
           cell.Controls.RemoveAt(1);
       }
       //RadGrid will cal this method when the value should be set to the filtering input control(s)
       protected override void SetCurrentFilterValueToControl(TableCell cell)
       {
           base.SetCurrentFilterValueToControl(cell);
           RadComboBox combo = (RadComboBox)cell.Controls[0];
           if ((this.CurrentFilterValue != string.Empty))
           {
               combo.Text = this.CurrentFilterValue;
           }
       }
       //RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
       protected override string GetCurrentFilterValueFromControl(TableCell cell)
       {
           RadComboBox combo = (RadComboBox)cell.Controls[0];
           return combo.Text;
       }
       private void list_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
       {
           ((RadComboBox)o).DataTextField = this.DataField;
           ((RadComboBox)o).DataValueField = this.DataField;
           ((RadComboBox)o).DataSource = GetDataTable("SELECT DISTINCT " + this.UniqueName + " FROM Customers WHERE " + this.UniqueName + " LIKE '" + e.Text + "%'");
           ((RadComboBox)o).DataBind();
       }
       private void list_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
       {
           GridFilteringItem filterItem = (GridFilteringItem)((RadComboBox)o).NamingContainer;
           if ((this.UniqueName == "Index"))
           {
               //this is filtering for integer column type
               filterItem.FireCommandEvent("Filter", new Pair("EqualTo", this.UniqueName));
           }
           //filtering for string column type
           filterItem.FireCommandEvent("Filter", new Pair("Contains", this.UniqueName));
       }
       public static DataTable GetDataTable(string query)
       {
           SqlConnection conn = new SqlConnection(ConnectionString);
           SqlDataAdapter adapter = new SqlDataAdapter();
           adapter.SelectCommand = new SqlCommand(query, conn);
           DataTable myDataTable = new DataTable();
           conn.Open();
           try
           {
               adapter.Fill(myDataTable);
           }
           finally
           {
               conn.Close();
           }
           return myDataTable;
       }
   }


 

MyCustomFilteringColumn.vb Copy Code
Public Class MyCustomFilteringColumnCS
 Inherits GridBoundColumn
 Public Shared ReadOnly Property ConnectionString() As String
  Get
   Return ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
  End Get
 End Property
 'RadGrid will call this method when it initializes the controls inside the filtering item cells
 Protected Overloads Overrides Sub SetupFilterControls(ByVal cell As TableCell)
  MyBase.SetupFilterControls(cell)
  cell.Controls.RemoveAt(0)
  Dim combo As New RadComboBox()
  combo.ID = ("RadComboBox1" + Me.UniqueName)
  combo.ShowToggleImage = False
  combo.Skin = "Office2007"
  combo.EnableLoadOnDemand = True
  combo.AutoPostBack = True
  combo.MarkFirstMatch = True
  combo.Height = Unit.Pixel(100)
  AddHandler combo.ItemsRequested, AddressOf Me.list_ItemsRequested
  AddHandler combo.SelectedIndexChanged, AddressOf Me.list_SelectedIndexChanged
  cell.Controls.AddAt(0, combo)
  cell.Controls.RemoveAt(1)
 End Sub
 'RadGrid will cal this method when the value should be set to the filtering input control(s)
 Protected Overloads Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)
  MyBase.SetCurrentFilterValueToControl(cell)
  Dim combo As RadComboBox = DirectCast(cell.Controls(0), RadComboBox)
  If (Me.CurrentFilterValue <> String.Empty) Then
   combo.Text = Me.CurrentFilterValue
  End If
 End Sub
 'RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
 Protected Overloads Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String
  Dim combo As RadComboBox = DirectCast(cell.Controls(0), RadComboBox)
  Return combo.Text
 End Function
   Private Sub list_ItemsRequested(ByVal o As Object, ByVal e As RadComboBoxItemsRequestedEventArgs)
    CType(o, RadComboBox).DataTextField = Me.DataField
    CType(o, RadComboBox).DataValueField = Me.DataField
    CType(o, RadComboBox).DataSource = GetDataTable("SELECT DISTINCT " + Me.UniqueName + " FROM Customers WHERE " + Me.UniqueName + " LIKE '" + e.Text + "%'")
    CType(o, RadComboBox).DataBind()
  End Sub


 Private Sub list_SelectedIndexChanged(ByVal o As Object, ByVal e As RadComboBoxSelectedIndexChangedEventArgs)
  Dim filterItem As GridFilteringItem = DirectCast((DirectCast(o, RadComboBox)).NamingContainer, GridFilteringItem)
  If (Me.UniqueName = "Index") Then
   'this is filtering for integer column type
   filterItem.FireCommandEvent("Filter", New Pair("EqualTo", Me.UniqueName))
  End If
  'filtering for string column type
  filterItem.FireCommandEvent("Filter", New Pair("Contains", Me.UniqueName))
 End Sub
 Public Shared Function GetDataTable(ByVal query As String) As DataTable
  Dim conn As New SqlConnection(ConnectionString)
  Dim adapter As New SqlDataAdapter()
  adapter.SelectCommand = New SqlCommand(query, conn)
  Dim myDataTable As New DataTable()
  conn.Open()
  Try
   adapter.Fill(myDataTable)
  Finally
   conn.Close()
  End Try
  Return myDataTable
 End Function
End Class