Telerik Radgrid With AutoSuggest Filter Using JQuery

Introduction

Hi all, after a long time I'm back here to write articles. In this article I'll show you how to make an auto suggest on Telerik RadGrid filter. On the Telerik demo site you can find the autosuggest implemented using radcombo which does not satisfy the developer requirements. In this article we will see how to use the jQuery UI autocomplete plugin to make the RadGrid filter with autosuggest options.

To make our RadGrid filter autosuggest you need to use the following steps. So let's start implementing the jQuery autocomplete on RadGrid filter.

Step 1: First you must know that when you need to use the jQuery UI plugin with associated Telerik controls you must register the script path in RadScriptManager like below:

<telerik:RadScriptManager ID="RadScriptManager1" runat="server">
            <Scripts>                                
                     
<asp:ScriptReference Assembly="Telerik.Web.UI"Name="Telerik.Web.UI.Common.Core.js" />
                     <asp:ScriptReference Assembly="Telerik.Web.UI"Name="Telerik.Web.UI.Common.jQuery.js" />
                     <asp:ScriptReference Assembly="Telerik.Web.UI"Name="Telerik.Web.UI.Common.jQueryInclude.js" />
                     <asp:ScriptReferencePath="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" />
                     <
asp:ScriptReference Path="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js" />
          </Scripts>
</
telerik:RadScriptManager>

In the above code we are adding a reference to the jQuery autocomplete file.

Step 2: After registering the script path in RadScriptManager as usual we have to reference those jQuery files and the associated stylesheet in the head tag of our ASP.Net form like below:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"
type = "text/javascript"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"
type = "text/javascript"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
rel = "Stylesheet" type="text/css" />

Step 3: After adding a reference to the script files we need to create the the RadGrid with filter enabled on it. In this case we must use the textbox control when in the FilterItemTemplate tag instead of using the default filter menu. Here we will create our own filter menu with textboxes like below. Here I've added only one filter textbox; you can extend it as needed. In this example we will use the RadGrid's needdatasource event to bind the grid. So our grid is like below:

<telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="true" AllowPaging="true"
AllowSorting="True" GridLines="None" AutoGenerateColumns="False" PageSize="50"ShowGroupPanel="false"  
AllowMultiRowSelection="true" OnItemCommand="RadGrid1_ItemCommand"Height="100
"
  OnNeedDataSource="RadGrid1_NeedDataSource"OnItemDataBound="RadGrid1_OnItemDataBound">                           
<PagerStyle Mode="NextPrevNumericAndAdvanced" AlwaysVisible="true" />
<MasterTableView TableLayout="Fixed" HierarchyLoadMode="Client" DataKeyNames="CandidateID"
RetrieveAllDataFields="false"ClientDataKeyNames="CandidateID" GroupLoadMode="Client"
ShowHeadersWhenNoRecords="true" EnableNoRecordsTemplate="true" NoMasterRecordsText="No
candidate found.">
<NoRecordsTemplate>
<
div>No records to display</div>
</NoRecordsTemplate> 
<Columns>
<
telerik:GridBoundColumn DataField="CandidateID" UniqueName="CandidateID"
 Visible="True"AllowFiltering="false" AllowSorting="false">
