paging

create proc getPaging( @noofpages int,@Currentpage int,@result varchar(1000) out)

begin

--declare @noofpages int=10,@Currentpage int=1,@result varchar(1000)=''

Declare @noofshowpage int=5,@min int=1,@max int=0,@type varchar(10)='Next'

set @result=''

if(@Currentpage%@noofshowpage=0)

begin

set @min=@Currentpage

if(@type='Next')

begin

if(@noofpages<@min+@noofshowpage)

set @max=@noofpages

else

set @max=@min+@noofshowpage

end

else

begin

if(@noofpages>@min-@noofshowpage)

set @max=@noofpages

else

set @max=@min-@noofshowpage

end

else

begin

if(@Currentpage>5)

begin

set @min=(@Currentpage/@noofshowpage)*@noofshowpage

set @max=@min+@noofshowpage

end

else

begin

set @min=1

set @max=5

end

select @min,@max

while(@min<=@max)

begin

if(@min%@noofshowpage=0 and @Currentpage>4 and @min!=@max)

set @result=@result+'<td><a href=javascript:void(0); title="'+cast(@min-1 as varchar)+'"class="pagelink">Previous 5</a></td>'

--set @result=@result+ ' Ppage'+ cast(@min-1 as varchar)

if(@Currentpage=@min)

begin

set @result=@result+'<td><a href=javascript:void(0); title="'+cast(@min as varchar)+'"class="btn btn-primary pagelink">'+cast(@min as varchar)+'</a></td>'

--set @result=@result+ ' spage'+ cast(@min as varchar)

end

else

begin

set @result=@result+'<td><a href=javascript:void(0); title="'+cast(@min as varchar)+'"class="pagelink">'+cast(@min as varchar)+'</a></td>'

--set @result=@result+ ' page'+ cast(@min as varchar)

end

if(@min=@max)

set @result=@result+'<td><a href=javascript:void(0); title="'+cast(@min+1 as varchar)+'"class="pagelink">Next 5</a></td>'

--set @result=@result+ ' Npage'+ cast(@min+1 as varchar)

if(@min>=@noofpages)

set @min=@max+1

set @min=@min+1

end

--select @result

end

---------------------

ALTER PROCEDURE [dbo].[GetCustomersPageWise]

@SearchTerm VARCHAR(100) =''

,@PageIndex INT = 1

,@PageSize INT = 10

,@RecordCount INT OUTPUT

,@pagingHtml nvarchar(2000) OUTPUT

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

declare @noofpages int=0

set @noofpages=CEILING(cast(@RecordCount as decimal)/@PageSize)

exec getPaging @noofpages,@PageIndex ,@pagingHtml out

DROP TABLE #Results

END

----------------------

@model MVCProj.Models.CustomerPaging

<tr>

@Html.Raw(Model.pagingHtml)

</tr>

</table>

@Html.Hidden("hfCurrentPageIndex")

$(document).ready(function () {

$(".pagelink").click(function () {

var index = this.title;

document.getElementById("hfCurrentPageIndex").value = index;

document.forms[0].submit();

});

})

</script>

---------------

@model MVCProj.Models.CustomerModel<MVCProj.Models.Customer>

@using PagedList.Mvc;

ViewBag.Title = "Employee List";

Layout = "~/Views/Shared/_Layout.cshtml";

}

<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("_Gridpaging1", Model.CustomerPaging)

}

--------------------------------

public ActionResult CustomerDetails(int pageIndex = 1, string searchTerm = "")

{

CustomerModel<Customer> model = new CustomerModel<Customer>();

CustomerPaging objCustomerPaging = new CustomerPaging();

model.SearchTerm = searchTerm;

objCustomerPaging.PageIndex = pageIndex;

objCustomerPaging.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", objCustomerPaging.PageIndex);

cmd.Parameters.AddWithValue("@PageSize", objCustomerPaging.PageSize);

cmd.Parameters.Add("@RecordCount", SqlDbType.VarChar, 30);

cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

cmd.Parameters.Add("@pagingHtml", SqlDbType.VarChar, 2000);

cmd.Parameters["@pagingHtml"].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;

objCustomerPaging.RecordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);

