一、数据库分页理论
在实际项目中经常会遇到一个表里有几K、几M以上的数据,而呈现给用户时并不会一下子都显示出来,所以都是分批展示给用户,这样一来可以减小网络传输量,二来也减轻服务器压力。
通常在不同数据库中都有查询从第N条到第M条记录的方法(M>N>=0),不过其效率和性能都不太一样。假如有如下一个表:
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;
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;
select
*
from
userlist order by userId limit n,m
select
top
(m
-
n)
*
from
userList
where
userid
not
in
( select top n userid from userList order by userid) order by userid
( select top n userid from userList order by userid) order by userid
select
*
from
(
select
rownum no,
*
from
userlist
where
rownum
<=
m)
where
no
>=
n;
另外,如果数据量小的话还可以直接用DbDataAdapter 的子类的实例的public int Fill (int startRecord,int maxRecords,params DataTable[] dataTables)方法。如果数据量大的话,可能会根据实际情况采用临时表或者缓存的办法,来获得更高性能。
二、程序代码:
前台页面:
<%
@ Page Language
=
"
C#
"
AutoEventWireup
=
"
true
"
CodeFile
=
"
AjaxPager.aspx.cs
"
Inherits
=
"
AjaxPager
"
%>
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< html xmlns ="http://www.w3.org/1999/xhtml" >
< head runat ="server" >
< title > AjaxPro翻页效果 </ title >
< style type ="text/css" >
tr.items {
background-color : #8FACC2 ;
border-color : #FFFFFF ;
line-height : 18px ;
}
tr.table { /* 表格内容 */
background-color : #F1F3F5 ;
border-color : #FFFFFF ;
line-height : 18px ;
}
</ style >
</ head >
< body onload ="JumpPage(1)" >
< form id ="form1" runat ="server" >
< table border ="0" cellpadding ="1" cellspacing ="1" >
< tr >< td > 和谐小区青年居民概况表 </ td ></ tr >
< tr >< td >
< div id ="memberList" >
数据装载中,请等待.....
</ div >
</ td ></ tr >
< tr >< td > 说明:本名单中不包括离退休人员、残疾智障人员和儿童。 </ td ></ tr >
</ table >
</ form >
< script language ="javascript" type ="text/javascript" defer ="defer" >
var pageSize = 20 ; // 假定每页显示20条数据
function JumpPage(page) // 完全服务器端分页处理方法
{
var label = document.getElementById( " memberList " );
label.innerHTML = AjaxPager.GetString(parseInt(page),parseInt(pageSize)).value;
}
/*
function ShowPager()
{
}
function JumpPageClient(page)
{
var label=document.getElementById("memberList");
var data=AjaxPager.GetDataTable(parseInt(page),parseInt(pageSize)).value;
if(data!=null)
{
alert(data.Rows.length);
}
label.innerHTML=data.Rows.length;
}
*/
</ script >
</ body >
</ html >
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< html xmlns ="http://www.w3.org/1999/xhtml" >
< head runat ="server" >
< title > AjaxPro翻页效果 </ title >
< style type ="text/css" >
tr.items {
background-color : #8FACC2 ;
border-color : #FFFFFF ;
line-height : 18px ;
}
tr.table { /* 表格内容 */
background-color : #F1F3F5 ;
border-color : #FFFFFF ;
line-height : 18px ;
}
</ style >
</ head >
< body onload ="JumpPage(1)" >
< form id ="form1" runat ="server" >
< table border ="0" cellpadding ="1" cellspacing ="1" >
< tr >< td > 和谐小区青年居民概况表 </ td ></ tr >
< tr >< td >
< div id ="memberList" >
数据装载中,请等待.....
</ div >
</ td ></ tr >
< tr >< td > 说明:本名单中不包括离退休人员、残疾智障人员和儿童。 </ td ></ tr >
</ table >
</ form >
< script language ="javascript" type ="text/javascript" defer ="defer" >
var pageSize = 20 ; // 假定每页显示20条数据
function JumpPage(page) // 完全服务器端分页处理方法
{
var label = document.getElementById( " memberList " );
label.innerHTML = AjaxPager.GetString(parseInt(page),parseInt(pageSize)).value;
}
/*
function ShowPager()
{
}
function JumpPageClient(page)
{
var label=document.getElementById("memberList");
var data=AjaxPager.GetDataTable(parseInt(page),parseInt(pageSize)).value;
if(data!=null)
{
alert(data.Rows.length);
}
label.innerHTML=data.Rows.length;
}
*/
</ script >
</ body >
</ html >
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;
/// <summary>
/// 说明:本文介绍如何利用AjaxPro技术实现翻页时局部刷新,同时也介绍了翻页所涉及到的数据库知识(MySQL、MS SQL和Oracle)。
/// 本演示程序采用MySQL数据库,数据库中的数据是采用程序随机生成的。
/// 首发地址:http://blog.csdn.net/zhoufoxcn/archive/2008/03/12/2174234.aspx
/// 作者:周公
/// 日期:2008-3-12
/// </summary>
public partial class AjaxPager : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
AjaxPro.Utility.RegisterTypeForAjax( typeof (AjaxPager));
}
/// <summary>
/// 从数据库中指定位置读取指定数目的数据
/// </summary>
/// <param name="startIndex"> 记录的起始页位置 </param>
/// <param name="size"> 要读取的记录条数 </param>
/// <returns></returns>
[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;
}
/// <summary>
/// 传递div节点的html字符串
/// </summary>
/// <param name="startIndex"> 记录的起始页位置 </param>
/// <param name="size"> 要读取的记录条数 </param>
/// <returns></returns>
[AjaxPro.AjaxMethod]
public string GetString( int pageIndex, int size)
{
StringBuilder text = new StringBuilder();
text.Append( " <table border='0' cellpadding='0' cellspacing='0' width='520px'> " );
text.Append( " <tr class='items' align='center'> " );
text.Append( " <td style='width:80px'>编号</td> " );
text.Append( " <td style='width:80px'>姓名</td> " );
text.Append( " <td style='width:80px'>年龄</td> " );
text.Append( " <td style='width:80px'>性别</td> " );
text.Append( " <td style='width:80px'>身高</td> " );
text.Append( " <td style='width:80px'>工资</td> " );
text.Append( " </tr> " );
DataTable source = GetDataTable(pageIndex,size);
DataRow row;
for ( int i = 0 ; i < source.Rows.Count; i ++ )
{
row = source.Rows[i];
text.Append( " <tr class='table' align='center'> " );
for ( int column = 0 ; column < source.Columns.Count; column ++ )
{
text.Append( " <td style='width:80px'> " + row[column].ToString() + " </td> " );
}
text.Append( " </tr> " );
}
int pageCount = ( int )(Math.Ceiling(GetRecordCount() / ( double )size));
text.Append( " <tr class='items' align='center'> " );
text.Append( " <td><a href='javascript:JumpPage(1)'>首页</a></td> " );
if (pageIndex < pageCount)
{
text.Append( " <td><a href='javascript:JumpPage( " + (pageIndex + 1 ) + " )'>下一页</a></td> " );
}
else
{
text.Append( " <td>下一页</a></td> " );
}
if (pageIndex > 1 )
{
text.Append( " <td><a href='javascript:JumpPage( " + (pageIndex - 1 ) + " )'>上一页</a></td> " );
}
else
{
text.Append( " <td>上一页</a></td> " );
}
text.Append( " <td><a href='javascript:JumpPage( " + pageCount + " )'>尾页</a><td> " );
text.Append( " <td>当前页: " + pageIndex + " / " + pageCount + " </td> " );
text.Append( " </table> " );
return text.ToString();
}
/// <summary>
/// 返回记录总条数
/// </summary>
/// <returns></returns>
[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;
}
}
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;
/// <summary>
/// 说明:本文介绍如何利用AjaxPro技术实现翻页时局部刷新,同时也介绍了翻页所涉及到的数据库知识(MySQL、MS SQL和Oracle)。
/// 本演示程序采用MySQL数据库,数据库中的数据是采用程序随机生成的。
/// 首发地址:http://blog.csdn.net/zhoufoxcn/archive/2008/03/12/2174234.aspx
/// 作者:周公
/// 日期:2008-3-12
/// </summary>
public partial class AjaxPager : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
AjaxPro.Utility.RegisterTypeForAjax( typeof (AjaxPager));
}
/// <summary>
/// 从数据库中指定位置读取指定数目的数据
/// </summary>
/// <param name="startIndex"> 记录的起始页位置 </param>
/// <param name="size"> 要读取的记录条数 </param>
/// <returns></returns>
[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;
}
/// <summary>
/// 传递div节点的html字符串
/// </summary>
/// <param name="startIndex"> 记录的起始页位置 </param>
/// <param name="size"> 要读取的记录条数 </param>
/// <returns></returns>
[AjaxPro.AjaxMethod]
public string GetString( int pageIndex, int size)
{
StringBuilder text = new StringBuilder();
text.Append( " <table border='0' cellpadding='0' cellspacing='0' width='520px'> " );
text.Append( " <tr class='items' align='center'> " );
text.Append( " <td style='width:80px'>编号</td> " );
text.Append( " <td style='width:80px'>姓名</td> " );
text.Append( " <td style='width:80px'>年龄</td> " );
text.Append( " <td style='width:80px'>性别</td> " );
text.Append( " <td style='width:80px'>身高</td> " );
text.Append( " <td style='width:80px'>工资</td> " );
text.Append( " </tr> " );
DataTable source = GetDataTable(pageIndex,size);
DataRow row;
for ( int i = 0 ; i < source.Rows.Count; i ++ )
{
row = source.Rows[i];
text.Append( " <tr class='table' align='center'> " );
for ( int column = 0 ; column < source.Columns.Count; column ++ )
{
text.Append( " <td style='width:80px'> " + row[column].ToString() + " </td> " );
}
text.Append( " </tr> " );
}
int pageCount = ( int )(Math.Ceiling(GetRecordCount() / ( double )size));
text.Append( " <tr class='items' align='center'> " );
text.Append( " <td><a href='javascript:JumpPage(1)'>首页</a></td> " );
if (pageIndex < pageCount)
{
text.Append( " <td><a href='javascript:JumpPage( " + (pageIndex + 1 ) + " )'>下一页</a></td> " );
}
else
{
text.Append( " <td>下一页</a></td> " );
}
if (pageIndex > 1 )
{
text.Append( " <td><a href='javascript:JumpPage( " + (pageIndex - 1 ) + " )'>上一页</a></td> " );
}
else
{
text.Append( " <td>上一页</a></td> " );
}
text.Append( " <td><a href='javascript:JumpPage( " + pageCount + " )'>尾页</a><td> " );
text.Append( " <td>当前页: " + pageIndex + " / " + pageCount + " </td> " );
text.Append( " </table> " );
return text.ToString();
}
/// <summary>
/// 返回记录总条数
/// </summary>
/// <returns></returns>
[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;
}
}