通用分页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);
        }

    }
}