objCustomerPaging.pagingHtml = Convert.ToString(cmd.Parameters["@pagingHtml"].Value);

int NoOfpage = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(objCustomerPaging.RecordCount) / objCustomerPaging.PageSize));

objCustomerPaging.NoOfpage = NoOfpage > 1 ? NoOfpage : 0;

objCustomerPaging.Url = "/Home/CustomerDetails";

model.CustomerPaging = objCustomerPaging;

}

return View(model);

}

[HttpPost]

public ActionResult CustomerDetails(CustomerModel<Customer> model, FormCollection frm)

{

CustomerPaging objCustomerPaging = new CustomerPaging();

objCustomerPaging.PageIndex = Convert.ToString(frm["hfCurrentPageIndex"]) != "" ? Convert.ToInt32(frm["hfCurrentPageIndex"]) : 1;

objCustomerPaging.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", objCustomerPaging.PageIndex);

cmd.Parameters.AddWithValue("@PageSize", objCustomerPaging.PageSize);

cmd.Parameters.Add("@RecordCount", SqlDbType.VarChar, 30);

cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

cmd.Parameters.Add("@pagingHtml", SqlDbType.VarChar, 2000);

cmd.Parameters["@pagingHtml"].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;

objCustomerPaging.RecordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);

objCustomerPaging.pagingHtml = Convert.ToString(cmd.Parameters["@pagingHtml"].Value);

int NoOfpage = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(objCustomerPaging.RecordCount) / objCustomerPaging.PageSize));

objCustomerPaging.NoOfpage = NoOfpage > 1 ? NoOfpage : 0;

objCustomerPaging.Url = "/Home/CustomerDetails";

model.CustomerPaging = objCustomerPaging;

}

return View(model);

}

----------------------

public class CustomerModel<T>

{

public string SearchTerm { get; set; }

public CustomerPaging CustomerPaging { get; set; }

public List<T> Result { get; set; }

}

public class CustomerPaging

{

public int PageIndex { get; set; }

public int PageSize { get; set; }

public int RecordCount { get; set; }

public int NoOfpage { get; set; }

public string Url { get; set; }

public string pagingHtml { 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; }

}

------------------------

@model MVCProj.Models.CustomerPaging

<tr>

@Html.Hidden("hfNoofpage", Model.NoOfpage)

int remainder = 0;

int pageStart = Math.DivRem(Model.PageIndex, 5, out remainder) * 5;

int pageEnd = ((pageStart + 5));

if (pageStart > 1)

{

<td>

<a id="btnpageprevious" href='javascript:void(0);' title="@((Model.PageIndex-1<=0? 1:Model.PageIndex-1))" class="btn btn-primary pagelink">Dot Button Previous</a>

</td>

}

if (Model.NoOfpage <= pageStart)

{

pageStart = Model.NoOfpage - 5;

pageEnd = Model.NoOfpage;

}

if (pageEnd > Model.NoOfpage)

{

pageEnd = Model.NoOfpage;

}

for (int i = pageStart; i <= pageEnd; i++)

{

if (i > 0)

{

if (i == Model.PageIndex)

{

<td>

</td>

}

else

{

<td>

</td>

}

if (Model.NoOfpage > pageStart)

{

<td>

<a id="btnpagenext" href='javascript:void(0);' title="@((Model.PageIndex+1>Model.NoOfpage? Model.NoOfpage:Model.PageIndex+1))" class="btn btn-primary pagelink">Dot Button End</a>

</td>

}

</tr>

</table>

@Html.Hidden("hfCurrentPageIndex")

$(document).ready(function () {

$(".pagelink").click(function () {

var index = this.title;

document.getElementById("hfCurrentPageIndex").value = parseInt(index);

document.forms[0].submit();

});

</script>

Search This Blog

RealCode

paging

Comments

Post a Comment