Custom Paging in ASP.NET using DataReader and Repeater

Download codeproject - 6.15 KB

Introduction

Paging is done often in database application for showing large result sets. There is default paging available using dataset in ASP.NET. But it cost per formation and memory. So for efficient paging we have to use custom paging option in ASP.NET.In ASP.NET paging can not be done using DataReader, but in by solution i don't want to use DataSet for paging.So i decided paging on SQL Server side by using ROW_NUMBER() (new function introduce in SQL server 2005).

Background

I search through google for find such paging example, but I don’t find any custom paging example(Specially search and paging example)suitable in my requirement, I want paging for my search result set using DataReader and Repeater control.So after getting the solution of my problem i want to share this on code project

On SQL Server Side

For that purpose I start from SQL Server 2005.I write SP for getting result set by using paging. By using a new function provided by SQL Server 2005 ROW_NUMBER() you can get result set with row number.This row number can be used for paging on database side.

Create PROCEDURE [dbo].[getcust]
    @PageIndex INT, 
    @PageSize INT,
    @searchkey varchar(255),
    @TotalRecords int OUTPUT
  AS
if exists(select * FROM tbcustomer  where cust_firstname= @searchkey)
BEGIN 
WITH customer AS ( 
SELECT  ROW_NUMBER() OVER (ORDER BY cust_firstname ASC)as row,cust_id, cust_firstname,address,phone FROM tbcustomer where cust_firstname= @searchkey)
SELECT cust_id,cust_firstname,address,phone FROM customer 
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
select @TotalRecords =count(*)  FROM tbcustomer where cust_firstname= @searchkey
END

Using the code

Now I write a function for getting the result set.As we can not achieve paging using DataReader so I put result set in DataTable and bind Repeater with this DataTable.

 
public static DataTable searchfromTables(string keyword, int currentpage,int pagesize,string showSP)

{
DataTable dtClients = new DataTable();
DataRow drClient;
dtClients.Columns.Add("cust_id",System.Type.GetType("System.Int32"));

dtClients.Columns.Add("cust_firstname",System.Type.GetType("System.String"));

dtClients.Columns.Add("address",System.Type.GetType("System.String"));

dtClients.Columns.Add("phone", System.Type.GetType("System.String"));

//for result set 

string sql = showSP;

SqlParameter[] parms = 

{ new SqlParameter("@searchkey", SqlDbType.VarChar, 256), 

new SqlParameter("@PageIndex", SqlDbType.Int), 

new SqlParameter("@PageSize", SqlDbType.Int),

new SqlParameter ("@TotalRecords",SqlDbType.Int)

};

parms[0].Value = keyword;

parms[0].Direction = ParameterDirection.Input;

parms[1].Value = currentpage;

parms[1].Direction = ParameterDirection.Input;

parms[2].Direction = ParameterDirection.Input;

parms[2].Value = pagesize;

parms[3].Direction = ParameterDirection.Output;

SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(connectionstring);

try

{

PrepareCommand(cmd, conn,CommandType.StoredProcedure, sql, parms);

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

if (rdr.HasRows == false)

{
totalCount = 0;

}
else

{
totalCount = 1;
//put all the result set into DataTable 
while (rdr.Read())

{
drClient = dtClients.NewRow();

drClient["cust_id"] = rdr["cust_id"];

drClient["cust_firstname"] = rdr["cust_firstname"];

drClient["address"] = rdr["address"];

drClient["phone"] = rdr["phone"];

dtClients.Rows.Add(drClient);

}

}
//reder is closed here to get total number of record for your search
rdr.Close();

// conn.Close();

if (totalCount != 0)

{
totalCount = Convert.ToInt32(cmd.Parameters["@TotalRecords"].Value);

}

cmd.Parameters.Clear();

}

catch

{
conn.Close();

throw;

}

return dtClients;

}

On first time page load do not show paging div.

 
if (!Page.IsPostBack)

{//this code for managing paging for first time page load
divpaging.Visible = false;
// 
currentpage = 1;

lbtnNext.Enabled = false;

lbtnPrevious.Enabled = false;

lblCurrentPage.Text = "";

lblTotalPages.Text = "";

}

This code is written on search button for find your search.

protected void btnsearch_Click(object sender, EventArgs e)

{
divpaging.Visible = true;

dataacccess.totalCount = 0;

keyword = (TextBox1.Text.Trim());

currentpage = 1;

lbtnPrevious.Enabled = false;

lbtnNext.Enabled = true;

bindcontrol(keyword, currentpage, pagesize,"getcust");

int totalrecords = dataacccess.totalCount;

}

This code for binding Repeater control from DataTable

 
protected void bindcontrol(string keyword, int currentpagevalue, int pagesize,string SP)

{

pagesize = Convert.ToInt32(ddpagesize.SelectedValue);

DataTable dr = dataacccess.searchfromTables(keyword, currentpage, pagesize, SP);

divpaging.Visible = true;

if (dataacccess.totalCount == 0)

{

Label1.Text = "No Record for your search for " + keyword;

lbtnNext.Enabled = false;

lbtnPrevious.Enabled = false;

lblCurrentPage.Text = "";

lblTotalPages.Text = "";

divpaging.Visible = false;

}

else

{
Label1.Text = "Total Record for " + keyword + " :" + dataacccess.totalCount;
int totalpages;
lblCurrentPage.Text = currentpage.ToString();
//find no of page in the result set
if ((dataacccess.totalCount % pagesize) == 0)

{
totalpages = dataacccess.totalCount / pagesize;

}

else

{
totalpages = (dataacccess.totalCount / pagesize) + 1;

}

lblTotalPages.Text = totalpages.ToString();

if (totalpages == 1)

{
divpaging.Visible = false;

}

else

{ divpaging.Visible = true; 

}

}

RepeSearch.DataSource = dr;

RepeSearch.DataBind();

}

For navigate to next page. On click on next or privious button current page value is chnaged and new result set is return from database and bind to epeater control.

protected void NextPage(object sender, EventArgs e)

{

int totalpage = Convert.ToInt32(lblTotalPages.Text);

if (totalpage > currentpage)

{

lbtnPrevious.Enabled = true;

currentpage = currentpage + 1;

bindcontrol(keyword, currentpage, pagesize, "getcust");

if (currentpage == totalpage)

{

lbtnNext.Enabled = false;

lbtnPrevious.Enabled = true;

}

}

else

{

lbtnNext.Enabled = false;

lbtnPrevious.Enabled = true;

}

}

Point of Interest

There are a lot of articles on custom paging in ASP.NET but there is no complete article which has custom paging implementation using a search option. This is my first article on code project, so I welcome comments and suggestion for this article.