通用分页SQL [转载]

转载.
原文地址:http://www.cnblogs.com/edobnet/archive/2004/11/03/59858.html



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);
}

}
}