Stored Procedure for paging

The custom page of The DataGrid is an important function of the DataGrid. The custom page of The DataGrid is mainly used for large data sources. Because the data source is large (with many records), loading takes a long time, slow response, and server resources are consumed. In addition, all data is reloaded every time a page is displayed. If we use custom pages, only one page of records will be loaded at a time, that is, only the records we want to display will be loaded. In this way, loading data is short, response is fast, and server resources are saved. During the project process, we often need to use custom functions. Today, let's talk about how to customize pages.
First, let's look at the principle of the DataGrid's custom paging. It mainly depends on two main attributes. The first one is the virtualitemcount attribute, which indicates the total number of records to be displayed in the DataGrid, it is used to generate Pager (paging Navigation Line). By combining the other two attributes pagesize and pagebuttoncount, The DataGrid will know the number of pages to be divided and the number of paging buttons to be displayed, it is not difficult to get the total number of pages to be displayed = (virtualitemcount + pagesize-1)/pagesize; to get the total number of pages <pagebuttoncount, display the total number of pages button; if the total number of pages> pagebuttoncount, the pagebuttoncount buttons are displayed. Of course, only the virtualitemcount % pagesize buttons (total number of records divided by the remainder of records displayed on each page) are displayed on the last page. Another important attribute is datasource. An important feature of custom paging is to display all records in the data source. If there is one record in the data source, a record is displayed; if the data source contains 10 thousand records, it will display 10 thousand records, and your machine may be slow :). Therefore, the most important thing to customize is how to set or obtain the data source.
Next, let's talk about the main steps of custom paging:
1. Set the virtualitemcount attribute of the DataGrid;
2. Obtain the data source of the DataGrid );
3. Bind data to the DataGrid;
4. Set the page number of the new page (DataGrid. currentpageindex attribute ).
Repeat steps 2, 3 and 4 above.
The following is an example to illustrate the udf process of the DataGrid.
The example requires that the machine is equipped with ms SQL Server 7.0 or 2000. Of course, you must be able to run the Asp.net page (nonsense ).
Let's first write a general Stored Procedure for paging, returning the record set to be displayed on a page, and an output parameter-the total number of records, but this stored procedure is flawed, for example, it can only be used for single-table queries and must have conditional statements.
Create procedure up_custompage @ vc_order_column_name varchar (100), @ vc_select_column_list varchar (100 ),
@ Vc_select_table_list varchar (100), @ vc_condition varchar (100), @ page_size int, @ current_page int, @ total1 int output
/*
(
@ Vc_order_column_name: name of the column to be sorted in the Table. Only one column can be ordered, and the column must be in the output list;
@ Vc_select_column_list: returns the list of column names;
@ Vc_select_table_list: name of the table to be queried;
@ Vc_condition: string of the query condition. A Query column must exist. Otherwise, an exception is thrown;
@ Page_size: displays the number of records on each page;
@ Current_page: the page number of the current page;
@ Total1: Total number of all qualified records.
SQL statement = select top the number of records displayed per page * from (select top the number of records displayed per page returns the column name list of the columns from the table name to be queried where the column to be sorted column name in (select Top each page displays the number of records x current page number the column name to be sorted from the table name to be queried where query condition order by the column name to be sorted) order by: Name of the column to be sorted (DESC) as temp1 order by: Name of the column to be sorted
)
*/
As
-- Declare the variables to be used. @ temp1 is a normal paging statement string, @ temp2 is the paging statement string on the last page, and @ page_total has several pages, @ last_page
-- The page number of the last page
Declare @ temp1 varchar (500), @ temp2 nvarchar (500), @ page_total int, @ last_page int
-- Construct a search statement to obtain the total number of pages
Set @ temp2 = n' select @ total2 = count (*) from '+ @ vc_select_table_list + 'where' + @ vc_condition
-- Execute the search statement to obtain the total number of records
Exec sp_executesql @ temp2, n' @ total2 int output', @ total1 output

