Paging in MVC with sql datanbase
Data base
create table tblCustomer([CustomerID] int identity(1,1)
,[CompanyName] varchar(100)
,[ContactName] varchar(100)
,[ContactTitle] varchar(100)
,[Address] varchar(100)
,[City] varchar(100)
,[Region] varchar(100)
,[PostalCode] varchar(100)
,[Country] varchar(100)
,[Phone] varchar(100)
,[Fax] varchar(100)
)
-------------
insert into tblCustomer ( CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax)
values('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax'),
('CompanyName','ContactName','ContactTitle','Address','City','Region','PostalCode','Country','Phone','Fax')
----
create PROCEDURE [dbo].[GetCustomersPageWise]
@SearchTerm VARCHAR(100) =''
,@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SELECT top 105 ROW_NUMBER() OVER
(
ORDER BY [CustomerID]ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax]
INTO #Results
FROM tblCustomer
WHERE [CompanyName]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----
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace binaryproj.Models
{
public class CustomPagingModel<T>
{
public string SearchTerm { get; set; }
public List<T> Result { get; set; }
public Pager pager { get; set; }
}
public class Pager
{
public Pager(int totalItems, int? page, int pageSize = 10)
{
// Total Paging need to show
int _totalPages = (int)Math.Ceiling((decimal)totalItems / (decimal)pageSize);
//Current Page
int _currentPage = page != null ? (int)page : 1;
//Paging to be starts with
int _startPage = _currentPage - 5;
//Paging to be end with
int _endPage = _currentPage + 4;
if (_startPage <= 0)
{
_endPage -= (_startPage - 1);
_startPage = 1;
}
if (_endPage > _totalPages)
{
_endPage = _totalPages;
if (_endPage > 10)
{
_startPage = _endPage - 9;
}
}
//Setting up the properties
TotalItems = totalItems;
CurrentPage = _currentPage;
PageSize = pageSize;
TotalPages = _totalPages;
StartPage = _startPage;
EndPage = _endPage;
}
public int TotalItems { get; set; }
public int CurrentPage { get; set; }
public int PageSize { get; set; }
public int TotalPages { get; set; }
public int StartPage { get; set; }
public int EndPage { 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 ActionResult CustomerDetails(int pageIndex = 1, string searchTerm = "")
{
CustomPagingModel<Customer> model = new CustomPagingModel<Customer>();
model.SearchTerm = searchTerm;
int PageIndex = pageIndex;
int PageSize = 10;
List<Customer> customers = new List<Customer>();
string constring = @"server=DESKTOP-PJ2K9I2\SQLEXPRESS;Database=binary_db;integrated security=true";// 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", PageIndex);
cmd.Parameters.AddWithValue("@PageSize", 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(),
CompanyName = sdr["CompanyName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
con.Close();
model.Result = customers;
int RecordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
var pager = new Pager(RecordCount > 0 ? RecordCount : 0, PageIndex, PageSize);
model.pager = pager;
}
}
return View(model);
}
[HttpPost]
public ActionResult CustomerDetails(CustomPagingModel<Customer> model, FormCollection frm)
{
int PageIndex = Convert.ToString(frm["hfCurrentPageIndex"]) != "" ? Convert.ToInt32(frm["hfCurrentPageIndex"]) : 1;
int PageSize = 10;
List<Customer> customers = new List<Customer>();
string constring = @"server=DESKTOP-PJ2K9I2\SQLEXPRESS;Database=binary_db;integrated security=true";// 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", PageIndex);
cmd.Parameters.AddWithValue("@PageSize", 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(),
CompanyName = sdr["CompanyName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
con.Close();
model.Result = customers;
model.Result = customers;
int RecordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
var pager = new Pager(RecordCount > 0 ? RecordCount : 0, PageIndex, PageSize);
model.pager = pager;
}
}
return View(model);
}
-------------------------
@model binaryproj.Models.CustomPagingModel<binaryproj.Models.Customer>
@{
ViewBag.Title = "Employee List";
Layout = null;
}
<script src="~/Scripts/jquery-3.3.1.min.js"></script>
<style>
table {
width: 100%;
}
table tr td {
border: 2px solid black;
text-align: center;
word-wrap: break-word;
}
table tr:hover {
background-color: #000;
color: #fff;
}
table tr th {
border: 2px solid black;
text-align: center;
background-color: #fff;
color: #000;
}
</style>
<h2>Employee List</h2>
@using (Html.BeginForm())
{
<table><tr>Search <td></td><td>@Html.TextBoxFor(M => Model.SearchTerm) </td><td><input type="submit" value="Search" /></td></tr></table>
<table>
<tr>
<th>
@Html.ActionLink("Employee Name", "CustomerDetails", new { sortOrder = "CompanyName", CurrentSort = ViewBag.CurrentSort })
</th>
<th>
@Html.ActionLink("ContactName", "CustomerDetails", new { sortOrder = "ContactName", CurrentSort = ViewBag.CurrentSort })
</th>
<th>
@Html.ActionLink("City", "CustomerDetails", new { sortOrder = "City", CurrentSort = ViewBag.CurrentSort })
</th>
<th>
@Html.ActionLink("Country", "CustomerDetails", new { sortOrder = "Country", CurrentSort = ViewBag.CurrentSort })
</th>
</tr>
@foreach (var rsItem in Model.Result)
{
<tr>
<td>
@Html.DisplayFor(modelItem => rsItem.CompanyName)
</td>
<td>
@Html.DisplayFor(modelItem => rsItem.ContactName)
</td>
<td>
@Html.DisplayFor(modelItem => rsItem.City)
</td>
<td>
@Html.DisplayFor(modelItem => rsItem.Country)
</td>
</tr>
}
</table>
<br />
@Html.Partial("_CustPaging", Model.pager)
}
Comments
Post a Comment