随笔- 87  评论- 610  文章- 1 

通用分页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, 0CHARINDEX('.',@PK))
        
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1LEN(@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, ""00"", Filter, Group,1});

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

        }


        
Web Form Designer generated code

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


    }

}

posted on 2004-11-03 09:39  edobnet  阅读(...)  评论(...编辑  收藏