/* Construct a paging query statement. The basic principle is to retrieve the @ page_size * @ current_page record first, this is equivalent to extracting the records on the current page and all pages before the current page, and then taking out the records to be displayed on the current page, that is, taking the previous @ page_size records after reverse sorting; finally, sort the results in reverse order (because the previous order was reversed once, and now sort it again, which is exactly the order we want). Finally, execute the command and return the result set.
*/
If @ total1> 0
Begin
Set @ page_total = (@ total1 + @ page_size-1)/@ page_size
-- If the current page is not the last page
If @ current_page <@ page_total
Set @ temp1 = 'select top '+ Cast (@ page_size as varchar (4) +' * from
(Select top '+ Cast (@ page_size as varchar (4) + ''+ @ vc_select_column_list + 'from' + @ vc_select_table_list + 'where' + @ vc_order_column_name
+ 'In (select top '+ Cast (@ page_size * @ current_page as varchar (10) + ''+ @ vc_order_column_name + 'from' +
@ Vc_select_table_list + 'where' + @ vc_condition + 'ORDER BY' +
@ Vc_order_column_name + ') order by' + @ vc_order_column_name + 'desc) as temp1 order by '+ @ vc_order_column_name
Else
-- The last page only returns the last few records after the page, that is, @ total1 % @ page_size records.
Begin
Set @ last_page = @ total1 % @ page_size
Set @ temp1 = 'select top '+ Cast (@ last_page as varchar (4) +' * from
(Select top '+ Cast (@ last_page as varchar (4) + ''+ @ vc_select_column_list + 'from' + @ vc_select_table_list + 'where' + @ vc_order_column_name
+ 'In (select top '+ Cast (@ total1 as varchar (10) + ''+ @ vc_order_column_name + 'from' +
@ Vc_select_table_list + 'where' + @ vc_condition + 'ORDER BY' +
@ Vc_order_column_name + ') order by' + @ vc_order_column_name + 'desc) as temp1 order by '+ @ vc_order_column_name
End
-- Perform search
Exec (@ temp1)
End
Else
Return

-------------------------------------------------------------------------

Then, on the create An ASPX page, the Code is as follows:
<% @ Page Language = "C #" codebehind = "custompage. aspx. cs" autoeventwireup = "false" inherits = "cyc_test.custompage" %>
<! Doctype HTML public "-// W3C // dtd html 4.0 transitional // en">
<HTML>
<Head>
<Title> custompage </title>
<Meta content = "Microsoft Visual maxcompute 7.0" name = "generator">
<Meta content = "C #" name = "code_language">
<Meta content = "JavaScript" name = "vs_defaultclientscript">
<Meta content = "http://schemas.microsoft.com/intellisense/ie5" name = "vs_targetschema">
<Style> A {behavior: URL (Mouseover. HTC )}
HR {color: black; Height: 2px}
. Stdtext {font-weight: bold; font-size: 9pt; font-family: verdana}
. Stdtextbox {border-Right: Black 1px solid; border-top: Black 1px solid; font-size: 9pt; filter: progid: DXImageTransform. microsoft. dropshadow (offx = 2, offy = 2, color = 'Gray ', positive = 'true'); border-left: Black 1px solid; border-bottom: Black 1px solid; font-family: verdana}
. Shadow {filter: progid: DXImageTransform. Microsoft. dropshadow (offx = 2, offy = 2, color = 'Gray ', positive = 'true ')}
</Style>
</Head>
<Body style = "font-family: Arial" bgcolor = "Ivory" ms_positioning = "gridlayout">
<Form ID = "custompage" method = "Post" runat = "server">
<H2> Project Summary-DataGrid custom Paging
</H2>
<Asp: Label id = "label1" runat = "server" font-bold = "true" cssclass = "stdtext"> current path: </ASP: Label> <asp: label id = "lblurl" style = "color: Blue" runat = "server" cssclass = "stdtext"> </ASP: Label>
<! -- Query --> <br/>
<Asp: Label id = "label2" runat = "server" cssclass = "stdtext" text = "query statement:"> </ASP: Label>
<Asp: textbox id = "textbox1" runat = "server" cssclass = "stdtextbox" text = "select employeeid, firstname, lastname, title from employees where employeeid> 0 order by employeeid "width =" 765px "enabled =" false "> </ASP: textbox>
<HR>
<! -- Show the information -->
<Asp: dataGrid id = "Grid" runat = "server" onpageindexchanged = "pageindexchanged" allowcustompaging = "true" allowpaging = "true" pagesize = "5" borderwidth = "1" bordercolor = "Black "borderstyle =" solid "backcolor =" white "cssclass =" shadow "gridlines =" vertical "cellspacing =" 0 "cellpadding =" 2 "font-names =" verdana "font- size = "smaller">
<Pagerstyle font-bold = "true" mode = "numericpages" backcolor = "palegreen"/>
<Alternatingitemstyle backcolor = "# eeeeee"/>
<Itemstyle backcolor = "white"/>
<Headerstyle font-bold = "true" forecolor = "white" backcolor = "Navy"/>
</ASP: DataGrid> </form>
</Body>
</Html>

Then write the background source code:
Using system;
Using system. collections;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. Web;
Using system. Web. sessionstate;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. htmlcontrols;
Using system. Data. sqlclient;

Namespace cyc_test
{
/// <Summary>
/// Abstract description of custompage.
/// </Summary>
Public class custompage: system. Web. UI. Page
{
Protected system. Web. UI. webcontrols. Label label1;
Protected system. Web. UI. webcontrols. Label lblurl;
Protected system. Web. UI. webcontrols. Label label2;
Protected system. Web. UI. webcontrols. textbox textbox1;
Protected system. Web. UI. webcontrols. DataGrid grid;
 
Private void page_load (Object sender, system. eventargs E)
{
// Initialize the page when the page is loaded for the first time
If (! Page. ispostback)
{
Lblurl. Text = request. url + "<HR> ";
Createdatasource (1 );
}
}

// Function for binding the DataGrid
Protected void createdatasource (INT current_page)
{
String str_table_name, str_column_list, str_order_column, str_condition;
// Name of the queried table
Str_table_name = "employees ";
// List of returned column names
Str_column_list = "employeeid, firstname, lastname, title ";
// Column name of the sort column
Str_order_column = "employeeid ";
// Query the table parts
Str_condition = "employeeid> 0 ";
String strconn = "database = northwind; server = localhost; uid = sa; Pwd = ;";
Sqlconnection conn = new sqlconnection (strconn );
// Declare the sqlcommand for executing the Stored Procedure
Sqlcommand scd_sel = new sqlcommand ("up_custompage", Conn );
Scd_sel.commandtype = commandtype. storedprocedure;
// Assign values to stored procedure parameters
Sqlparameter sp_temp;
Sp_temp = scd_sel.parameters.add ("@ vc_order_column_name", sqldbtype. varchar, 100 );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = str_order_column;
Sp_temp = scd_sel.parameters.add ("@ vc_select_column_list", sqldbtype. varchar, 100 );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = str_column_list;
Sp_temp = scd_sel.parameters.add ("@ vc_select_table_list", sqldbtype. varchar, 100 );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = str_table_name;
Sp_temp = scd_sel.parameters.add ("@ vc_condition", sqldbtype. varchar, 100 );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = str_condition;
Sp_temp = scd_sel.parameters.add ("@ page_size", sqldbtype. INT );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = grid. pagesize;
Sp_temp = scd_sel.parameters.add ("@ current_page", sqldbtype. INT );
Sp_temp.direction = parameterdirection. input;
Sp_temp.value = current_page;
Sp_temp = scd_sel.parameters.add ("@ total1", sqldbtype. INT );
Sp_temp.direction = parameterdirection. output;

// Execute the Stored Procedure
Sqldataadapter SDA = new sqldataadapter ();
SDA. selectcommand = scd_sel;
If (conn. State = connectionstate. Closed)
Conn. open ();
Dataset DS = new dataset ();
SDA. Fill (DS, "tb1 ");
Conn. Close ();
// Set the virtualitemcount attribute
Grid. virtualitemcount = (INT) scd_sel.parameters ["@ total1"]. value;
// Bind the data source
Grid. datasource = Ds. Tables ["tb1"]. defaultview;
Grid. databind ();
}

# Region web form designer generated code
Override protected void oninit (eventargs E)
{
//
// Codegen: This call is required by the ASP. NET web form designer.
//
Initializecomponent ();
Base. oninit (E );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. Grid. itemcreated + = new system. Web. UI. webcontrols. datagriditemeventhandler (this. grid_itemcreated );
This. Grid. pageindexchanged + = new system. Web. UI. webcontrols. datagridpagechangedeventhandler (this. pageindexchanged );
This. Load + = new system. eventhandler (this. page_load );

}
# Endregion
// Itemcreated event of the DataGrid, used to customize the paging Navigation Line
Private void grid_itemcreated (Object sender, system. Web. UI. webcontrols. datagriditemeventargs E)
{

Listitemtype elemtype = E. Item. itemtype;
// Customize the pagination Navigation Line. The style is [1] [2] Page 3rd [4]
If (elemtype = listitemtype. pager)
{

Tablecell pager = (tablecell) E. Item. controls [0];

For (INT I = 0; I <pager. Controls. Count; I ++ = 2)
{
Object o = pager. controls [I];
If (O is linkbutton)
{
Linkbutton H = (linkbutton) O;
H. Text = "[" + H. Text + "]";
}
Else
{
Label L = (Label) O;
L. Text = "" + L. Text + "page ";
}
}
}
}
// Page selected (paging) Events
Public void pageindexchanged (Object source, system. Web. UI. webcontrols. datagridpagechangedeventargs E)
{
Grid. currentpageindex = E. newpageindex;
// The page number starts from scratch, so add one
Createdatasource (grid. currentpageindex + 1 );
}
}
}

To run the preceding example, first create a stored procedure, then write the code on the page, and finally the source code. The example references the paging idea in "build a web solution-apply ASP. NET and ADO. Net. It passes local debugging. You only need to understand the idea of paging stored procedures. If you have a better solution, please come up with it. Maybe you are better!
This article has been written in a rush and may not be clear in some places. You can ask questions and discuss it. Hope to help you.