分页存储过程

USE [xxx]
GO


/****** Object:  StoredProcedure [dbo].[baseGetDataFromPage]    Script Date: 07/11/2014 09:12:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--数据分页的存储过程
CREATE PROCEDURE [dbo].[baseGetDataFromPage] 
    @SelectList            VARCHAR(2000),    --欲选择字段列表
    @TableSource        VARCHAR(2000),    --表名或视图表 
    @SearchCondition    VARCHAR(2000),    --查询条件
    @OrderExpression    VARCHAR(1000),    --排序表达式
    @PageIndex            INT = 1,        --页号,从0开始
    @PageSize            INT = 10        --页尺寸
AS 
BEGIN
    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
    BEGIN
        SET @SelectList = '*'
    END
    PRINT @SelectList
    
    SET @SearchCondition = ISNULL(@SearchCondition,'')
    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
    IF @SearchCondition <> ''
    BEGIN
        IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
        BEGIN
            SET @SearchCondition = 'WHERE ' + @SearchCondition
        END
    END
    PRINT @SearchCondition


    SET @OrderExpression = ISNULL(@OrderExpression,'')
    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
    IF @OrderExpression <> ''
    BEGIN
        IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'
        BEGIN
            SET @OrderExpression = 'ORDER BY ' + @OrderExpression
        END
    END
    else
		begin
		   SET @OrderExpression = 'ORDER BY Id desc'
		end
    
    PRINT @OrderExpression


    IF @PageIndex IS NULL OR @PageIndex < 1
    BEGIN
        SET @PageIndex = 1
    END
    PRINT @PageIndex
    IF @PageSize IS NULL OR @PageSize < 1
    BEGIN
        SET @PageSize = 10
    END
    PRINT  @PageSize


    DECLARE @SqlQuery VARCHAR(4000)


    SET @SqlQuery='SELECT RowNumberTableSource.* 
    FROM 
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber 
          FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource 
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) 
    + ' AND ' + 
    CAST((@PageIndex * @PageSize) AS VARCHAR) 
--    ORDER BY ' + @OrderExpression
    PRINT @SqlQuery
    SET NOCOUNT ON
    EXECUTE(@SqlQuery)
    SET NOCOUNT OFF
 
    RETURN @@RowCount
END
GO

exec baseGetDataFromPage '', desProduct,"size='100'",'','2','15';

</pre><pre name="code" class="sql">  public static DataTable GetDataByPager(string SelectList, string tablename, string where, string OrderExpression, int pageindex, int pagesize,out string pagebar)
        {
            string sql = "select count(1) from " + tablename + (where == "" ? "" : " where " + where);
            int count = Convert.ToInt32(GetValue(CommandType.Text, sql, null));


            int pagecount = (count + pagesize - 1) / pagesize;


            string url = CurrentRequest.getRawUrl();
            if (url.Contains("?"))
            {
                if (!url.Contains("pageIndex"))
                {
                    url = url + "&pageIndex=" + pageindex.ToString();
                }
            }
            else
            {
                url = url + "?pageIndex=" + pageindex.ToString();
            }


            string firsturl, preurl, nexturl, lasturl;


            firsturl = url.Replace("pageIndex=" + pageindex, "pageIndex=1");
            preurl = url.Replace("pageIndex=" + pageindex, "pageIndex=" + (pageindex == 1 ? "1" : (pageindex - 1).ToString()));
            nexturl = url.Replace("pageIndex=" + pageindex, "pageIndex=" + (pageindex == pagecount ? pagecount.ToString() : (pageindex + 1).ToString()));
            lasturl = url.Replace("pageIndex=" + pageindex, "pageIndex=" + pagecount.ToString());


            StringBuilder sb = new StringBuilder();
            sb.Append("共" + count.ToString() + "条");
            sb.Append("&nbsp;&nbsp;&nbsp;&nbsp;第" + pageindex + "页/共" + pagecount.ToString() + "页");
            sb.Append("<a href='" + firsturl + "'>首页</a>");
            sb.Append("<a href='" + preurl + "'>上一页</a>");
            sb.Append("<a href='" + nexturl + "'>下一页</a>");
            sb.Append("<a href='" + lasturl + "'>尾页</a>");
            pagebar = sb.ToString();




            SqlParameter[] para = new SqlParameter[6];
            para[0] = new SqlParameter("@SelectList", SelectList);
            para[1] = new SqlParameter("@TableSource", tablename);
            para[2] = new SqlParameter("@SearchCondition", where);
            para[3] = new SqlParameter("@OrderExpression", OrderExpression);
            para[4] = new SqlParameter("@pageindex", pageindex);
            para[5] = new SqlParameter("@pagesize", pagesize);


            return GetDataTable(CommandType.StoredProcedure, "baseGetDataFromPage", para);
        }