sqlserver2005简单分页解决方案学习

一、简单分页存储过程

1、”not in" 方式

 

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        <冯俊杰>
--  Create date: <2010-03-22>
--  Description:    <Description,,>
--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ pro_page1 ]
@pageindex   int , -- 当前页码
@pagesize   int -- 分页大小/*
AS
BEGIN

SELECT   TOP (  @pagesize )  *
FROM  s_table
WHERE  nid  NOT   IN
          (
           SELECT   top  ( @pagesize * ( @pageindex - 1 )) nid  FROM  s_table  ORDER   BY  nid
          )
ORDER   BY  nid
END

 

 

2、max id方式

 

代码

/* ***********************************************************
 * Code formatted by SoftTree SQL Assistant ?v4.7.11
 * Time: 2010-3-22 18:08:52
 *********************************************************** */

USE  test
SET  ANSI_NULLS  ON
GO

SET  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        <冯俊杰>
--  Create date: <2010-03-22>
--  Description:    <maxid分页>
--  =============================================
ALTER   PROCEDURE  pro_page2
     @pagesize   INT ,  -- 页码大小
     @pageindex   INT -- 当前页码
AS
BEGIN
     SELECT   TOP ( @pagesize )  *
     FROM    s_table
     WHERE   nid  >  (
                SELECT   ISNULL ( MAX (nid),  0 )
                FROM    (
                           SELECT   TOP ( @pagesize   *  ( @pageindex   - 1 ))  *
                           FROM    s_table  ORDER   BY  nid 
                      )A
    )
     ORDER   BY  nid
END
EXEC  pro_page2  10 , 2

3、rownumber 内置函数---效率最高

代码

/* ***********************************************************
 * Code formatted by SoftTree SQL Assistant ?v4.7.11
 * Time: 2010-3-22 22:24:18
 *********************************************************** */

/* ***********************************************************
 * Code formatted by SoftTree SQL Assistant ?v4.7.11
 * Time: 2010-3-22 22:23:58
 *********************************************************** */

--  ================================================

--  ================================================
SET  ANSI_NULLS  ON
GO

SET  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        <Author,,Name>
--  Create date: <Create Date,,>
--  Description:    <Description,,>
-- rownumber over:为sqlserver2005内置函数,功能为范围制定查询的连续顺序号
--  =============================================
alter   PROCEDURE  pro_page3
     @pagesize   INT ,
     @pageindex   INT
AS
BEGIN
     SELECT   TOP ( @pagesize )  *
     FROM    (
                SELECT  nid,
                      ROW_NUMBER()  OVER ( ORDER   BY  nid)  AS  rownumber
                FROM    s_table
               
           )a
     WHERE   rownumber  >  ( @pagesize   *  ( @pageindex   - 1 ))
END
GO
--

二、简单C#代码实现

前台页面：

代码

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

<! 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 >< a  href ="http://www.163.com" > xinlang </ a ></ div >
     < div >

         < asp:GridView  ID ="GridView1"  runat ="server"  
            onrowdatabound ="GridView1_RowDataBound" >
         </ asp:GridView >
     </ div >
     < div >
         < asp:LinkButton  ID ="LinkButton1"  runat ="server"  onclick ="LinkButton1_Click" > 首页 </ asp:LinkButton >
         < asp:LinkButton  ID ="LinkButton2"  runat ="server"  onclick ="LinkButton2_Click" > 上一页 </ asp:LinkButton >
         < asp:LinkButton  ID ="LinkButton3"  runat ="server"  onclick ="LinkButton3_Click" > 下一页 </ asp:LinkButton >
         < asp:LinkButton  ID ="LinkButton4"  runat ="server"  onclick ="LinkButton4_Click" > 尾页 </ asp:LinkButton >
     </ div >
     </ form >
</ body >
</ html >

 

后台代码：

代码

using  System;
using  System.Configuration;
using  System.Data;
using  System.Linq;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.HtmlControls;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Xml.Linq;
using  System.Data.SqlClient;

public   partial   class  _Default : System.Web.UI.Page 
{
     private   static   string  contring  =   " Data Source=LENOVO-FENGJUNJ;Initial Catalog=test;User ID=sa " ;
     private   static   int  TotalCount  =   10 ;

     protected   void  Page_Load( object  sender, EventArgs e)
    {
         if  ( ! IsPostBack)
        {
             this .GDataBind( 1 );
        }
    }
     protected   void  LinkButton1_Click( object  sender, EventArgs e)
    {
         // 首页
         this .GDataBind( 1 );
    }
     ///   <summary>
     ///  数据绑定
     ///   </summary>
     ///   <param name="pageindex"></param>
     private   void  GDataBind( int  pageindex)
    {
         int  pagesize  =   10 ;
        SqlConnection con  =   new  SqlConnection(contring);
        con.Open();
        SqlCommand commd  =   new  SqlCommand();
        commd.Connection  =  con;
        commd.CommandType  =  CommandType.StoredProcedure;
        commd.CommandText  =   " pro_page3 " ;
        commd.Parameters.Add( " @pageindex " , SqlDbType.Int,  50 );
        commd.Parameters.Add( " @pagesize " , SqlDbType.Int,  50 );
        commd.Parameters[ " @pageindex " ].Value  =  pageindex;
        commd.Parameters[ " @pagesize " ].Value  =  pagesize;
        SqlDataAdapter sd  =   new  SqlDataAdapter(commd);
        DataTable dt = new  DataTable();
        sd.Fill(dt);
        GridView1.DataSource  =  dt;
        GridView1.DataBind();
         if  ( this .pageindex  >=  TotalCount)
        {
            LinkButton3.Enabled  =   false ;
        }
         else
        {
             if  ( this .pageindex <=   1 )
            {
                LinkButton2.Enabled  =   false ;
            }        
             else
        {
            LinkButton2.Enabled = true ;
        }
        }


    }
     private   int  pageindex
    {
         get  
        {  if  (ViewState[ " pageindex " ] == null )
        {
            ViewState[ " pageindex " ] = 1 ;
        }
         return  Convert.ToInt32(ViewState[ " pageindex " ].ToString());
        }
         set  
        {
            ViewState[ " pageindex " ]  =  value;
        }
    }
     protected   void  LinkButton2_Click( object  sender, EventArgs e)
    {
         // 上一页
         this .pageindex -- ;
         this .GDataBind(pageindex);
    }
     protected   void  LinkButton3_Click( object  sender, EventArgs e)
    {
         // 下一页
         this .pageindex ++ ;
         this .GDataBind(pageindex);
    }
     protected   void  LinkButton4_Click( object  sender, EventArgs e)
    {
         this .pageindex  =  TotalCount;
         this .GDataBind(pageindex);
    }
     protected   void  GridView1_RowDataBound( object  sender, GridViewRowEventArgs e)
    {
    }
}

 

 

 

 

 

转载于:https://www.cnblogs.com/jack-feng/archive/2010/03/22/1691977.html