Webform2.aspx page
<% @ Page Language = "C #" codebehind = "webform2.aspx. cs" autoeventwireup = "false" inherits = "Page. webform2" %>
<! Doctype HTML public "-// W3C // dtd html 4.0 transitional // en">
<HTML>
<Head>
<Title> webform2 </title>
<Meta name = "vs_showgrid" content = "false">
<Meta name = "generator" content = "Microsoft Visual Studio. NET 7.1">
<Meta name = "code_language" content = "C #">
<Meta name = "vs_defaultclientscript" content = "JavaScript">
<Meta name = "vs_targetschema" content = "http://schemas.microsoft.com/intellisense/ie5">
</Head>
<Body>
<Form ID = "form1" method = "Post" runat = "server">
<Aspatagrid id = "customerlist" runat = "server" autogeneratecolumns = "false">
<Columns>
<Aspoundcolumn datafield = "ID" headertext = "ID"> </aspoundcolumn>
<Aspoundcolumn datafield = "text" headertext = "text"> </aspoundcolumn>
</Columns>
<Pagerstyle horizontalalign = "right" mode = "numericpages"> </pagerstyle>
</Aspatagrid>
<Asp: linkbutton id = "linkbutton1" runat = "server" commandname = "up"> linkbutton </ASP: linkbutton>
<Asp: linkbutton id = "linkbutton2" runat = "server" commandname = "down"> linkbutton </ASP: linkbutton>
using system;
using system. collections;
using system. componentmodel;
using system. data;
using system. data. sqlclient;
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. configuration;
Namespace page
{
/// <Summary>
/// Summary of webform2.
/// </Summary>
Public class webform2: system. Web. UI. Page
{
Protected system. Web. UI. webcontrols. DataGrid customerlist;
Protected system. Web. UI. webcontrols. linkbutton linkbutton1;
Protected system. Web. UI. webcontrols. linkbutton linkbutton2;
Protected system. Web. UI. webcontrols. Label label1;
Private function P = new function ();
Private void page_load (Object sender, system. eventargs E)
{
// Place the user hereCodeTo initialize the page
Timespan starttime = datetime. Now. timeofday;
If (! Page. ispostback)
BIND ();
Label1.text = datetime. Now. timeofday. Subtract (starttime). totalmilliseconds. tostring ();
}
Private void BIND ()
{
If (viewstate ["pageindex"] = NULL)
Viewstate ["pageindex"] = 0;
Sqlconnection myconnection = new sqlconnection (configurationsettings. etettings ["DSN"]);
// String strkeyword = keyword. Text. Trim (). Replace ("\'","\'\'");
// String strsalesid = Sales. selecteditem. value;
Int recordcount = calcrecordcount ();
// Recordnumber. Text = recordcount. tostring ();
// Lblrecordnumber. Text = recordcount. tostring ();
String strexpress = "id <> 0 ";
// If (strkeyword! = "")
// Strexpress = strexpress + "and (companyenname like '%" + strkeyword + "%' or companychname like '%" + strkeyword + "%' or companyshortname like '%" + strkeyword + "% 'or web like' %" + strkeyword + "% 'or mainproduct like' %" + strkeyword + "% 'or phone like' %" + strkeyword + "%' or memo like '% "+ strkeyword +" %' or address like '% "+ strkeyword +" %' or linkmanphone like '% "+ strkeyword +" % ') ";
// If (strsalesid! = "")
// Strexpress = strexpress + "and salesid =" + strsalesid;
Sqlcommand mycommand = new sqlcommand ();
Mycommand. Connection = myconnection;
Mycommand. commandtext = "getrecordfrompage ";
Mycommand. commandtype = commandtype. storedprocedure;
Mycommand. Parameters. Add ("@ tblname", "test1 ");
Mycommand. Parameters. Add ("@ fldname", "ID ");
Mycommand. Parameters. Add ("@ strwhere", strexpress );
Mycommand. Parameters. Add ("@ pagesize", customerlist. pagesize );
Mycommand. Parameters. Add ("@ pageindex", (INT) viewstate ["pageindex"] + 1 );
Myconnection. open ();
Sqldatareader myreader = mycommand. executereader ();
Customerlist. virtualitemcount = recordcount;
Customerlist. datasource = myreader;
Customerlist. datakeyfield = "ID ";
Customerlist. databind ();
Myreader. Close ();
Myconnection. Close ();
Myconnection. Dispose ();
}
Private int calcrecordcount ()
{
String SQL = "select count (ID) from test1 ";
Return (INT) p. executescalar (SQL );
}
# Code generated by region web Form Designer
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. linkbutton1.command + = new system. Web. UI. webcontrols. commandeventhandler (this. Up );
This. linkbutton2.command + = new system. Web. UI. webcontrols. commandeventhandler (this. Up );
This. Load + = new system. eventhandler (this. page_load );
}
# Endregion
Private void up (Object sender, system. Web. UI. webcontrols. commandeventargs E)
{
Linkbutton BTN = (linkbutton) sender;
Switch (BTN. commandname)
{
Case "up ":
Viewstate ["pageindex"] = (INT) viewstate ["pageindex"]-1;
Break;
Case "down ":
Viewstate ["pageindex"] = (INT) viewstate ["pageindex"] + 1;
Break;
}
BIND ();
}
}
}
Create procedure getrecordfrompage
@ Tblname varchar (255), -- table name
@ Fldname varchar (255), -- field name
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Iscount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1000) = ''-- Query condition (Note: Do not add where)
As
Declare @ strsql varchar (2000) -- subject sentence
Declare @ strtmp varchar (1000) -- Temporary Variable
Declare @ strorder varchar (1000) -- sort type
If @ ordertype! = 0
Begin
Set @ strtmp = "<(select Min"
Set @ strorder = "order by [" + @ fldname + "] DESC"
End
Else
Begin
Set @ strtmp = "> (select Max"
Set @ strorder = "order by [" + @ fldname + "] ASC"
End
Set @ strsql = "select top" + STR (@ pagesize) + "* from ["
+ @ Tblname + "] Where [" + @ fldname + "]" + @ strtmp + "(["
+ @ Fldname + "]) from (select top" + STR (@ PageIndex-1) * @ pagesize) + "["
+ @ Fldname + "] from [" + @ tblname + "]" + @ strorder + ") as tbltmp )"
+ @ Strorder
If @ strwhere! =''
Set @ strsql = "select top" + STR (@ pagesize) + "* from ["
+ @ Tblname + "] Where [" + @ fldname + "]" + @ strtmp + "(["
+ @ Fldname + "]) from (select top" + STR (@ PageIndex-1) * @ pagesize) + "["
+ @ Fldname + "] from [" + @ tblname + "] Where (" + @ strwhere + ")"
+ @ Strorder + ") as tbltmp) and (" + @ strwhere + ")" + @ strorder
If @ pageindex = 1
Begin
Set @ strtmp = ""
If @ strwhere! =''
Set @ strtmp = "where (" + @ strwhere + ")"
Set @ strsql = "select top" + STR (@ pagesize) + "* from ["
+ @ Tblname + "]" + @ strtmp + "" + @ strorder
End
If @ iscount! = 0
Set @ strsql = "select count (*) as total from [" + @ tblname + "]"
Exec (@ strsql)
Go
Correct your own mistakes
Timespan starttime = datetime. Now. timeofday; // statement bar
If (! Page. ispostback)
BIND ();
Label1.text = datetime. Now. timeofday. Subtract (starttime). totalmilliseconds. tostring (); // statement Bar B
Statement entries A and B should be placed at the beginning and end of the BIND () subprogram.
After the program is modified, the running time is more than 200 milliseconds for each page flip.
Use the pagination control provided by the DataGrid to perform paging measurement of million data rows. The paging time is usually-milliseconds, and dataset paging is used.
Paging the Local Machine with stored procedures [Memory + Virtual Memory] consumes approximately
Aspnet_wp.exe actual memory: 26,440 K virtual memory: 17,608 K
Sqlservr.exe actual memory: 26,752 K virtual memory: 29,092 K
Using. Net dataset paging, the local [Memory + Virtual Memory] consumes approximately
Aspnet_wp.exe actual memory: 73,512 K virtual memory: 91,352 K
Note: Each page flip, because datasetwill input data to the memory, the actual memory consumed by aspnet_wp.exe and the virtual memory will increase to nearly 400,000 kb.
That is, the more pages the page is, the more memory is occupied. Of course,. NET will release its own memory,
However, excessive memory consumption also slows down the computer.
Sqlservr.exe actual memory: 39,664 K virtual memory: 51,132 K