Create procedure sp_page
@ TB varchar (50), -- table name
@ Col varchar (50), -- pagination by this column
@ Coltype int, -- @ Col column type, 0-number type, 1-character type, 2-Date and Time Type
@ Orderby bit, -- sort, 0-order, 1-inverted
@ Collist varchar (800), -- List of fields to be queried, * indicates all fields
@ Pagesize int, -- number of records per page
@ Page int, -- specify the page
@ Condition varchar (800), -- Query Condition
@ Pages int output -- total number of pages
As
/*
Function Description: queries records that meet the specified conditions in a specified table by Page Based on the specified column. The page can be sorted in reverse order.
Query can specify the page size, specify any page to query, specify the list of output fields, and return the total number of pages
Author: pbsql
Version 1.10
Last modified: 2004-11-29
*/
Declare @ SQL nvarchar (4000), @ where1 varchar (800), @ where2 varchar (800)
If @ condition is null or rtrim (@ condition) =''
Begin -- no query Conditions
Set @ where1 = 'where'
Set @ where2 =''
End
Else
Begin -- with query Conditions
Set @ where1 = 'where ('+ @ condition +') and '-- this condition is added if conditions exist.
Set @ where2 = 'where ('+ @ condition +') '-- this condition is added if no conditions exist.
End
Set @ SQL = 'select @ pages = ceiling (count (*) + 0.0)/'+ Cast (@ pagesize as varchar) +
') From' + @ TB + @ where2
Exec sp_executesql @ SQL, n' @ pages int output', @ pages output -- calculate the total number of pages
If @ orderby = 0
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
'From' + @ TB + @ where1 + @ Col + '> (select max (' + @ Col + ')' +
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + ') T) Order by' + @ col
Else
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
'From' + @ TB + @ where1 + @ Col + '<(select Min (' + @ Col + ')' +
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + 'desc) T) Order by' +
@ Col + 'desc'
If @ page = 1 -- first page
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist + 'from' + @ TB +
@ Where2 + 'ORDER BY' + @ Col + case @ orderby when 0 then' 'else' DESC 'end
Exec (@ SQL)
Go
For details, see:
Http://blog.csdn.net/pbsql/archive/2004/11/30/199657.aspx
Http://community.csdn.net/Expert/topic/3587/3587201.xml? Temp =. 4292414.
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.
Among the numerous articles on analysis performance, I am glad to turn to this http://www.codeproject.com/aspnet/PagingLarge.asp (thanks to a friend in the garden, the specific name has not remembered ), if you are interested in the paging storage process, you can check it out and write it in detail. If you are interested in the paging storage process, feel free to pf the seriousness and professionalism of your foreign counterparts.
At the beginning of the article mentioned above, we strongly promoted the use of the rowcoun T method,
However, the method provided in the original document does not support sorting non-unique fields.
Not applicable in most cases
The author mentioned at the end of the article that he has begun to consider using the cursor method.
However, I have a special liking for the rowcount method, so I improved this method in the original article.
The improved method has basically met my needs. Now it is released. You can download and try it out with some useful friends.
Modification record:
1) added support for sorting non-unique fields, but a PK field must be set (Note: as long as it is a unique field, it can be used as a PK field)
2) Add output parameters for the total number of records
3) modify several bugs in the original process
Note:
1) if the table name parameter is multi-table join, the table name must be specified in the sort column;
2) only single-field sorting is supported. If a friend asks why multi-field sorting is not allowed, this is indeed possible in theory, but it must be at the cost of a certain efficiency loss, in addition, the method will be too complex. If this is the case, you can write a separate paging storage process, which is simpler than the general method in terms of performance and complexity.
3) There are not many tests due to time. If there is a bug, please submit it and fix it immediately;
1if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [paging_rowcount] ') and objectproperty (ID, n' isprocedure') = 1)
2 drop procedure [DBO]. [paging_rowcount]
3go
4
5 set quoted_identifier on
6go
7 set ansi_nulls on
8go
9 ---------------------------------------------------------------
10 -- paging Stored Procedure (using rowcount) -- edit by siben
11 -- Summary:
12 -- retrieve the paging data of a table or table set
13 -- when multiple tables are connected, the sort column must specify the table name.
14 ---------------------------------------------------------------
15
16 create procedure paging_rowcount
17 (
18 @ tables varchar (1000 ),
19 @ PK varchar (100 ),
20 @ sort varchar (200) = NULL,
21 @ pagenumber Int = 1,
22 @ pagesize Int = 10,
23 @ fields varchar (1000) = '*',
24 @ filter varchar (1000) = NULL,
25 @ group varchar (1000) = NULL,
26 @ recordcount Int = 0 output
27)
28as
29
30/** // * default sorting */
31if @ sort is null or @ sort =''
32 set @ sort = @ PK
33
34/** // * Find the @ PK type */
35 declare @ sorttable varchar (100)
36 declare @ sortname varchar (100)
37 declare @ strsortcolumn varchar (200)
38 declare @ operator char (2)
39 declare @ Type varchar (100)
40 declare @ prec int
41
42/** // * Set sorting variables .*/
43if charindex ('desc', @ sort)> 0
44 begin
45 set @ strsortcolumn = Replace (@ sort, 'desc ','')
46 set @ operator = '<'
47 end
48 else
49 begin
50 if charindex ('asc ', @ sort)> 0
51 set @ strsortcolumn = Replace (@ sort, 'asc ','')
52 else
53 set @ strsortcolumn = @ sort
54
55 set @ operator = '>'
56 end
57
58
59if charindex ('.', @ strsortcolumn)> 0
60 begin
61 set @ sorttable = substring (@ strsortcolumn, 0, charindex ('.', @ strsortcolumn ))
62 set @ sortname = substring (@ strsortcolumn, charindex ('.', @ strsortcolumn) + 1, Len (@ strsortcolumn ))
63 end
64 else
65 begin
66 set @ sorttable = @ tables
67 set @ sortname = @ strsortcolumn
68 end
69
70 select @ type = T. Name, @ prec = C. prec
71 from sysobjects o
72 join syscolumns C on O. ID = C. ID
73 join policypes t on C. xusertype = T. xusertype
74 where O. Name = @ sorttable and C. Name = @ sortname
75
76if charindex ('Char ', @ type)> 0
77 set @ type = @ Type + '(' + Cast (@ prec as varchar) + ')'
78
79 declare @ strpagesize varchar (50)
80 declare @ strstartrow varchar (50)
81 declare @ strfilter varchar (1000)
82 declare @ strsimplefilter varchar (1000)
83 declare @ strgroup varchar (1000)
84
85/** // * default page number */
86if @ pagenumber <1
87 set @ pagenumber = 1
88
89/** // * Set paging variables .*/
90 set @ strpagesize = cast (@ pagesize as varchar (50 ))
91 set @ strstartrow = cast (@ pagenumber-1) * @ pagesize + 1) as varchar (50 ))
92
93/** // * Set filter & group variables .*/
94if @ filter is not null and @ filter! =''
95 begin
96 set @ strfilter = 'where' + @ filter +''
97 set @ strsimplefilter = 'and' + @ filter +''
98 end
99 else
100 begin
101 set @ strsimplefilter =''
102 set @ strfilter =''
103 end
Required if @ group is not null and @ group! =''
105 set @ strgroup = 'group by' + @ group +''
106 else
107 set @ strgroup =''
108
109/** // * Get rows count .*/
110 declare @ str_count_ SQL nvarchar (500)
111 set @ str_count_ SQL = 'select @ totalcount = count (*) from '+ @ tables + @ strfilter
112 exec sp_executesql @ str_count_ SQL, n' @ totalcount Int = 0 output', @ recordcount output
113
114/** // * execute dynamic query */
115if @ strsortcolumn = @ PK
116 begin
117 exec (
118'
119 declare @ sortcolumn '+ @ Type +'
120 set rowcount '+ @ strstartrow +'
121 select @ sortcolumn = '+ @ strsortcolumn + 'from' + @ tables + @ strfilter + ''+ @ strgroup + 'ORDER BY' + @ sort +'
122 set rowcount '+ @ strpagesize +'
123 select '+ @ fields + 'from' + @ tables + 'where' + @ strsortcolumn + @ operator +' = @ sortcolumn '+ @ strsimplefilter + ''+ @ strgroup +' order by '+ @ sort +'
124'
125)
126 end
127 else
128 begin
129 exec (
130'
131 declare @ sortcolumn '+ @ Type +'
132 declare @ sortnullvalue '+ @ Type +'
133 declare @ pkstartvalue int
134 set @ sortnullvalue = cast (''' as '+ @ Type + ')
135 set rowcount '+ @ strstartrow +'
136 select @ sortcolumn = isnull ('+ @ strsortcolumn +', @ sortnullvalue ), @ pkstartvalue = '+ @ PK + 'from' + @ tables + @ strfilter + ''+ @ strgroup + 'ORDER BY' + @ sort + ', '+ @ PK +' DESC
137 set rowcount '+ @ strpagesize +'
138 select '+ @ fields + 'from' + @ tables + 'where (isnull (' + @ strsortcolumn + ', @ sortnullvalue) '+ @ operator +' @ sortcolumn or (isnull ('+ @ strsortcolumn +', @ sortnullvalue) = @ sortcolumn and '+ @ PK +' <= @ pkstartvalue )) '+ @ strsimplefilter + ''+ @ strgroup + 'ORDER BY' + @ sort +', '+ @ PK +' DESC
139'
140)
141 end
142go
143 set quoted_identifier off
144go
145 set ansi_nulls on
146go
147
148
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
