页面分页(通过存储过程实现)

原创 于 2008-06-20 11:51:00 发布 · 373 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#存储 #hyperlink #string #textbox #asp #server

本文介绍了一个SQL Server中用于实现高效分页查询的存储过程，并提供了完整的代码示例及前后端实现方式。该存储过程支持自定义字段、排序方式、查询条件等。 
CREATE PROCEDURE dbo.proc_DataPaging

(

@tblName     nvarchar(200),        ----要显示的表或多个表的连接

@fldName     nvarchar(500) = '*',    ----要显示的字段列表

@pageSize    int = 1,        ----每页显示的记录个数

@page        int = 10,        ----要显示那一页的记录

@pageCount    int = 1 output,            ----查询结果分页后的总页数

@Counts    int = 1 output,                ----查询到的记录数

@fldSort    nvarchar(200) = null,    ----排序字段列表或条件

@Sort        bit = 0,        ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')

@strCondition    nvarchar(4000) = null,    ----查询条件,不需where

@ID        nvarchar(150),        ----主表的主键

@Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加

)

AS

SET NOCOUNT ON

Declare @sqlTmp nvarchar(4000)        ----存放动态生成的SQL语句

Declare @strTmp nvarchar(4000)        ----存放取得查询结果总数的查询语句

Declare @strID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句

Declare @strSortType nvarchar(10)    ----数据排序规则A

Declare @strFSortType nvarchar(10)    ----数据排序规则B

Declare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造

Declare @SqlCounts nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造

if @Dist  = 0

begin

    set @SqlSelect = 'select '

    set @SqlCounts = 'Count(*)'

end

else

begin

    set @SqlSelect = 'select distinct '

    set @SqlCounts = 'Count(DISTINCT '+@ID+')'

end

if @Sort=0

begin

    set @strFSortType=' ASC '

    set @strSortType=' DESC '

end

else

begin

    set @strFSortType=' DESC '

    set @strSortType=' ASC '

end

--------生成查询语句--------

--此处@strTmp为取得查询结果数量的语句

if @strCondition is null or @strCondition=''     --没有设置显示条件

begin

    set @sqlTmp =  @fldName + ' From ' + @tblName

    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName

    set @strID = ' From ' + @tblName

end

else

begin

    set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition

    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition

    set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition

end

----取得查询结果总数量-----

--print @strTmp

exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

declare @tmpCounts int

if @Counts = 0

    set @tmpCounts = 1

else

    set @tmpCounts = @Counts

    --取得分页总数

    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

    /**//**当前页大于总页数 取最后一页**/

    if @page>@pageCount

        set @page=@pageCount

    --/*-----数据分页2分处理-------*/

    declare @pageIndex int --总数/页大小

    declare @lastcount int --总数%页大小 

    set @pageIndex = @tmpCounts/@pageSize

    set @lastcount = @tmpCounts%@pageSize

    if @lastcount > 0

        set @pageIndex = @pageIndex + 1

    else

        set @lastcount = @pagesize

    --//***显示分页

    if @strCondition is null or @strCondition=''     --没有设置显示条件

    begin

        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理

            begin 

                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName

                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

                        +' order by '+ @fldSort +' '+ @strFSortType+')'

                        +' order by '+ @fldSort +' '+ @strFSortType 

            end

        else

            begin

            set @page = @pageIndex-@page+1 --后半部分数据处理

                if @page <= 1 --最后一页数据显示

                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName

                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

                else                

                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName

                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

                        +' order by '+ @fldSort +' '+ @strSortType+')'

                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

            end

    end

    else --有查询条件

    begin

        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理

        begin 

                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from  '+@tblName

                    +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

                    +' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'

                    +' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType                 

        end

        else

        begin 

            set @page = @pageIndex-@page+1 --后半部分数据处理

            if @page <= 1 --最后一页数据显示

                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName

                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

            else

                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName

                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'

                        + @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

        end    

    end

------返回查询结果-----

print @strTmp

exec sp_executesql @strTmp



SET NOCOUNT OFF

GO











分页后台代码

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;

using System.Text.RegularExpressions;

using System.Data.SqlClient;



namespace WebApplication2

{

    public partial class _Default : System.Web.UI.Page

    {

        private string tblName = "t_files";

        private string fldName = "fileid,filename,exttype1,exttype2,exttype3,url,releasetime";

        private int size = 5;

        private int page = 1;

        private string sort = "releasetime";

        private string where;

        private string key = "fileid";

        private string src = "Default.aspx";         



        protected void Page_Load(object sender, EventArgs e)

        {

            hl_Next.Enabled = true; //下一页

            hl_Previous.Enabled = true; //上一页



            if (!IsPostBack)

            {

                where = " and spare3='free' and filestate=1";

                if (String.IsNullOrEmpty(Request["page"]) || Regex.IsMatch(Request["page"], "//D"))

                {

                    page = 1;

                }

                else

                {

                    page = Convert.ToInt16(Request["page"]);

                }

                DBBind();

            }

        }



