True/false pagination in ASP. NET

When there is a large amount of data in the database, the previous DataGrid often seems powerless. In this case, it is the turn of paging to show its skills. Paging refers to dividing the data in the database into several pages and displaying them independently. We usually divide pages into two types: real pages and false pages.

False paging indicates that all data is retrieved from the database and displayed to the user by page.

1. Fake paging front-end interface.

2. Set the allowpaging attribute to true to allow pagination. Set the pagesize to the number of data entries displayed on each page.

3. Running interface:

The process of pagination is as follows:

1. Obtain aspnetpager. dll from the website.

2. Add this component to the toolbox. For details, see add Web Control in my blog.

3. Add controls to the foreground page.

4. Background code:

Protected void page_load (Object sender, eventargs e) {asppage. recordcount = 100 ;} /// <summary> /// event executed when the page number is changed /// </Summary> /// <Param name = "sender"> </param> // /<Param name = "E"> </param> protected void asp_pagechanged (Object sender, eventargs e) {response. write ("start and end" + asppage. startrecordindex + "<br>" + "ended at" + asppage. endrecordindex); // binddata () of the start and end data rows; // bind the DataGrid data} // <summary> /// Bind data /// </Summary> private void binddata () {// bind the dgpage of the DataGrid data. datasource = DT (); dgpage. databind ();} /// <summary> /// generate a dataset based on the current page number /// </Summary> /// <returns> </returns> private datatable DT () {int pagestart = asppage. startrecordindex; // get the current starting data row int pageend = asppage. endrecordindex; // obtain sqlconnection conn = new sqlconnection ("Server = .; database = beidaqingniao; uid = sa; Pwd = 123456 ;");// Create a database connection string sqltxt = "select * from page where ID between @ start and @ end"; // define the SQL statement sqlcommand sqlcmd = new sqlcommand (sqltxt, Conn ); // database creation command sqlparameter [] paras = new sqlparameter [] {New sqlparameter ("@ start", pagestart), new sqlparameter ("@ end", pageend )}; // define the parameter array sqlcmd. parameters. addrange (paras); // Add parameters to the command dataset DS = new dataset (); // define dataset sqldataadapter da = new sqldataadapter (SQ Lcmd); // create a data adapter try {Conn. open (); // open the connection da. fill (DS); // fill in DS data return Ds. tables [0]; // returned dataset} catch (exception) {Throw new exception ("retrieving data from the database failed! "); // The error message" "failed to retrieve data from the database! ""} Finally {conn. Close (); // close the connection sqlcmd. Dispose (); // pin the command }}

5. Running interface:

For ease of calling, you can write a stored procedure (from niuyun ):

-- ===================================================== ====== -- Author: niu Yun -- create Date: -- Description: Paging, using row_number () -- ===================================================== ====== create procedure [DBO]. [proc_showpage] @ tblname varchar (255), -- table name @ strgetfields varchar (1000) = '*', -- columns to be returned, default * @ strorder varchar (255) = '', -- Name of the sorted field, required @ strordertype varchar (10) = 'asc ', -- sorting method, ASC @ pagesize Int = 10 by default, -- page size, Mo 10 @ pageindex Int = 1, -- page number. Default Value: 1 @ strwhere varchar (1500) = ''-- Query condition (Note: Do not add where) asdeclare @ strsql varchar (5000) if @ strwhere! = ''Set @ strwhere = 'where' + @ strwhereset @ strsql = 'select * from ('+ 'select row_number () over (order by '+ @ strorder + ''+ @ strordertype +') as pos, '+ @ strgetfields + ''+ 'from' + @ tblname +'' + @ strwhere +') as SP where POS between '+ STR (@ PageIndex-1) * @ pagesize + 1) + 'and' + STR (@ pageindex * @ pagesize) exec (@ strsql)

True/false paging comparison. The advantage of false paging is that the code is simple. If attributes are set, they can be implemented without writing a method. This method is suitable for a small number of data rows (several hundred rows). The advantage of real paging is that no matter how much data, the time for retrieving a page is basically the same, which is applicable to situations with massive data (such as Baidu search results ).