通用分页方法

       public static DataSet QueryByPage(string connectionString, string SQLString, SqlParameter[] cmdParms, string sortPara, out int total, int pageIndex = 1, int PageSize = 10, string sortRule = "asc")
        {
            total = 0;
            var sqlPagebuilder = new StringBuilder(@"select * from (
                                                        select *, ROW_NUMBER() over(order by ");
            if (string.IsNullOrWhiteSpace(sortPara) || sortRule == null)
                throw new ArgumentNullException();
            sqlPagebuilder.Append(sortPara);
            sqlPagebuilder.Append(" ");
            sqlPagebuilder.Append(sortRule);
            sqlPagebuilder.Append(" ) as rownumber FROM ( ");
            sqlPagebuilder.Append(SQLString);
            sqlPagebuilder.Append(" ) as TabByPage1 ");
            sqlPagebuilder.Append(" ) as TabByPage2 where rownumber between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize; ");
            //获取数据总数
            sqlPagebuilder.AppendLine("");
            sqlPagebuilder.AppendLine(@"select COUNT(1) as Total from ( ");
            sqlPagebuilder.Append(SQLString);
            sqlPagebuilder.Append(" ) as TabByTotalCount");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, connection, null, sqlPagebuilder.ToString(), cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                    cmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");

                        total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }