sql server高效分页控件及c#调用实例

第一、首先在sqlserver中创建一个存储过程

USE [BZY]
GO
/****** 对象:  StoredProcedure [dbo].[up_ProcCustomPage2005_New]    脚本日期: 12/24/2013 11:17:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_ProcCustomPage2005_New]
@SelectSql varchar(4000)
,@OrderBy varchar(4000)
,@PageSize int
,@PageIndex int
,@PageCount int output
,@RecordCount int output
AS

--SET @SelectSql = 'select * from table_3'
--SET @OrderBy = 'order by id desc'

Declare @sql nvarchar(4000)
SET @sql = 'select @RecourdCount = count(*) from (' + @SelectSql + ') as tt@#$'
EXEC sp_executesql @sql,N'@RecourdCount int OUTPUT',@RecordCount OUTPUT

SET @PageCount = CEILING( (@RecordCount + 0.0) / @PageSize)

IF @PageIndex > @PageCount
    SET @PageIndex = @PageCount
    
IF @PageIndex < 1
    SET @PageIndex = 1

DECLARE @StartIndex int
DECLARE @EndIndex int

SET @StartIndex = (@PageIndex-1) * @PageSize + 1
SET @EndIndex = @PageIndex * @PageSize
    
SET @sql = 'select * from (select row_number()over(' + @OrderBy + ') as rownumber,*  from (' + @SelectSql + ') AS tt@#$) as tt@#$result where rownumber between ' + cast(@StartIndex AS varchar) + ' and ' + cast(@EndIndex AS varchar) 
PRINT @sql    
EXEC sp_executesql @sql

二、c#代码：

        private static DataTable GetExecuteCustomPage_New(string selectSql, string orderBy, int pageSize, int pageIndex, out int pageCount, out int recordCount) 
        {
            MatchCollection mc = Regex.Matches(orderBy, @"([^ ]*).[^ ]*");

            if (mc.Count > 0)
            {
                foreach (Match item in mc)
                {                    
                    orderBy = orderBy.Replace(item.Groups[1].Value + ".", "");
                }
            }           

            SqlParameter SelectSql = new SqlParameter() { ParameterName = "@SelectSql", Size = 4000, Value = selectSql };
            SqlParameter OrderBy = new SqlParameter() { ParameterName = "@OrderBy", Size = 4000, Value = orderBy };
            SqlParameter PageSize = new SqlParameter() { ParameterName = "@PageSize", Size = 4, Value = pageSize , DbType = DbType.Int32 };
            SqlParameter PageIndex = new SqlParameter() { ParameterName = "@PageIndex", Size = 4, Value = pageIndex , DbType = DbType.Int32 };
            SqlParameter PageCount = new SqlParameter() { ParameterName = "@PageCount", Size = 4, Direction = ParameterDirection.Output , DbType = DbType.Int32 };
            SqlParameter RecordCount = new SqlParameter() { ParameterName = "@RecordCount", Size = 4, Direction = ParameterDirection.Output , DbType = DbType.Int32 };

            DataSet ds = new DataSet();
        
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConfig"].ToString())) 
            {
                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = "up_ProcCustomPage2005_New";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnn;
                cmd.Parameters.Add(SelectSql);
                cmd.Parameters.Add(OrderBy);
                cmd.Parameters.Add(PageSize);
                cmd.Parameters.Add(PageIndex);
                cmd.Parameters.Add(PageCount);
                cmd.Parameters.Add(RecordCount);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                cnn.Open();
                sda.Fill(ds);

                pageCount = Convert.ToInt32(PageCount.Value);
                recordCount = Convert.ToInt32(RecordCount.Value);
            }

            return ds.Tables[0];

        }

三、引用例子

public DataSet GetPurchaserSalesVolumeAnalysis(string where,
           string sFilter_Condition, int iPage_Size, int iPage_Index, string sTaxisField,
           int iTaxis_Sign, out int iPageCount, out int iiRecord_Count)
        {

            string sql = @"select NewT.ptPaymentDate,NewT.fsStatus from Student NewT ";string orderwhere = " ORDER BY NewT.ptPaymentDate ";
            return BaitourDAO.Common.DBHelper.GetExecuteCustomPage_New(
                                  sql
                                  , orderwhere
                                  , iPage_Size
                                  , iPage_Index
                                  , out iPageCount
                                  , out iiRecord_Count);
        }