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

Leave a Comment

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply