数据分页

#region 数据分页
        /// <summary>
        ///  单数据表数据分页
        /// </summary>
        /// <param name="TableName">数据库表名称</param>
        /// <param name="Conditions">查询条件</param>
        /// <param name="PageSize">单页面记录条数 为0时表示取出所有数据</param>
        /// <param name="CurrentPage">当前页数</param>
        /// <param name="OrderRule">排序规则</param>
        /// <param name="KeyName">当前表主键名称</param>
        /// <param name="RecordCount">记录总数</param>
        /// <returns>返回指定条数的满足条件的记录 和 满足条件的记录总数</returns>
        public DataTable GetPageData(String TableName,String Fields, String[] Conditions, Int32 PageSize, Int32 CurrentPage, String OrderRule, String KeyName,
            out Int32 RecordCount)
        {
            StringBuilder strBuilder = new StringBuilder();
            if (String.IsNullOrEmpty(Fields))
            {
                Fields = "*";
            }
            if (Conditions == null)
            {
                strBuilder.Append("1=1◎");
            }
            else
            {
                int _arr_len = Conditions.Length;
                for (int i = 0; i < _arr_len; i++)
                {
                    if (Conditions[i] == null || Conditions[i] == "")
                    {
                        continue;
                    }
                    strBuilder.Append(Conditions[i]);
                    strBuilder.Append("◎");
                }
            }
            DbCommand dbCmd = db.GetStoredProcCommand("UP_GetPageData");
            db.AddInParameter(dbCmd, "@TableName", DbType.String, TableName);
            db.AddInParameter(dbCmd, "@Fields", DbType.String, Fields);
            db.AddInParameter(dbCmd, "@Conditions", DbType.String, strBuilder.ToString());
            db.AddInParameter(dbCmd, "@PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCmd, "@CurPage", DbType.Int32, CurrentPage);
            db.AddInParameter(dbCmd, "@OrderRule", DbType.String, OrderRule);
            db.AddInParameter(dbCmd, "@KeyName", DbType.String, KeyName);
            db.AddOutParameter(dbCmd, "@RecordCount", DbType.Int32, 32);
            DataTable dt = null;
            try
            {
                dt = db.ExecuteDataSet(dbCmd).Tables[0];
                RecordCount = Convert.ToInt32(db.GetParameterValue(dbCmd, "@RecordCount"));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        #endregion

 

存储过程

ALTER PROCEDURE [dbo].[UP_GetPageData]
@TableName nvarchar(100),
@Fields varchar(1000),
@Conditions nvarchar(1000),
@PageSize int,
@CurPage int,
@OrderRule nvarchar(100),
@KeyName nvarchar(100),
@RecordCount int output
AS
--变量声明
DECLARE @SqlStr nvarchar(4000)
DECLARE @Location int
DECLARE @Split varchar(10)
DECLARE @Start int
DECLARE @ItemStr varchar(100)
DECLARE @Seed int
DECLARE @ParmDefinition nvarchar(500)
DECLARE @Result nvarchar(1000)
DECLARE @TempStr nvarchar(4000)
--变量初始化
SET @Split='◎'
SET @Seed=LEN(@Split)
SET @SqlStr=N''
BEGIN
    SET @SqlStr=@SqlStr+' FROM '
               +@TableName
               +N' WHERE '
    SET @Start=1
    SET @Location=CHARINDEX(@Split,@Conditions)
    WHILE @Location<>0
    BEGIN
        SET @ItemStr=SUBSTRING(@Conditions,@Start,@Location-@Start)
        SET @SqlStr=@SqlStr+@ItemStr
        SET @Start=@Location+@Seed
        SET @Location=CHARINDEX(@Split,@Conditions,@Start)
        IF @Location<>0
        BEGIN
            SET @SqlStr=@SqlStr+N' AND '  
        END
    END 
    --开始执行 1.取出记录的条数 2.取出想要的记录
    --1--
    SET @Result=N'SELECT @sRecordCount=COUNT(*)'
    SET @TempStr=@Result+@SqlStr
    SET @ParmDefinition=N'@sRecordCount int output'
    EXEC sp_executesql @TempStr,@ParmDefinition,@sRecordCount=@RecordCount output
    --2--
    IF @PageSize=0
    BEGIN
        SET @Result=N'SELECT '+@Fields+N' '
        SET @TempStr=@Result+@SqlStr
                    +N' ORDER BY '+@OrderRule
        EXEC sp_executesql @TempStr
    END
    ELSE
    BEGIN
        SET @Result=N'SELECT TOP '+CONVERT(varchar,@PageSize)+' '+@Fields+N' '
        SET @TempStr=@Result+@SqlStr
                    +N' AND '+@KeyName+' NOT IN(SELECT TOP '+CONVERT(varchar,@PageSize*(@CurPage-1))
                    +N' '+@KeyName
                    +@SqlStr
                    +N' ORDER BY '+@OrderRule+N') ORDER BY '+@OrderRule
        print @TempStr
        EXEC sp_executesql @TempStr
    END
END