Paging in ASP DOT Net With MS SQL Database
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
-----------------------------
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="projctindotnet._Default" %>
<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
<asp:GridView Width="500" runat="server" ID="grdCustomPagging">
</asp:GridView>
<asp:DataList CellPadding="5" RepeatDirection="Horizontal" runat="server" ID="dlPager"
onitemcommand="dlPager_ItemCommand">
<ItemTemplate>
<asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>' CommandArgument='<%#Eval("Value") %>'
CommandName="PageNo"></asp:LinkButton>
</ItemTemplate>
</asp:DataList>
</asp:Content>
----------------------------
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace projctindotnet
{
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindGrid(1);
}
}
public void bindGrid(int currentPage)
{
int pageSize = 10;
int _TotalRowCount = 0;
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", "");
cmd.Parameters.AddWithValue("@PageIndex", currentPage);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.VarChar, 30);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
_TotalRowCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
grdCustomPagging.DataSource = ds;
grdCustomPagging.DataBind();
dlPager.DataSource =generatePager(_TotalRowCount, pageSize, currentPage);
dlPager.DataBind();
}
}
}
public List<ListItem> generatePager(int totalRowCount, int pageSize, int currentPage)
{
int totalLinkInPage = 5;
int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);
int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);
if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
{
lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);
}
List<ListItem> pageLinkContainer = new List<ListItem>();
if (startPageLink != 1)
pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
for (int i = startPageLink; i <= lastPageLink; i++)
{
pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));
}
if (lastPageLink != totalPageCount)
pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));
return pageLinkContainer;
}
protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "PageNo")
{
bindGrid(Convert.ToInt32(e.CommandArgument));
}
}
}
}
Comments
Post a Comment