通用分页SQL

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

CREATE     PROCEDURE  Paging_Asc_Desc
@Tables  varchar ( 1000 ),
@PK  varchar ( 100 ),
@Sort  varchar ( 200 )  =   NULL ,
@PageNumber  int   =   1 ,
@PageSize  int   =   10 ,
@Fields  varchar ( 1000 )  =   ' * ' ,
@Filter  varchar ( 1000 )  =   NULL ,
@Group  varchar ( 1000 )  =   NULL ,
@isCount  bit   =   0   -- 1时返回记录条数
AS

/**/ /*Find the @PK type*/
DECLARE  @PKTable  varchar ( 100 )
DECLARE  @PKName  varchar ( 100 )
DECLARE  @type  varchar ( 100 )
DECLARE  @prec  int

IF   CHARINDEX ( ' . ' , @PK)  >   0
     BEGIN
         SET  @PKTable  =   SUBSTRING (@PK,  0 ,  CHARINDEX ( ' . ' ,@PK))
         SET  @PKName  =   SUBSTRING (@PK,  CHARINDEX ( ' . ' ,@PK)  +   1 ,  LEN (@PK))
     END
ELSE
     BEGIN
         SET  @PKTable  =  @Tables
         SET  @PKName  =  @PK
     END

SELECT  @type = t.name, @prec = c.prec
FROM  sysobjects o 
JOIN  syscolumns c  on  o.id = c.id
JOIN  systypes t  on  c.xusertype = t.xusertype
WHERE  o.name  =  @PKTable  AND  c.name  =  @PKName

IF   CHARINDEX ( ' char ' , @type)  >   0
    SET  @type  =  @type  +   ' ( '   +   CAST (@prec  AS   varchar )  +   ' ) '

DECLARE  @strPageSize  varchar ( 50 )
DECLARE  @strRows  varchar ( 50 )
DECLARE  @strFilter  varchar ( 8000 )
DECLARE  @strGroup  varchar ( 8000 )
DECLARE  @strSortColumn  varchar ( 4000 )
DECLARE  @strSortDesc  varchar ( 4000 )

/**/ /*Default Sorting*/
IF  @Sort  IS   NULL
     SET  @Sort  =  @PK

/**/ /*Set sorting variables.*/     
IF   CHARINDEX ( ' DESC ' ,@Sort) > 0
     BEGIN
         SET  @strSortDesc  =   REPLACE (@Sort,  ' DESC ' ,  ' ASC ' )
         SET  @strSortColumn  =   ' ,  '   +   REPLACE (@Sort,  ' DESC ' ,  '' )
     END
ELSE
     BEGIN
         IF   CHARINDEX ( ' ASC ' , @Sort)  =   0
             BEGIN
                 SET  @strSortDesc  =  @Sort  +   '  DESC '
                 SET  @strSortColumn  =   ' ,  '   +  @Sort
             END
         ELSE
             BEGIN
                 SET  @strSortDesc  =   REPLACE (@Sort,  ' ASC ' ,  ' DESC ' )
                 SET  @strSortColumn  =   ' ,  '   +   REPLACE (@Sort,  ' ASC ' ,  '' )
             END
     END
IF  @Sort  =  @PK
     SET  @strSortColumn  =   ''

/**/ /*Default Page Number*/
IF  @PageNumber  <   1
     SET  @PageNumber  =   1
    
/**/ /*Set paging variables.*/
SET  @strPageSize  =   CONVERT ( varchar ( 50 ), @PageSize)
SET  @strRows  =   CONVERT ( varchar ( 50 ), (@PageSize  *  (@PageNumber  -   1 )  +  @PageSize))

/**/ /*Set filter & group variables.*/
IF  @Filter  IS   NOT   NULL   AND  @Filter  !=   ''
     BEGIN
         SET  @strFilter  =   '  WHERE  '   +  @Filter  +   '   '
     END
ELSE
     BEGIN
         SET  @strFilter  =   ''
     END
IF  @Group  IS   NOT   NULL   AND  @Group  !=   ''
     SET  @strGroup  =   '  GROUP BY  '   +  @Group  +   '   '
