Custom Paging and Searching using Stored Procedure and ADO.Net in ASP.Net MVC

Hi SajidHussa,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

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; }
}

public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Region { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
    public string Fax { 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)
    {
        CustomerModel model = new CustomerModel();
        model.SearchTerm = searchTerm;
        model.PageIndex = pageIndex;
        model.PageSize = 10;

        List<Customer> customers = new List<Customer>();
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SearchTerm", model.SearchTerm);
                cmd.Parameters.AddWithValue("@PageIndex", model.PageIndex);
                cmd.Parameters.AddWithValue("@PageSize", model.PageSize);
                cmd.Parameters.Add("@RecordCount", SqlDbType.VarChar, 30);
                cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    customers.Add(new Customer
                    {
                        CustomerID = sdr["CustomerID"].ToString(),
                        ContactName = sdr["ContactName"].ToString(),
                        City = sdr["City"].ToString(),
                        Country = sdr["Country"].ToString()
                    });
                }
                con.Close();

                model.Customers = customers;
                model.RecordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            }
        }
           
        return Json(model);
    }
}

View

@model IEnumerable<Search_Page_MVC.Models.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>
</head>
<body>
    <h4>Customers</h4>
    <hr />
    <input type="text" id="txtSearch" />
    <input type="button" id="btnSearch" value="Search"/>
    <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*=btnSearch]").live("click", 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>

Screenshot