---------------------------------
--用途：分页存储过程(对有主键的表效率极高)
--说明：
------------------------------------
CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTotal varchar(2000) -- 获取记录数合计语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
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 @strWhere != ''
set @strTotal = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
else
set @strTotal = 'select count(*) as Total from [' + @tblName + ']'
exec (@strSQL)
if @IsReCount != 0
exec (@strTotal)
GO
在asp.net中的代码,本人采用三层架构,没有完全整理好,相信大家都能看的懂,呵呵~~~~~~~~~--用途：分页存储过程(对有主键的表效率极高) --说明：------------------------------------CREATE PROCEDURE UP_GetRecordByPage @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)ASdeclare @strSQL varchar(6000) -- 主语句declare @strTotal varchar(2000) -- 获取记录数合计语句declare @strTmp varchar(1000) -- 临时变量declare @strOrder varchar(400) -- 排序类型if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc'endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'endset @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrderif @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 + ' ' + @strOrderif @PageIndex = 1begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end if @strWhere != '' set @strTotal = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereelse set @strTotal = 'select count(*) as Total from [' + @tblName + ']'exec (@strSQL)if @IsReCount != 0exec (@strTotal)GO1.html代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewMinutepage.aspx.cs"
Inherits="GridViewMinutepage_" %><%@ Register Assembly="LtpPageControl" Namespace="LtpPageControl" TagPrefix="cc1" %><!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>无标题页</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" Width="100%"> </asp:GridView> </div> <cc1:Page02 ID="Page02_1" runat="server" Page_Index="GridViewMinutepage.aspx" /> </form></body></html>using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class GridViewMinutepage_ : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e)
{
int PageIndex = 1; if (!Page.IsPostBack) { if (Request.Params["page"] != null && Request.Params["page"].ToString() != "") { PageIndex = Convert.ToInt32(Request.Params["page"]);
} else { PageIndex = 1; } dataBind(PageIndex, 5, ""); } } /// <summary> /// 功能:分页控件应用 /// 作 者 ： PUKE /// 完成日期： 2007-05-28 /// 版权所有： pukesys@tom.com /// </summary> /// <param name="pageIndex">当前页</param> /// <param name="PageSize">每页几行</param> /// <param name="strSql">条件</param> public void dataBind(int pageIndex, int PageSize, string strSql) { Practice.BLL.authors bll = new Practice.BLL.authors(); DataSet ds = bll.Minutepage("authors", "au_id", PageSize, pageIndex, 1, 0,strSql); this.GridView1.DataSource = ds.Tables[0].DefaultView; int record_Count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString()); int totalPages = int.Parse(Math.Ceiling((double)record_Count / PageSize).ToString()); if (totalPages > 0) { if ((pageIndex + 1) > totalPages) pageIndex = totalPages - 1; } else { pageIndex = 0; } this.GridView1.DataBind(); int page_Count = totalPages; int page_Current = pageIndex; Page02_1.Page_Count = page_Count; Page02_1.Page_Size = PageSize; Page02_1.Page_Current = page_Current; }}/// <summary> /// 功 能：分页 /// 作 者：PUKE /// 完成时间：2007-05-28 /// 版 权：pukesys@tom.com /// </summary> /// <param name="tblName">表名</param> /// <param name="fldName">主键字段名</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="IsReCount">返回记录总数, 非 0 值则返回</param> /// <param name="OrderType">设置排序类型, 非 0 值则降序</param> /// <param name="strWhere">查询条件 (注意: 不要加 where)</param> /// <returns>ds</returns> public DataSet Minutepage(string tblName,string fldName,int PageSize,int PageIndex,int IsReCount,int OrderType,string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar,255), new SqlParameter("@fldName",SqlDbType.VarChar,255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount",SqlDbType.Bit), new SqlParameter("@OrderType",SqlDbType.Bit), new SqlParameter("@strWhere",SqlDbType.VarChar,1000) }; parameters[0].Value = tblName; parameters[1].Value = fldName; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = IsReCount; parameters[5].Value = OrderType; parameters[6].Value = strWhere; return DbHelperSQL.RunProcedure("PR_MinutePage", parameters, "ds"); }