Use AjaxPro to achieve the effect of refreshing pages and database paging technology

Before reading this article, I suggest you read my series of articles: how to pass the value during the interaction between AjaxPro and the server: blog. csdn. netzhoufoxcnarchive200801052026908.aspx "using AjaxPro for second-level interaction": blog. csdn. netzhoufoxcnarchive200804262029204.aspx

Before reading this article, it is recommended to view my series of articles: "AjaxPro and server side in the process of interaction in how to pass the value": http://blog.csdn.net/zhoufoxcn/archive/2008/01/05/2026908.aspx "with AjaxPro to achieve second-level linkage": http://blog.csdn.net/zhoufoxcn/archive/2008/01/07/2029204.aspx "with Aj

Before reading this article, we recommend that you view my series of articles:
AjaxPro and how to pass the value in the process of interaction with the server: http://blog.csdn.net/zhoufoxcn/archive/2008/01/05/2026908.aspx
"Using AjaxPro to achieve secondary linkage": http://blog.csdn.net/zhoufoxcn/archive/2008/01/07/2029204.aspx
The use of AjaxPro to achieve regular refresh effect: http://blog.csdn.net/zhoufoxcn/archive/2008/03/09/2160407.aspx
In order to have a preliminary impression on AjaxPro.

Digress: despite repeated demands from some friends, I wrote so many Ajax articles, most of which were extracted from my project, however, in order to demonstrate the Framework Structure of the entire program, there will not be a lot of actual business logic processing in the demo program code, however, this does not prevent you from using these theories to make complex and perfect applications.

I. Database paging Theory
In actual projects, we often encounter data of several K or more M in a table, but it is not displayed to users at all times, so it is displayed to users in batches, this reduces the amount of network transmission and reduces the pressure on servers.

Generally, different databases have methods to query records from N to M (M> N> = 0), but their efficiency and performance are not the same. Assume that the following table is used:

Drop table if exists 'zhoufoxcn'. 'userlist ';
Create table 'zhoufoxcn'. 'userlist '(
'Userid' int (10) unsigned not null auto_increment,
'Username' varchar (45) not null,
'Age' int (10) unsigned not null default '10 ',
'Sex' tinyint (3) unsigned not null default '1 ',
'Tall' int (10) unsigned not null,
'Salary' int (10) unsigned not null,
Primary key ('userid ')
ENGINE = InnoDB AUTO_INCREMENT = 694 default charset = utf8;

The above is a MySQL table that I will use today. For tables with the same structure, the method for querying records from nth to nth is expressed in MySQL:

Select * from userlist order by userId limit n, m

Ms SQL Server:

Select top (m-n) * from userList where userid not in
(Select top n userid from userList order by userid) order by userid

Oracle:

Select * from (select rownum no, * from userlist where rownum <= m) where no> = n;

In addition, if the data volume is small, you can directly use the public int Fill (int startRecord, int maxRecords, params DataTable [] dataTables) method of the DbDataAdapter subclass instance. If the data volume is large, you may use a temporary table or cache method based on the actual situation to achieve higher performance.

Ii. program code:
Front-end page:

<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "AjaxPager. aspx. cs" Inherits = "AjaxPager" %>





Page turning Effect of AjaxPro

Overview table of young residents in harmonious community

Loading data. Please wait .....

Note: This list does not include retirees, persons with disabilities, and children.

Background code:

Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Caching;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using System. Text;
Using MySql. Data. MySqlClient;
Using MySql. Data. Types;

///

/// Description: This article describes how to use the AjaxPro technology to partially refresh pages. It also describes the database knowledge (MySQL, ms SQL, and Oracle) involved in page turning ).
/// This demo uses the MySQL database, and the data in the database is randomly generated by the program.
/// Starting address: http://blog.csdn.net/zhoufoxcn/archive/2008/03/12/2174234.aspx
/// By Zhou Gong
/// Date:
///
Public partial class AjaxPager: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
AjaxPro. Utility. RegisterTypeForAjax (typeof (AjaxPager ));
}

///
/// Read a specified number of data from a specified location in the database
///
/// Location of the record's start page
/// Number of records to read
///
[AjaxPro. AjaxMethod]
Public DataTable GetDataTable (int pageIndex, int size)
{
MySqlConnection connection = new MySqlConnection (ConfigurationManager. ConnectionStrings ["MySql"]. ConnectionString );
MySqlDataAdapter adapter = new MySqlDataAdapter ("select * from userlist limit" + (pageIndex-1) * size + "," + size, connection );
DataTable data = new DataTable ();
Adapter. Fill (data );
Connection. Close ();
Adapter. Dispose ();
Return data;
}
///
/// Pass the html string of the p Node
///
/// Location of the record's start page
/// Number of records to read
///
[AjaxPro. AjaxMethod]
Public string GetString (int pageIndex, int size)
{
StringBuilder text = new StringBuilder ();
Text. Append ("


























");Text. Append (" ");Text. Append (" ");Text. Append (" ");Text. Append (" ");Text. Append (" ");Text. Append (" ");Text. Append (" ");Text. Append (" ");DataTable source = GetDataTable (pageIndex, size );DataRow row;For (int I = 0; I <source. Rows. Count; I ++){Row = source. Rows [I];Text. Append (" ");For (int column = 0; column <source. Columns. Count; column ++){Text. Append (" ");}Text. Append (" ");}Int pageCount = (int) (Math. Ceiling (GetRecordCount ()/(double) size ));Text. Append (" ");Text. Append (" ");If (pageIndex <pageCount){Text. Append (" ");Text. Append ("

No.	Name	Age	Gender	Height	Salary
"+ Row [column]. ToString () +"
Homepage	"+ (PageIndex + 1) + ""); } Else { Text. Append (""); } If (pageIndex> 1) { Text. Append ("	"+ (PageIndex-1) + ""); } Else { Text. Append (""); } Text. Append ("	"+ PageCount +") '> last page	"); Text. Append ("	Current page: "+ pageIndex +"/"+ pageCount +"

");
Return text. ToString ();
}
///
/// Total number of returned records
///
///
[AjaxPro. AjaxMethod]
Public int GetRecordCount ()
{
MySqlConnection connection = new MySqlConnection (ConfigurationManager. ConnectionStrings ["MySql"]. ConnectionString );
MySqlCommand command = new MySqlCommand ("select count (userId) from userlist", connection );
Connection. Open ();
Int count = int. Parse (command. ExecuteScalar (). ToString ());
Connection. Close ();
Command. Dispose ();
Return count;
}
}

Program running effect:

Note: A careful friend may also find that the public able GetDataTable (int pageIndex, int size) in the program also has the AjaxMethod attribute. I originally planned to write this method, however, it is too late for everyone to implement it. This is another way: return a DataTable to the client and process the data in the DataTable, the difference between this method and the method I present is an innerHtml method that concatenates p layers on the server side and the client side. The advantage of server splicing is pure cs code, which is highly efficient in development, but it actually occupies server resources. The advantage of client splicing is that server resources are not occupied during splicing, high running efficiency, but low writing efficiency.