Custom Paging and Searching in WebGrid using jQuery AJAX in ASP.Net MVC

Last Reply one month ago By dharmendr

Posted one month ago

I am populating a web grid by data with around 20K rows in it and displaying only 10 rows per page.

I am planning to provide searching on this web grid on random columns.

What will be the best approach from following:

1) Searching by calling Action method in Ajax call?

2) Holding data in client side and searching it by JQuery?

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago

Hi abhi619,

Using the below two article i have created the example.

Populate (Bind) WebGrid from database using jQuery AJAX and JSON in ASP.Net MVC

Search and Filter GridView as you type in TextBox using jQuery AJAX in ASP.Net

SQL

CREATE PROCEDURE [dbo].[GetCustomersPageWise]
	  @SearchTerm VARCHAR(100) = ''
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
             ,[CustomerID]
             ,[CompanyName]
             ,[ContactName]
             ,[ContactTitle]
             ,[Address]
             ,[City]
             ,[Region]
             ,[PostalCode]
             ,[Country]
             ,[Phone]
             ,[Fax]
      INTO #Results
      FROM [Customers]
	  WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
   
      SELECT @RecordCount = COUNT(*)
      FROM #Results
         
      SELECT *
      FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
   
      DROP TABLE #Results
END

Model

public class CustomerModel
{
    public List<Customer> Customers { get; set; }
    public string SearchTerm { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int RecordCount { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        List<Customer> dummy = new List<Customer>();
        dummy.Add(new Customer());
        return View(dummy);
    }

    [HttpPost]
    public JsonResult AjaxMethod(int pageIndex, string searchTerm)
    {
        NorthwindEntities entities = new NorthwindEntities();
        CustomerModel model = new CustomerModel();
        model.SearchTerm = searchTerm;
        model.PageIndex = pageIndex;
        model.PageSize = 10;
        ObjectParameter recordCount = new ObjectParameter("RecordCount", typeof(int));
        model.Customers = entities.GetCustomers(model.SearchTerm, model.PageIndex, model.PageSize, recordCount).ToList();
        model.RecordCount = Convert.ToInt32(recordCount.Value);
        return Json(model);
    }
}

View

@model List<Customer>

@{
    Layout = null;
    WebGrid webGrid = new WebGrid(source: Model, canSort: false, canPage: false);
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }

        table {
            border: 1px solid #ccc;
            border-collapse: collapse;
            background-color: #fff;
        }

            table th {
                background-color: #B8DBFD;
                color: #333;
                font-weight: bold;
            }

            table th, table td {
                padding: 5px;
                border: 1px solid #ccc;
            }

            table, table table td {
                border: 0px solid #ccc;
            }

        .Pager span {
            text-align: center;
            color: #333;
            display: inline-block;
            width: 20px;
            background-color: #B8DBFD;
            margin-right: 3px;
            line-height: 150%;
            border: 1px solid #B8DBFD;
        }

        .Pager a {
            text-align: center;
            display: inline-block;
            width: 20px;
            background-color: #ccc;
            color: #333;
            border: 1px solid #ccc;
            margin-right: 3px;
            line-height: 150%;
            text-decoration: none;
        }
    </style>
</head>
<body>
    <h4>Customers</h4>
    <hr />
    <input type="text" id="txtSearch" />
    <br /><br />
    @webGrid.GetHtml(
        htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
        columns: webGrid.Columns(
                 webGrid.Column("CustomerID", "CustomerID"),
                 webGrid.Column("ContactName", "ContactName"),
                 webGrid.Column("City", "City"),
                 webGrid.Column("Country", "Country")))

    <br />
    <div class="Pager"></div>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="~/Scripts/ASPSnippets_Pager.min.js"></script>
    <script type="text/javascript">
        $(function () {
            GetCustomers(1, $('#txtSearch').val().trim());
        });
        $("body").on("click", ".Pager .page", function () {
            GetCustomers(parseInt($(this).attr('page')), $('#txtSearch').val().trim());
        });
        $("[id*=txtSearch]").live("keyup", function () {
            GetCustomers(parseInt(1), $('#txtSearch').val().trim());
        });
        function GetCustomers(pageIndex, searchTerm) {
            $.ajax({
                type: "POST",
                url: "/Home/AjaxMethod",
                data: '{pageIndex: ' + pageIndex + ', searchTerm: "' + searchTerm + '"}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
        };
        function OnSuccess(response) {
            var model = response;
            var row = $("#WebGrid tbody tr:last-child").clone(true);
            $("#WebGrid tbody tr").remove();
            $.each(model.Customers, function () {
                var customer = this;
                $("td", row).eq(0).html(customer.CustomerID);
                $("td", row).eq(1).html(customer.ContactName);
                $("td", row).eq(2).html(customer.City);
                $("td", row).eq(3).html(customer.Country);
                $("#WebGrid").append(row);
                row = $("#WebGrid tbody tr:last-child").clone(true);
            });

            $(".Pager").ASPSnippets_Pager({
                ActiveCssClass: "current",
                PagerCssClass: "pager",
                PageIndex: model.PageIndex,
                PageSize: model.PageSize,
                RecordCount: model.RecordCount
            });
        };
    </script>
</body>
</html>