Simple stored procedures implement pagination in Asp.net

We haveProgramQuery data is required. When the amount of information is large, it takes a lot of time to retrieve information and display the page.

Time leads to low efficiency!
When retrieving information, we only retrieve the data we currently need, that is, we use paging to improve processing efficiency!
When browsing the Web page, we found that many websites and forums use the pagination function provided by the gridview in Asp.net to process data pagination. Looks very simple

Single. Essentially, when we start pagination in the gridview, the data source associated with the data source control will still load all the information. Therefore

The paging function in the gridview is very inefficient in the case of a large amount of data!
Here I will talk about using stored procedures to implement the paging function.
Here I will show my tables
Create Table New (
Num identity (1, 1) not null,
Sort varchar (64) not null,
Title varchar (128) not null,
Author varchar (64) not null,
Matter text not null
)
Go

The stored procedure is as follows:
Create procedure pro_new
@ Pageindex int, // current page number displayed
@ Pagesize int // display size per page

As

/* Based on SQL Server 2005 */

Select num, title, serialnumber from
(Select title, num, row_number () over (order by num ASC) as serialnumber from new) as t
Where T. serialnumber> (@ PageIndex-1) * @ pagesize) and T. serialnumber <= (@ pageindex * @ pagesize)

Go

The defined serialnumber is the sorted number, because when num is set to auto-increment, there may be vacancies after the record is deleted!
Note that the first page in the database is actually 0th pages.

Public partial class _ default: system. Web. UI. Page
{
Private newsbiz biz = new newsbiz (); // call the business logic layer method here

Public int pagesize = 10; // The data displayed on the home page is 10 records.
Private Static readonly string connectionstrings =

Configurationmanager. connectionstrings ["newsdata"]. connectionstring; // connect to Web. config

Protected void page_load (Object sender, eventargs E)
{

Int pagecount = convert. toint32 (CMD. executescalar (); // total records

Int page = convert. toint32 (request. querystring. Get ("ID"); // obtain the page number by passing the value

If (page = 0)
{
Page = 1;
}

Float temp = (float) pagecount/pagesize; // Number of pages

If (page> = convert. toint32 (math. ceiling (temp)
{< br> page = convert. toint32 (math. ceiling (temp);
}

// You can bind a data source to be read, you can also customize the embedded HTML Code
ilist News = biz. searchexec (page, pagesize);
gridview1.datasource = News;
gridview1.databind ();
// string html = NULL;
// For (INT I = 0; I // {

// Html + = "<Div align = \" center \ "> ";
// Html + = "<a href = \" view. aspx? Id = "+ news [I]. Num +" \ "target = \" _ blank> \"";
// Html + = "</a> ";
// Html + = News [I]. title;
// Html + = "</div> ";

 

//}
// Showtd. innerhtml = HTML;

If (! Ispostback) // I personally think this is very important, because for the Asp.net page (not the page number mentioned above, yes

Page class) There is a lifecycle. Every time a request is sent to the server, the page will be destroyed and re-Load
{
Pagenowtextbox. Text = page. tostring ();
}

}

Other codes such as previous pages and next pages will not be listed! The idea of paging is easy to implement.
Maybe my method is not a good one! However, it is more efficient than pagination in the gridview! Explore other paging methods if you have time

!