</telerik:GridBoundColumn>
<
telerik:GridTemplateColumn DataField="FormattedName" HeaderText="Candidate Name"
CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" ShowFilterIcon="false"
UniqueName="FormattedName" SortExpression="FormattedName" FilterControlWidth="100%">
<ItemTemplate>
<%#Eval("FormattedName"%>
</ItemTemplate>
<
FilterTemplate>
<
asp:TextBox ID="txtformattedname" runat="server" onfocus="OnFormattedNameFocus(this,event)"></asp:TextBox>
<telerik:RadScriptBlock ID="RadScriptBlockFormattedName" runat="server"> 
<script type="text/javascript">                                   
    function OnFormattedNameSelected(selectedval) {
        if (selectedval != "None") {
        var _isvalue = $get('<%= autocanname.ClientID %>').value;
        var _autocannamehid = $get('<%= autocanname.ClientID %>');
         if (_isvalue == "True" && selectedval == "") {
         _autocannamehid.value = "False";
          var tableView = $find("<%= RadGrid1.ClientID %>").get_masterTableView();
          tableView.filter("FormattedName", selectedval, "Contains");
          }
          else if (_isvalue == "False" && selectedval != "") {
          _autocannamehid.value = "True";
          var tableView = $find("<%= RadGrid1.ClientID %>").get_masterTableView();
          tableView.filter("FormattedName", selectedval, "Contains");
          }
          else if (_isvalue == "True" && selectedval != "") {
          var tableView = $find("<%= RadGrid1.ClientID %>").get_masterTableView();
          tableView.filter("FormattedName", selectedval, "Contains");
           }
          }
        else {
             var _autocannamehid = $get('<%= autocanname.ClientID %>');
             _autocannamehid.value = "False";
             var tableView = $find("<%= RadGrid1.ClientID %>").get_masterTableView();
             tableView.filter("FormattedName""""Contains");
             }
      }
      function OnFormattedNameFocus(sender, args) {
         var txtformattedname = sender;
         $(txtformattedname).autocomplete({
         source: function(request, response) {
         $.ajax({
         url: 'AutoSuggest.asmx/GetFormattedList',
         data: "{ 'prefixText': '" + request.term + "'}",
         dataType: "json",
         
type: "POST",
         contentType: "application/json; charset=utf-8",
         success: function(data) {
         response($.map(data.d, function(item) {
                    return {
                        label: item.split('-')[0] 
                    }
                }))
            },
            error: function(response) {
                alert(response.responseText);
            },
            failure: function(response) {
                alert(response.responseText);
            }
        });
        },
      select: function(e, i) {
          $(txtformattedname).val(i.item.label);
          setTimeout(OnFormattedNameSelected(i.item.label), "10"); 
                     },
                     minLength: 0
                 });
             }                                              
         </script> </telerik:RadScriptBlock>
         </FilterTemplate>
     </telerik:GridTemplateColumn>
     <telerik:GridBoundColumn DataField="DateOfBirth" UniqueName="DateOfBirth" HeaderText="Date of
Birth"
 Visible="True" AllowFiltering="false" AllowSorting="false"> </telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Gender" UniqueName="Gender" Visible="True" 
AllowFiltering="false"HeaderText="Gender" AllowSorting="false">
</telerik:GridBoundColumn>
</
Columns>
</
MasterTableView>
</
telerik:RadGrid>

In the above code we have created the grid in which you can see the candidate's name column with custom radgridfilteringitem which contains the textbox. On the focus of this textbox we will attach an autosuggest to it using jQuery. In the above scrpt block we have one more method which is responsible for filtering the RadGrid on the selected item populated from the jQuery autocomplete list. In the focus method only you can see we are calling this method. When the user selects any item from the populated list it will start the filtering item with respect to the selected item. For the second method we have declared one hidden field which initially contains the value false; this is for when the user clears the filter we must clear the filter value so using the hidden field we are tracing the user action either he is filtering for the first time or not the first time. This jQuery autosuggest list is populated from the webservice method. So next we have to add the webservice with a method attribute callable from scripts.

Step 4: Next add the Webservice in your application and populate the list in the webmethod and return the response to the ajax call from the textbox focus austosuggest. You can define the method like the following:

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]//required for call from script
public string[] GetFormattedList(string prefixText)
{
     List<string> formattednames = new List<string>();
     using (SqlConnection conn = new SqlConnection())
     {
         conn.ConnectionString = "Your Connection String Here";//ConfigurationManager.ConnectionString
         ["THTProConnectionString"].ConnectionString;
         using (SqlCommand cmd = new SqlCommand())
         {
              cmd.CommandText = "select Distinct Top 10 FormattedName From Candidates where " +
              "FormattedName like @SearchText + '%'";
              cmd.Parameters.AddWithValue("@SearchText", prefixText);
              cmd.Connection = conn;
              conn.Open();
              formattednames.Add(string.Format("{0}""None"));
              using (SqlDataReader sdr = cmd.ExecuteReader())
              {
                  while (sdr.Read())
                  {
                      formattednames.Add(string.Format("{0}", sdr["FormattedName"]));
                  }
              }
              conn.Close();
          }
          return formattednames.ToArray();
     }
}


Step 5: After our webservice is ready we need to populate the grid so in the NeedDataSource event populate your grid from the database like the following:

protected
 void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
      SqlConnection cn = new SqlConnection("your connection string here");
      SqlDataAdapter da = new SqlDataAdapter("Select CandidateId,FormattedName,DateOfBirth,Gender From
Candidates"
,cn);
      DataSet ds = new DataSet();
      da.Fill(ds);
      RadGrid1.MasterTableView.VirtualItemCount = ds.Tables[0].Rows.Count;
      RadGrid1.DataSource = ds.Tables[0];
}


Step 6: Now we are ready to run the application and see the RadGrid filter with autosuggest options populated from the jQuery UI.

Conclusion

In this way we can use jQuery autocomplete on a Telerik RadGrid filter.