Thursday, January 16, 2014

JQuery UI AutoComplete TextBox from Database using PageMethod in ASP.Net

In this post we’ll see how to user Jquery UI Autocomplete with asp.net pagemethod.

For database, we’ll be using Northwind database.

PageMethod:
[ScriptMethod(ResponseFormat= ResponseFormat.Json)]
    [WebMethod]
    public static List<AutoCompleteHelper> GetEmployee(string searchText)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager
                    .ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select CustomerID,ContactName from Customers where " +
                "ContactName like @SearchText + '%'";
                cmd.Parameters.AddWithValue("@SearchText", searchText);
                cmd.Connection = conn;
                List<AutoCompleteHelper> listAutoCompleteHelper = new List<AutoCompleteHelper>();
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        AutoCompleteHelper objCompleteHelper = new AutoCompleteHelper();
                        objCompleteHelper.displayText = Convert.ToString(sdr["ContactName"]);
                        objCompleteHelper.displayValue = Convert.ToString(sdr["CustomerID"]);
                        listAutoCompleteHelper.Add(objCompleteHelper);
                        
                    }
                }
                conn.Close();
                Thread.Sleep(3000);
                return listAutoCompleteHelper;
            }
        }
    }
In above code, we’re fetching data from customer table from northwind database based on text entered by user. You may want to have this code in separate (data access) layer and must use stored procedure rather than inline query, I’ve included this code just for demonstration purpose.

Select query will fetch CustomerId and CustomerName based on user input.

You may want to omit Thread.Sleep(3000), here it is used to delay response so that loading image can be displayed in textbox.

Pagemethod returns list of AutocompleteHelper class which is as below:
public class AutoCompleteHelper
{
    public string displayText { get; set; }
    public string displayValue { get; set; }
}
JavaScript:
$(document).ready(function () {
            $('#<%=txtEmployeeSearch.ClientID %>').autocomplete({
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        url: '<%=ResolveUrl("~/JqueryAutocomplete.aspx/GetEmployee")%>',
                        data: "{'searchText': '" + request.term + "'}",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function (dataJson) {
                            var data = dataJson.hasOwnProperty("d") ? dataJson.d : dataJson;
                            response(
                                    $.map(data, function (item) {
                                        return {
                                            label: item.displayText,
                                            value: item.displayValue

                                        }
                                    }));
                        },
                        failure: function (data) {
                            alert(data);
                        }
                    });
                },
                minLength: 1,
                search: function (e, u) {
                    $(this).addClass('ajaxprocessing');
                },
                response: function( event, ui ) {
                    $(this).removeClass('ajaxprocessing');
                },
                select: function (event, ui) {
                    event.preventDefault();
                    $('#<%= hdnEmployeeId.ClientID %>').val(ui.item.value);
                    this.value = ui.item.label;
                    return false;
                },
                focus: function (event, ui) {
                    event.preventDefault();
                    this.value = ui.item.label;
                }
            });
        });
We’re using search and response events of Jquery UI to add and remove ajaxprocessing class respectively to show loading image. In select event we’re storing value(customer Id) in hiddenfield.
In success callback function, we’ve below line:
 var data = dataJson.hasOwnProperty("d") ? dataJson.d : dataJson;
Which checks whether response is surrounded by d attribute. (Ajax response contains .d attribute, if you’re using .net 3.5+, but in asp.net 2.0, it simply returns response. For more info, check this post of Dave Ward.)

HTML:
 <asp:TextBox ID="txtEmployeeSearch" runat="server"></asp:TextBox>
 <asp:HiddenField ID="hdnEmployeeId" runat="server" />
CSS:
 .ajaxprocessing
    {
        background: url('Content/Images/ajax-loader.gif');
        background-repeat: no-repeat;
        background-position: right;
    }
Now run your page, and it should be working as shown in below image:

Jquery UI Autocomplete using pagemethods

Notes:

  • If your pagemethod is not being hit and your ajax response contains whole page, then make sure you’ve added necessary configuration entries to make pagemethod work. Please add settings mentioned here.
  • IE10 may not pass parameter for Ajax Post, and hence it may throw “Invalid web service call, missing value for parameter” error. To solve the error, you need to add below meta tag in page head:

<meta http-equiv="x-ua-compatible" content="IE=9" >

I hope it helps!

Wednesday, January 15, 2014

Jquery DataTables : Add column filter and remove global filter

Jquery DataTables plugin allows you to search through all the columns and it works pretty well. Now if you wan to add column wise filter, you need to use JQuery DataTables Column Filter plugin.

To extend Jquery DataTables with Column Filter plugin, you need to have code like below:
     <asp:Repeater ID="repUserTempAssignment" runat="server" DataSourceID="SqlDataSource1"
            EnableViewState="false">
            <HeaderTemplate>
                <table class="table-data" id="tblEmployee" style="width: 100%px;">
                    <thead>
                        <tr>
                            <th>
                                First Name
                            </th>
                            <th>
                                Last Name
                            </th>
                            <th>
                                City
                            </th>
                            <th>
                                Country
                            </th>
                        </tr>
                        <tr>
                            <th>
                                First Name
                            </th>
                            <th>
                                Last Name
                            </th>
                            <th>
                                City
                            </th>
                            <th>
                                Country
                            </th>
                        </tr>
                    </thead>
                    <tbody>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td>
                        <%# Eval("FirstName") %>
                    </td>
                    <td>
                        <%# Eval("LastName") %>
                    </td>
                    <td>
                        <%# Eval("City") %>
                    </td>
                    <td>
                        <%# Eval("Country") %>
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </tbody><tfoot>
                </tfoot>
                </table>
            </FooterTemplate>
        </asp:Repeater>
I’m using asp.net repeater control to bind employee list. If you notice in above code table has two rows in header. One will work as table header and one will be replaced with textboxes at runtime which will work as column filter. For that you need below script:
$('#tblEmployee').dataTable(
            {
                "bSortCellsTop": true
            }).columnFilter(
            {
                sPlaceHolder: "head:after"
            });
bSortCellsTop: This will apply sorting on first row of header.
sPlaceHolder: It will add textboxes in second row of header for every column. If you omit this, there won’t be any filters applied to columns.

After adding above code, table will look like below:

image

Now to get rid of global search filter you need to add below code in dataTable settings:

"sDom": '<"top"l>rt<"bottom"ip><"clear">'

sDom: It allow you to specify exactly where in the DOM you want DataTables to inject the various controls it adds to the page.

If you’ve not looked into documentation and if you google for quick solution, you might find may solutions stating to use bFilter, but it simply enable/disable filtering capability of dataTable. If one doesn’t want to use default styling of dataTable and want to apply his own classes, bSortClasses needs to be used. Final code would be:

$(document).ready(function () {
            $('#tblEmployee').dataTable(
            {
                "bSortCellsTop": true,
                "sDom": '<"top"l>rt<"bottom"ip><"clear">',
                "bFilter": false,
                "bSortClasses": false
            }).columnFilter(
            {
                sPlaceHolder: "head:after"
            });
        });

Using above code, global filter won’t be added to page as shown below:

image

I hope it helps!