--Create table
CREATE TABLE [dbo].[Customer](
[ContactName] [varchar](250) NULL,
[City] [varchar](250) NULL,
[PostalCode] [varchar](250) NULL,
[Country] [varchar](250) NULL,
[Phone] [varchar](250) NULL,
[Fax] [varchar](250) NULL,
[CustomerId] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
--Create procedure
create proc GetCustomersPageWise(@PageIndex int,@PageSize int,@PageCount int out)
as
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER( ORDER BY [CustomerID] ASC )AS RowNumber,
ContactName,City,PostalCode,Country,Phone,Fax,CustomerId INTO #Results FROM Customer
SELECT @PageCount = COUNT(*) FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
--Filling value in table
declare @max int ,@min int
set @max=500
set @min=1
while(@min<@max)
begin
insert into Customer( ContactName,City,PostalCode,Country,Phone,Fax)values(@min,@min,@min,@min,@min,@min)
set @min=@min+1
end
select * from Customer
------------------
//Connection String in Web.config
<connectionStrings>
<add name="constr" connectionString="Data Source=.;Initial Catalog=Test_DB;integrated security=true"/>
</connectionStrings>
---------------------------
---------------------
//Asp.net page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="custompaging.aspx.cs" Inherits="custompaging" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
<asp:ListItem Text="10" Value="10" />
<asp:ListItem Text="25" Value="25" />
<asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
<asp:BoundField HeaderText="ContactName" DataField="ContactName" />
<asp:BoundField HeaderText="CompanyName" DataField="Postalcode" />
</Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
//Code Behind page in asp.net C#
using System;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Configuration;
public partial class custompaging : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GetCustomersPageWise(1);
}
private void GetCustomersPageWise(int pageIndex)
{
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("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4);
cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
con.Open();
IDataReader idr = cmd.ExecuteReader();
GridView1.DataSource = idr;
GridView1.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@PageCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem("First", "1", currentPage > 1));
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
protected void PageSize_Changed(object sender, EventArgs e)
{
this.GetCustomersPageWise(1);
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetCustomersPageWise(pageIndex);
}
}
No comments:
Post a Comment