        protected void btn_GOTO_Click(object sender, EventArgs e)

        {

            if (String.IsNullOrEmpty(txt_Page.Text) || Regex.IsMatch(txt_Page.Text, "//D"))

            {

                page = 1;

            }

            else

            {

                page = Convert.ToInt16(txt_Page.Text);

            }

            DBBind();

        }



        private void DBBind()

        {

            int pageCount, totalCount;

            SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["Test"]);

            cn.Open();

            SqlCommand cm = new SqlCommand("edu_admin.p_DataPaging", cn);

            cm.CommandType = CommandType.StoredProcedure; 

            cm.Parameters.Add("@tblName", SqlDbType.NVarChar, 200);

            cm.Parameters.Add("@fldName", SqlDbType.NVarChar, 500);

            cm.Parameters.Add("@pageSize", SqlDbType.Int, 4);

            cm.Parameters.Add("@page", SqlDbType.Int, 4);

            cm.Parameters.Add("@pageCount", SqlDbType.Int, 4);

            cm.Parameters.Add("@Counts", SqlDbType.Int, 4);

            cm.Parameters.Add("@fldSort", SqlDbType.NVarChar, 200);

            cm.Parameters.Add("@Sort", SqlDbType.Bit, 1);

            cm.Parameters.Add("@strCondition", SqlDbType.NVarChar, 4000);

            cm.Parameters.Add("@ID", SqlDbType.NVarChar, 150);

            cm.Parameters.Add("@Dist", SqlDbType.Bit, 1);



            cm.Parameters["@tblName"].Value = tblName; //要显示的表或多个表的连接

            cm.Parameters["@fldName"].Value = fldName; //要显示的字段列表

            cm.Parameters["@pageSize"].Value = size;     //每页显示的记录个数

            cm.Parameters["@page"].Value = page;         //要显示那一页的记录

            cm.Parameters["@pageCount"].Direction = ParameterDirection.Output; //查询结果分页后的总页数

            cm.Parameters["@Counts"].Direction = ParameterDirection.Output;     //查询到的记录数

            cm.Parameters["@fldSort"].Value = sort;      //排序字段列表或条件

            cm.Parameters["@Sort"].Value = 1;            //排序方法，0为升序，1为降序

            cm.Parameters["@strCondition"].Value = where; //查询条件,不需where

            cm.Parameters["@ID"].Value = key;                 //主表的主键

            cm.Parameters["@Dist"].Value = 0;                 //是否添加查询字段的 DISTINCT 默认0不添加/1添加    



            SqlDataReader dr = cm.ExecuteReader();

            GridView1.DataSource = dr;

            GridView1.DataBind();

            dr.Close();

            pageCount = Convert.ToInt16(cm.Parameters[4].Value);

            totalCount = Convert.ToInt16(cm.Parameters[5].Value);

            cn.Dispose();

            cm.Dispose();



            if (pageCount > 1)

            {

                if (page <= 1)

                {

                    page = 1;

                    hl_Previous.Enabled = false;

                    txt_Page.Text = "1";

                }

                if (page >= pageCount)

                {

                    hl_Next.Enabled = false;

                    txt_Page.Text = pageCount.ToString();

                    page = pageCount;

                }

            }

            else

            {

                page = 1;

                hl_Next.Enabled = false;

                hl_Previous.Enabled = false;

            }

            lbl_Count.Text = totalCount.ToString(); //总共的记录数

            lbl_Page.Text = page.ToString() + "/" + pageCount.ToString(); //显示值为单前请求页/总页数,如1/3

            hl_Next.NavigateUrl = src + "?page=" + Convert.ToString(page + 1) ;

            hl_Previous.NavigateUrl = src + "?page=" + Convert.ToString(page - 1) ;            

            txt_Page.Text = page.ToString();

        }

    }

}









分页前台代码

 <table border=0 align="center" cellpadding=0 cellspacing="0" width=571>

        <tr>

            <td height="40" align=right >

                本课程下共有

                  <asp:Label ID="lbl_Count" runat="server"></asp:Label>个课件　

                当前为<asp:Label ID="lbl_Page" runat="server"></asp:Label>页　

                <asp:HyperLink ID="hl_Previous" runat="server">上一页</asp:HyperLink>　

                <asp:HyperLink ID="hl_Next" runat="server">下一页</asp:HyperLink>　

                转到第<asp:TextBox ID="txt_Page" runat="server" Text="1" Width="15px"></asp:TextBox>页　

                <asp:Button ID="btn_GOTO" runat="server" Text="GO" OnClick="btn_GOTO_Click" />

            </td>

        </tr>

 </table>
alexniit4
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值