Follow these 3 steps:
- Create a page named as paging.aspx
- Create Table named as ProductDetails
- Create a storedprocedure named as SQLPaging
paging.aspx :
<div id=”divData” runat=”server”>
</div>
<div>
<asp:linkbutton font-bold=”True” font-underline=”False” id=”lnkBtnPrev” onclick=”lnkBtnPrev_Click” runat=”server”> << Prev </asp:linkbutton>
<asp:linkbutton font-bold=”True” font-underline=”False” id=”lnkBtnNext” onclick=”lnkBtnNext_Click” runat=”server”>Next >> </asp:linkbutton>
<input id=”txtHidden” runat=”server” style=”width: 28px;” type=”hidden” value=”1″ />
</div>
paging.aspx.cs :
public void bindrepeater()
{
try
{
var itemsinPage = 6; //No of items to show per page
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“conTest”].ToString());
SqlCommand com = new SqlCommand(“SQLPaging”, con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(“@PAGENO”, SqlDbType.Int).Value = Convert.ToInt16(txtHidden.Value);
com.Parameters.Add(“@PAGESIZE”, SqlDbType.Int).Value = 6;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = com;
DataSet ds = new DataSet();
da.Fill(ds);
divData.InnerHtml = “”;
foreach (DataRow dr in ds.Tables[0].Rows)
{
string strData = “<div><div><strong>Producr Details</strong></div>”;
strData += “<div>” + dr[“BrandName”].ToString() + “</div>”;
strData += “<div>” + dr[“ProductCategory “].ToString() + “</div>”;
strData += “<div>” + dr[“Address”].ToString() + “</div></div>”;
divData.InnerHtml += strData;
}
int items = ds.Tables[0].Rows.Count;
pageno(Convert.ToInt16(ds.Tables[1].Rows[0][0].ToString()),items);
}
catch(Exception ex)
{
Response.Write(“<br/><br/>there is an error in bindrepeater()<br/><br/>” + ex);
}
}
public void pageno(int totItems,int pageitems)
{
int pgCount;
if (totItems > 6)
{
pgCount = totItems / 6 + totItems % 6;
}
else
{
pgCount = 1;
}
if (pgCount > Convert.ToInt16(txtHidden.Value))
lnkBtnNext.Visible = true;
else
lnkBtnNext.Visible = false;
if ((Convert.ToInt16(txtHidden.Value)) > 1)
lnkBtnPrev.Visible = true;
else
lnkBtnPrev.Visible = false;
if(pageitems < 6)
lnkBtnNext.Visible = false;
}
protected void lnkBtnPrev_Click(object sender, EventArgs e)
{
txtHidden.Value = Convert.ToString(Convert.ToInt16(txtHidden.Value) – 1);
bindrepeater();
}
protected void lnkBtnNext_Click(object sender, EventArgs e)
{
txtHidden.Value = Convert.ToString(Convert.ToInt16(txtHidden.Value) + 1);
bindrepeater();
}
SQLPaging Stored Procedure:
CREATE PROCEDURE [Test].[SQLPaging]
(
@PAGENO int,
@PAGESIZE int
)
AS
BEGIN
CREATE TABLE #TEMP
(
[ID] INT IDENTITY PRIMARY KEY,
[Location] VARCHAR(200),
[Address] VARCHAR(200),
[City] VARCHAR(200),
[State] VARCHAR(200),
[PostalCode] VARCHAR(200)
)
SET NOCOUNT OFF
INSERT INTO #TEMP([Location],[Address],[City],[State],[PostalCode],)
SELECT [Location],[Address],[City],[State],[PostalCode] FROM tblTableName
DECLARE @FROM INT
DECLARE @TO INT
SET @TO = @PAGENO * @PAGESIZE
SET @FROM = @PAGENO * @PAGESIZE – @PAGESIZE
SELECT * FROM #TEMP WHERE [ID] > @FROM AND [ID] <= @TO
SELECT COUNT (*) FROM #TEMP
DROP TABLE #TEMP
SET NOCOUNT ON
END