高性能分页查询示例：简单易用

SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID desc) RowNumber,* FROM (select * from BtxCMS_Message) t1 WHERE  1 = 1) t WHERE   RowNumber BETWEEN 1 
  AND  10;select count(1) from (select * from BtxCMS_Message) as a




        #region ·ÖÒ³²éѯ
        public static DataSet ExecutePageDataSet(string sql, string order, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            if (order == null || order == "")
            {
                order = "ID desc";
            }
            string psql = string.Format(@" SELECT  *
                                        FROM    (
                                                 SELECT ROW_NUMBER() OVER (ORDER BY {1}) RowNumber,*
                                                 FROM   (
                                                         {0}
                                                        ) t1
                                                 WHERE  1 = 1 
                                                ) t
                                        WHERE   RowNumber BETWEEN @RowNumber_Begin
                                                          AND     @RowNumber_End;select count(1) from ({0}) as a", sql, order);

            List<SqlParameter> paramlist = new List<SqlParameter>()
            {
                new SqlParameter("@RowNumber_Begin", SqlDbType.Int){ Value = (pageIndex - 1) * pageSize + 1 },
                new SqlParameter("@RowNumber_End", SqlDbType.Int){ Value = pageIndex * pageSize }
            };
            if (parms != null) paramlist.AddRange(parms);