Asp.net Paging

Custom page of The DataGrid Control in Asp.net

Zheng Zuo 2004-10-28

Although it is convenient to use the page provided by the DataGrid, it is not efficient. Every time you need to read all the pages (the entire record set), only one page is loaded, this results in a waste of resources. A large number of records can reduce the efficiency. The following uses the custom paging function of the DataGrid to reduce resource usage and improve efficiency.

The key to implementation is to set the allowcustompaging attribute bit to true, and set the virtualitemcount attribute to the total number of records for paging. The main code at the front end is as follows:

<Form ID = "form1" method = "Post" runat = "server">

<Table id = "Table1" style = "font-size: 9pt" cellspacing = "1" cellpadding = "1" width = "450" align = "center"

Border = "1">

<Tr>

<TD>

<Asp: DataGrid id = "datagrid1" runat = "server" width = "100%" allowpaging = "true" allowcustompaging = "true">

<Pagerstyle font-size = "9pt" mode = "numericpages"> </pagerstyle>

</ASP: DataGrid> </TD>

</Tr>

</Table>

</Form>

The data source used here is also assumed to be the northwind MERs table.

The following is the Stored Procedure for accessing a single page. There are many implementation methods, but this is the most common,

Create procedure [getcustomersdatapage]

@ Pageindex int,

@ Pagesize int,

@ Recordcount int out,

@ Pagecount int out

As

Select @ recordcount = count (*) from MERs

Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize)

Declare @ sqlstr nvarchar (1000)

If @ pageindex = 0 or @ pagecount <= 1

Set @ sqlstr = n' select top' + STR (@ pagesize) +

'Mermerid, companyName, address, phone from MERs order by mermerid DESC'

Else if @ pageindex = @ pagecount-1

Set @ sqlstr = n' select * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) +

'Mermerid, companyName, address, phone from MERs order by customerid ASC) temptable order by mermerid DESC'

Else

Set @ sqlstr = n' select top '+ STR (@ pagesize) +' * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) +

'Mermerid, companyName, address, phone from MERs order by customerid ASC) temptable order by mermerid DESC'

Exec (@ sqlstr)

 

Go

The output parameters of the stored procedure are used to obtain the number of records and the number of pages.

Obtain the data source. Here, a dataset is returned.

First, a data member is defined,

Private int pagecount; // Number of pages

Private int recordcount; // number of records

// Obtain single-page data

Private Static dataset getcustomersdata (INT pageindex, int pagesize, ref int recordcount, ref int pagecount)

{

String connstring = configurationsettings. receivettings ["connstring"];

Sqlconnection conn = new sqlconnection (connstring );

Sqlcommand comm = new sqlcommand ("getcustomersdatapage", Conn );

 

Comm. Parameters. Add (New sqlparameter ("@ pageindex", sqldbtype. INT ));

Comm. Parameters [0]. value = pageindex;

 

Comm. Parameters. Add (New sqlparameter ("@ pagesize", sqldbtype. INT ));

Comm. Parameters [1]. value = pagesize;

 

Comm. Parameters. Add (New sqlparameter ("@ recordcount", sqldbtype. INT ));

Comm. Parameters [2]. Direction = parameterdirection. output;

 

Comm. Parameters. Add (New sqlparameter ("@ pagecount", sqldbtype. INT ));

Comm. Parameters [3]. Direction = parameterdirection. output;

 

Comm. commandtype = commandtype. storedprocedure;

Sqldataadapter dataadapter = new sqldataadapter (Comm );

Dataset DS = new dataset ();

Dataadapter. Fill (DS );

 

Recordcount = (INT) Comm. Parameters [2]. value;

Pagecount = (INT) Comm. Parameters [3]. value;

 

Return Ds;

}

// Bind data to the DataGrid and refresh the total number of data records

Private void datagriddatabind ()

{

Dataset DS = getcustomersdata (pageindex, pagesize, ref recordcount, ref pagecount );

This. datagrid1.virtualitemcount = recordcount;

This. datagrid1.datasource = Ds;

This. datagrid1.databind ();

}

Below are several variable attributes of the page

Public int pagecount

{

Get {return this. datagrid1.pagecount ;}

}

 

Public int pagesize

{

Get {return this. datagrid1.pagesize ;}

}

 

Public int pageindex

{

Get {return this. datagrid1.currentpageindex ;}

Set {This. datagrid1.currentpageindex = value ;}

}

 

Public int recordcount

{

Get {return recordcount ;}

}

Register a DataGrid paging event

// Handle paging events

Private void datagrid1_pageindexchanged (Object source, system. Web. UI. webcontrols. datagridpagechangedeventargs E)

{

DataGrid DG = (DataGrid) source;

DG. currentpageindex = E. newpageindex;

Datagriddatabind ();

}

It is best to determine whether the current page is loaded for the first time to prevent repeated loading of data twice,

Private void page_load (Object sender, system. eventargs E)

{

If (! Page. ispostback)

{

Datagriddatabind ();

}

}

The display page is as follows: