"Google suggest" style filtering in RadGrid

 

Article information
Article relates to	RadGrid, RadComboBox RadGrid AJAX & RadComboBox AJAX (Prometheus) Telerik.Web.UI 2007.3.1425
Created by	Stephen, Telerik
Last modified by	Veskoni, Telerik

HOW-TO 
Perform "Google suggest" style filtering in RadGrid

DESCRIPTION
In some cases you may whish to display the available options when the user types in filter textbox of grid column and filter the record on item selection/Enter key press. 
 




SOLUTION
This behavior is easily attainable by replacing the default textbox input with RadComboBox instance in "look-up textbox" mode (setting the combobox's ShowToggleImage property to false). Here are the steps you need to undertake in order to configure the grid as mentioned above:

• extend the default GridBoundColumn to remove the default textbox and embed RadComboBox inside the filtering cell
• configure the options for the combobox 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 

The example below is expanded by enabling the AJAX mechanism 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 our case we search for matches in the default grid source on each filter command. That is why we set each column's CurrentFilterFunction to GridKnownFunction.NoFilter and CurrentFilterValue to String.Empty on filter operation.

You can find a demo project in the Article files section for further reference.
  
Default.aspx

<radG:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="False" AllowPaging="True"

AllowSorting="True" AllowFilteringByColumn="True" Width="500px" EnableAJAX="True"

EnableAJAXLoadingTemplate="True" Skin="Windows">

<MasterTableView AllowFilteringByColumn="True" />

<PagerStyle Mode="NumericPages" />

</radG:RadGrid>

<br>

<asp:Button ID="clrFilters" runat="server" Text="Clear filters"></asp:Button>

Default.aspx.vb

Dim ds As DataSet

Dim dt As DataTable

Public Shared ReadOnly Property connectionString() As String

Get

Dim dbPath As String = System.Web.HttpContext.Current.Server.MapPath("~\Grid\Data\Access\Nwind.mdb")

Return ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath)

End Get

End Property

Private Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

dt = GetDataTable("SELECT Country, City, Index FROM Customers")

Me.RadGrid1.DataSource = dt

End Sub

Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

If Not IsPostBack Then

ds = GetDataSet("SELECT Country, City, Index FROM Customers")

Me.RadGrid1.MasterTableView.Columns.Clear()

For Each dataColumn As DataColumn In ds.Tables(0).Columns

Dim gridColumn As RadGrid.MyCustomFilteringColumn = New RadGrid.MyCustomFilteringColumn

Me.RadGrid1.MasterTableView.Columns.Add(gridColumn)

gridColumn.DataField = dataColumn.ColumnName

gridColumn.HeaderText = dataColumn.ColumnName

Next

End If

End Sub

Private Sub RadGrid1_ColumnCreating(ByVal sender As Object, ByVal e As Telerik.WebControls.GridColumnCreatingEventArgs) Handles RadGrid1.ColumnCreating

If (e.ColumnType = GetType(RadGrid.MyCustomFilteringColumn).Name) Then

e.Column = New RadGrid.MyCustomFilteringColumn

End If

End Sub

Private Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As Telerik.WebControls.GridCommandEventArgs) Handles RadGrid1.ItemCommand

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

Private Sub clrFilters_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clrFilters.Click

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 MyOleDbConnection As OleDbConnection = New OleDbConnection(connectionString)

Dim MyOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter

MyOleDbDataAdapter.SelectCommand = New OleDbCommand(query, MyOleDbConnection)

Dim myDataTable As DataTable = New DataTable

MyOleDbConnection.Open()

Try

MyOleDbDataAdapter.Fill(myDataTable)

Finally

MyOleDbConnection.Close()

End Try

Return myDataTable

End Function

Public Shared Function GetDataSet(ByVal query As String) As DataSet

Dim MyOleDbConnection As OleDbConnection = New OleDbConnection(connectionString)

Dim MyOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter

MyOleDbDataAdapter.SelectCommand = New OleDbCommand(query, MyOleDbConnection)

Dim myDataSet As DataSet = New DataSet("Customers")

MyOleDbConnection.Open()

Try

MyOleDbDataAdapter.Fill(myDataSet)

Finally

MyOleDbConnection.Close()

End Try

Return myDataSet

End Function

Default.aspx.cs

DataSet ds;

DataTable dt;

public static string connectionString

{

get

{

string dbPath = System.Web.HttpContext.Current.Server.MapPath("~\\Grid\\Data\\Access\\Nwind.mdb");

return ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath);

}

}

private void RadGrid1_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)

{

dt = GetDataTable("SELECT Country, City, Index FROM Customers");

this.RadGrid1.DataSource = dt;

}

private void Page_Load(object sender, System.EventArgs e)

{

if (!IsPostBack)

{

ds = GetDataSet("SELECT Country, City, Index FROM Customers");

this.RadGrid1.MasterTableView.Columns.Clear();

foreach (DataColumn dataColumn in ds.Tables(0).Columns)

{

RadGrid.MyCustomFilteringColumn gridColumn = new RadGrid.MyCustomFilteringColumn();

this.RadGrid1.MasterTableView.Columns.Add(gridColumn);

gridColumn.DataField = dataColumn.ColumnName;

gridColumn.HeaderText = dataColumn.ColumnName;

}

}

}

private void RadGrid1_ColumnCreating(object sender, Telerik.WebControls.GridColumnCreatingEventArgs e)

{

if (e.ColumnType == typeof(RadGrid.MyCustomFilteringColumn).Name)

{

e.Column = new RadGrid.MyCustomFilteringColumn();

}

}

private void RadGrid1_ItemCommand(object source, Telerik.WebControls.GridCommandEventArgs e)

{

if (e.CommandName == "Filter")

{

foreach (GridColumn column in e.Item.OwnerTableView.Columns)

{

column.CurrentFilterValue = string.Empty;

column.CurrentFilterFunction = GridKnownFunction.NoFilter;

}

}

}

private void clrFilters_Click(object sender, System.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)

{

OleDbConnection MyOleDbConnection = new OleDbConnection(connectionString);

OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();

MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, MyOleDbConnection);

DataTable myDataTable = new DataTable();

MyOleDbConnection.Open();

try

{

MyOleDbDataAdapter.Fill(myDataTable);

}

finally

{

MyOleDbConnection.Close();

}

return myDataTable;

}

public static DataSet GetDataSet(string query)

{

OleDbConnection MyOleDbConnection = new OleDbConnection(connectionString);

OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();

MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, MyOleDbConnection);

DataSet myDataSet = new DataSet("Customers");

MyOleDbConnection.Open();

try

{

MyOleDbDataAdapter.Fill(myDataSet);

}

finally

{

MyOleDbConnection.Close();

}

return myDataSet;

}

MyCustomFilteringColumn.vb

Imports System.Data

Imports RadGridFilter

Imports Telerik.WebControls

Imports System.Web.UI.WebControls

Imports System.Web.UI

Imports System

Imports System.Data.OleDb

Namespace RadGrid

' <summary>

' Custom column that shows filtering dropdown instead of textbox

' </summary>

'

Public Class MyCustomFilteringColumn

Inherits GridBoundColumn

Public Shared ReadOnly Property connectionString() As String

Get

Dim dbPath As String = System.Web.HttpContext.Current.Server.MapPath("~\Grid\Data\Access\Nwind.mdb")

Return ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath)

End Get

End Property

'RadGrid will cal this method when it initializes the controls inside the filtering item cells

Protected Overrides Sub SetupFilterControls(ByVal cell As TableCell)

MyBase.SetupFilterControls(cell)

cell.Controls.RemoveAt(0)

Dim combo As RadComboBox = New RadComboBox

combo.ID = ("RadComboBox1" + Me.DataField)

combo.ShowToggleImage = False

combo.Skin = "WindowsXP"

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 Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)

MyBase.SetCurrentFilterValueToControl(cell)

Dim combo As RadComboBox = CType(cell.Controls(0), RadComboBox)

If (Me.CurrentFilterValue <> String.Empty) Then

combo.Text = Me.CurrentFilterValue

End If

End Sub

'RadGrid will call this method when the filtering value should be extracted from the filtering input control(s)

Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String

Dim combo As RadComboBox = CType(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 Telerik.WebControls.RadComboBoxSelectedIndexChangedEventArgs)

Dim filterItem As GridFilteringItem = CType(CType(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 MyOleDbConnection As OleDbConnection = New OleDbConnection(connectionString)

Dim MyOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter

MyOleDbDataAdapter.SelectCommand = New OleDbCommand(query, MyOleDbConnection)

Dim myDataTable As DataTable = New DataTable

MyOleDbConnection.Open()

Try

MyOleDbDataAdapter.Fill(myDataTable)

Finally

MyOleDbConnection.Close()

End Try

Return myDataTable

End Function

End Class

End Namespace

MyCustomFilteringColumn.cs

using System.Data;

using RadGridFilter;

using Telerik.WebControls;

using System.Web.UI.WebControls;

using System.Web.UI;

using System;

using System.Data.OleDb;

namespace RadGrid

{

public class MyCustomFilteringColumn : GridBoundColumn

{

public static string connectionString

{

get

{

string dbPath = System.Web.HttpContext.Current.Server.MapPath("~\\Grid\\Data\\Access\\Nwind.mdb");

return ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath);

}

}

protected override void SetupFilterControls(TableCell cell)

{

base.SetupFilterControls(cell);

cell.Controls.RemoveAt(0);

RadComboBox combo = new RadComboBox();

combo.ID = ("RadComboBox1" + this.DataField);

combo.ShowToggleImage = false;

combo.Skin = "WindowsXP";

combo.EnableLoadOnDemand = true;

combo.AutoPostBack = true;

combo.MarkFirstMatch = true;

combo.Height = Unit.Pixel(100);

combo.ItemsRequested += new RadComboBoxItemsRequestedEventHandler(this.list_ItemsRequested);

combo.SelectedIndexChanged += new Telerik.WebControls.RadComboBoxSelectedIndexChangedEventHandler(this.list_SelectedIndexChanged);

cell.Controls.AddAt(0, combo);

cell.Controls.RemoveAt(1);

}

protected override void SetCurrentFilterValueToControl(TableCell cell)

{

base.SetCurrentFilterValueToControl(cell);

RadComboBox combo = (RadComboBox)cell.Controls[0];

if (this.CurrentFilterValue != string.Empty)

{

combo.Text = this.CurrentFilterValue;

}

}

protected override string GetCurrentFilterValueFromControl(TableCell cell)

{

RadComboBox combo = (RadComboBox)cell.Controls(0);

return combo.Text;

}

private void list_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)

{

(o as RadComboBox).DataTextField = this.DataField;

(o as RadComboBox).DataValueField = this.DataField;

(o as RadComboBox).DataSource = GetDataTable("SELECT DISTINCT " + this.UniqueName + " FROM Customers WHERE " + this.UniqueName + " LIKE '" + e.Text + "%'");

(o as RadComboBox).DataBind();

}

private void list_SelectedIndexChanged(object o, Telerik.WebControls.RadComboBoxSelectedIndexChangedEventArgs e)

{

GridFilteringItem filterItem = (GridFilteringItem)(o as RadComboBox).NamingContainer;

if (this.UniqueName == "Index")

{

filterItem.FireCommandEvent("Filter", new Pair("EqualTo", this.UniqueName));

}

filterItem.FireCommandEvent("Filter", new Pair("Contains", this.UniqueName));

}

public static DataTable GetDataTable(string query)

{

OleDbConnection MyOleDbConnection = new OleDbConnection(connectionString);

OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();

MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, MyOleDbConnection);

DataTable myDataTable = new DataTable();

MyOleDbConnection.Open();

try

{

MyOleDbDataAdapter.Fill(myDataTable);

}

finally

{

MyOleDbConnection.Close();

}

return myDataTable;

}

}

}

 

 

 

SOLUTION

The solution for the AJAX controls is illustrated in this demo. Another approach to customise the filter controls for a grid column and implement custom filtering is to define its FilterTemplate per your need as shown here.

Comments

• Vinny Tang , May 8, 2007

  I'd like to add an EditCommandItem column. How can I do this and still have the google filtering?

• Telerik Admin , May 11, 2007

  Adding GridEditCommandColumn to the grid Columns collection should not affect the presented google-filtering logic at all.

• Eric L , Jun 27, 2007

  Takes a little work to customize, but all in all very, very nice.

• Wayne Pirtle , Nov 26, 2007

  This would be more useful if you had a c# zip file to download.

• Telerik Admin , Nov 27, 2007

  We provided code snippets in C# as well which can replace the VB.NET logic. Additionally, you can convert the code using our free online converter: http://converter.telerik.com/

• Brett , Jun 12, 2008

  In the CustomFilteringColumn.cs under SetCurrentFilterValueToControl and GetCurrentFilterValueFromControl I think it should be: RadComboBox combo = (RadComboBox)cell.Controls[0]; Instead of: RadComboBox combo = (RadComboBox)cell.Controls(0);

• Telerik Admin , Jun 13, 2008

  You are absolutely right!We made the necessary change in the code as per your suggestion. Thank you for pointing out this detail.

• Paul Haines , Sep 3, 2008

  Pretty good for a starting point, but it'd be nice if the column was more general in terms of where it gets its data-source from; rather than hard-coding a source into it. Plus, in the "list_SelectedIndexChanged" event handler, shouldn't it be an if..then..else statement, otherwise won't two 'filter' commands will be raised for a column with "Index" as its unique name? Oh, and Brett's suggestion seems to've been lost at some point.

• Dave Durose , Sep 9, 2008

  it is a cool concept and i would love to implement for certain filters in my radgrid, but seems like a fair bit of work involved to get it going. please look for a way to integrate the google-like filtering into your radgrid in an easier fashion. thanx!

• Eric Santos , Nov 7, 2008

  Great article! Congratulations!

If you'd like to comment on this KB article, please, send us a Support Ticket.
Thank you!