C#连接sqlserver分页查询的两个简单的方法

 /// <summary>
        /// 分页查询函数
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="Params">参数</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">排序放</param>
        /// <returns>备注：查询效率偏低</returns>
        public DataTable Pagination(string connStr, string strSql, List<SqlParameter> Params, int pageSize, int pageIndex, string order, string sort)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            cmd.Connection = conn;
            string sqlFinal = string.Format(@"WITH tmp2 as( SELECT ROW_NUMBER() OVER(ORDER BY {1} {2})
                   AS rownum,* from ({0}) tmp1)
                   select  (select count(*) from tmp2)total,* from tmp2 where tmp2.rownum BETWEEN
                   @startRow and @endRow", strSql, order, sort);

            Params.Add(new SqlParameter("@startRow", (pageIndex - 1) * pageSize + 1) { SqlDbType = SqlDbType.Int });
            Params.Add(new SqlParameter("@endRow", pageIndex * pageSize) { SqlDbType = SqlDbType.Int });
            cmd.CommandText = sqlFinal;
            for (int i = 0; i < Params.Count; i++)
            {
                cmd.Parameters.Add(Params[i]);
            }
            ada.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 分页查询函数
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="Params">参数</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">排序放</param>
        /// <returns>备注：查询效率高，但是会建临时表</returns>
        public DataTable Pagination2(string connStr, string strSql, List<SqlParameter> Params, int pageSize, int pageIndex, string order, string sort)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            cmd.Connection = conn;
            string sqlFinal = string.Format(@"SELECT ROW_NUMBER() OVER(ORDER BY {1} {2})
                   AS rownum,* into #tmp2 from ({0}) tmp1;
                   select  (select count(*) from #tmp2)total,* from #tmp2 where #tmp2.rownum BETWEEN
                   @startRow and @endRow", strSql, order, sort);
            Params.Add(new SqlParameter("@startRow", (pageIndex - 1) * pageSize + 1) { SqlDbType = SqlDbType.Int });
            Params.Add(new SqlParameter("@endRow", pageIndex * pageSize) { SqlDbType = SqlDbType.Int });
            cmd.CommandText = sqlFinal;
            for (int i = 0; i < Params.Count; i++)
            {
                cmd.Parameters.Add(Params[i]);
            }
            ada.Fill(dt);
            return dt;
        }