Hi hugocc13,
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].[GetCustomersPageWiseSearch]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
,@search nvarchar(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY [CustomerID] DESC) AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
INTO #Results
FROM [Customers]
WHERE (ContactName LIKE @search + '%' OR @search IS NULL)
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
{
///<summary>
/// Gets or sets Customers.
///</summary>
public List<Customer> Customers { get; set; }
///<summary>
/// Gets or sets CurrentPageIndex.
///</summary>
public int CurrentPageIndex { get; set; }
///<summary>
/// Gets or sets PageCount.
///</summary>
public int PageCount { get; set; }
}
public class Customer
{
public string Id { get; set; }
public string Name { get; set; }
public string Company { get; set; }
}
Namespaces
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View(this.GetCustomers("", 1));
}
[HttpPost]
public ActionResult Index(string searchTerm, int currentPageIndex = 1)
{
return View(this.GetCustomers(searchTerm, currentPageIndex));
}
private CustomerModel GetCustomers(string searchTerm, int currentPage)
{
int pageSize = 10;
CustomerModel customerModel = new CustomerModel();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand("GetCustomersPageWiseSearch");
List<Customer> customers = new List<Customer>();
int recordCount = 0;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", currentPage);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.AddWithValue("@search", searchTerm);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
customers.Add(new Customer
{
Id = sdr["CustomerID"].ToString(),
Name = sdr["ContactName"].ToString(),
Company = sdr["CompanyName"].ToString()
});
}
con.Close();
recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
}
}
double pageCount = (double)((decimal)recordCount / Convert.ToDecimal(pageSize));
customerModel.Customers = customers;
customerModel.PageCount = (int)Math.Ceiling(pageCount);
customerModel.CurrentPageIndex = currentPage;
return customerModel;
}
}
View
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<Paging_Searching_ADO.Net.Models.CustomerModel>" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Index</title>
<script type="text/javascript">
function PagerClick(index) {
document.getElementById("hfCurrentPageIndex").value = index;
document.forms[0].submit();
}
</script>
</head>
<body>
<div>
<% using (Html.BeginForm("Index", "Home", FormMethod.Post)){%>
<%:Html.TextBox("searchTerm")%>
<input type="submit" name="name" value="Search" />
<hr />
<table>
<tr>
<th>Id</th>
<th>Name</th>
<th>Company</th>
</tr>
<%foreach (Customer customer in Model.Customers)
{%>
<tr>
<td><%=customer.Id%></td>
<td><%=customer.Name%></td>
<td><%=customer.Company%></td>
</tr>
<%}%>
</table>
<br />
<table>
<tr>
<%for (int i = 1; i <= Model.PageCount; i++){%>
<td><%if (i != Model.CurrentPageIndex){%>
<a href="javascript:PagerClick(<%=i%>);"><%=i%></a>
<% } else {%>
<span><%=i%></span>
<% }%>
</td>
<% }%>
</tr>
</table>
<input type="hidden" id="hfCurrentPageIndex" name="currentPageIndex" />
<% }%>
</div>
</body>
</html>
Screenshot