ELSE
     SET  @strGroup  =   ''

if  @isCount  =   1
     begin
         EXEC ( ' SELECT  Count(*) FROM  '   +  @Tables  +  @strFilter  +   '   '   +  @strGroup )
     end
else
     begin
     /**/ /*Execute dynamic query*/     
     EXEC (
     ' DECLARE @tblPK TABLE (
                PK   '   +  @type  +   '  NOT NULL PRIMARY KEY
                )
    
    INSERT INTO @tblPK SELECT TOP  '   +  @strPageSize  +   '   '   +  @PK  +   '  FROM (SELECT TOP  '   +  @strRows  +   '   '   +  @PK  +  @strSortColumn  +   '  FROM  '   +  @Tables  +  @strFilter  +   '   '   +  @strGroup  +   '  ORDER BY  '   +  @Sort  +   ' ) AS  '   +  @PKTable  +   '  ORDER BY  '   +  @strSortDesc  +   '
    
    SELECT  '   +  @Fields  +   '  FROM  '   +  @Tables  +   '  JOIN @tblPK tblPK ON  '   +  @PK  +   '  = tblPK.PK  '   +  @strFilter  +   '   '   +  @strGroup  +   '  ORDER BY  '   +  @Sort
    )
     end




GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

简单测试代码

using  System;
using  System.Collections;
using  System.ComponentModel;
using  System.Data;
using  System.Drawing;
using  System.Web;
using  System.Web.SessionState;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.HtmlControls;
using  System.Data.SqlClient;
using  Microsoft.ApplicationBlocks.Data;

namespace  PagingWebTest
{
    /**//// <summary>
    /// Summary description for WebForm1.
    /// </summary>
    public class WebForm1 : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid DataGrid1;

        //Connection string
        string conStr = "Server=Localhost;Uid=sa;Pwd=;Database=test";

        string spName = "Paging_Asc_Desc";
        string Tables = "LargeTable";
        string PK = "LargeTable.PK";
        int PageNumber = 1;
        int PageSize = 10;
        string Fields = "*";
        string Filter = "";
        string Group = "";

        /**//// <summary>
        /// Property Sort (string)
        /// </summary>
        public string Sort
        {
            get
            {
                if ((string)this.ViewState["Sort"]=="")
                {
                    return "LargeTable.PK";
                }
                return (string)this.ViewState["Sort"];
            }
            set
            {
                this.ViewState["Sort"] = value;
            }
        }
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            // This is the key line for custom paging, DataGrid will automatically calculate everything, it just needs the total number of pages
            DataGrid1.VirtualItemCount = (int)SqlHelper.ExecuteScalar(conStr, spName, new object[]{Tables, PK, "", 0, 0, "", Filter, Group,1});

            //Bind Grid the first time
            if(!Page.IsPostBack)
            {
                BindGrid(PageNumber);
            }
        }

        Web Form Designer generated code#region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /**//// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {    
            this.DataGrid1.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.DataGrid1_PageIndexChanged);
            this.DataGrid1.SortCommand += new System.Web.UI.WebControls.DataGridSortCommandEventHandler(this.DataGrid1_SortCommand);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        //DataBinding
        private void BindGrid(int pageNumber)
        {
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExecuteReader(conStr, spName, new object[]{Tables, PK, Sort, pageNumber, PageSize, Fields, Filter, Group,0});
                DataGrid1.DataSource = dr;
                DataGrid1.DataBind();
            }
            catch (Exception ex)
            {
                throw(ex);
            }
            finally
            {
                if (dr!=null) dr.Close();
            }
        }

        //Handle Paging
        private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
        {
            DataGrid1.CurrentPageIndex = e.NewPageIndex;
            if(e.NewPageIndex+1 >= DataGrid1.PageCount)
                BindGrid(DataGrid1.CurrentPageIndex);
            else
                BindGrid(DataGrid1.CurrentPageIndex+1);
        
        }

        //Handle Sorting
        private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
        {
            this.Sort = e.SortExpression;
            BindGrid(DataGrid1.CurrentPageIndex);
        }

    }
}