SQL 语句 数据分页方法

#region 数据分页 返回 DataTable
        /// <summary>
        /// 摘要:
        ///     数据分页
        /// 参数：
        ///     sql：传入要执行sql语句
        ///     param：参数化
        ///     orderField：排序字段
        ///     orderType：排序类型
        ///     pageIndex：当前页
        ///     pageSize：页大小
        ///     count：返回查询条数
        /// </summary>
        public DataTable GetPageList(string sql, SqlParam[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                int num = (pageIndex - 1) * pageSize;
                int num1 = (pageIndex) * pageSize;
                sb.Append("Select * From (Select ROW_NUMBER() Over (Order By " + orderField + " " + orderType + "");
                sb.Append(") As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
                count = Convert.ToInt32(this.GetObjectValue(new StringBuilder("Select Count(1) From (" + sql + ") As t"), param));
                return this.GetDataTableBySQL(sb, param);
            }
            catch (Exception e)
            {
                DbLog.WriteException(e);
                return null; ;
            }
        }
        /// <summary>
        /// 摘要:
        ///     数据分页
        /// 参数：
        ///     sql：传入要执行sql语句
        ///     orderField：排序字段
        ///     orderType：排序类型
        ///     pageIndex：当前页
        ///     pageSize：页大小
        ///     count：返回查询条数
        /// </summary>
        public DataTable GetPageList(string sql, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            return GetPageList(sql, null, orderField, orderType, pageIndex, pageSize, ref  count);
        }
        #endregion

　　例子：执行完程序后的SQL。

Select * From (Select ROW_NUMBER() Over (Order By order_id asc) As rowNum, * From (SELECT 
                            E.order_id,
                            E.order_no,
                            E.cust_no, 
                            E.cust_name, 
                            E.or_currency_Name,
                            E.or_ask_money,
                            E.or_sale_uname, 
                            E.or_date,
                            E.touching_uname,
                            E.touching_date,
                            E.or_check,
                            E.or_check_date, 
                            E.or_status,
                            E.or_remark,
                            E.change_uname, 
                            E.change_uid,
                            E.change_date,
                            E.aga_status,
                            Q.op_id,
                            Q.op_matno, 
                            Q.op_matname, 
                            Q.op_matStandard,
                            Q.op_unit,
                            Q.op_unit_id,
                            Q.op_quantity, 
                            Q.op_price,
                            Q.op_money, 
                            Q.op_delivery_date,
                            Q.IsStockNum,
                            Q.NotStockNum,
                            Q.op_remark,
                            Q.pro_code,
                            Q.aga_status as aga_status2,
                            D.dep_type,
							D.amount           
                            FROM YZOrder as E LEFT JOIN 
                            YZOrder_product as Q on e.order_no = Q.order_no LEFT JOIN 
                            YZ_Depot as D on q.op_matno= D.mat_no WHERE 1=1 AND E.order_no like '' + '%'  AND E.cust_name like '' + '%'  AND Q.op_matno like '' + '%'  AND Q.pro_code like '' + '%'  AND E.touching_date >= '2014/5/9 20:00:00'  AND E.touching_date <= '2014/5/16 20:00:00' ) As T ) As N Where rowNum > 0 And rowNum <= 20