可返回记录总数的SQL2005,SQL2012通用分页方法

    #region SQL2012的分页(返回总记录数)
    public static DataSet GetPagingDataSetBySql2012(string tableName, string queryFields, string orderField, int pageIndex, int pageSize, string strWhere, out int totalCount)
    {
      if (orderField.Trim().Length <= 0)
      {
        throw new NoNullAllowedException();
      }
      if (queryFields.Trim().Length <= 0)
      {
        queryFields = "*";
      }
      totalCount = 0;
      var sql = new StringBuilder();
      sql.AppendFormat(" SELECT {0},COUNT(*) OVER(PARTITION BY '') AS Total", queryFields);
      sql.AppendFormat(" FROM {0}", tableName);
      if (strWhere.Trim().Length > 0)
      {
        sql.AppendFormat(" WHERE {0}", strWhere);
      }
      sql.AppendFormat(" ORDER BY {0}", orderField);
      sql.AppendFormat(" OFFSET {0} * {1} ROWS", pageIndex, pageSize);
      sql.AppendFormat(" FETCH NEXT {0} ROWS ONLY;", pageSize);
      var ds = DbHelperSQL.Query(sql.ToString());
      if (ds != null && ds.Tables[0].Rows.Count > 0)
      {
        var c = ds.Tables[0].Columns.Count;
        totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][c - 1]);
      }
      return ds;
    }
    #endregion

    #region SQL2005的分页(返回总记录数)
    public static DataSet GetPagingDataSetBySql2005(string tableName, string queryFields, string orderField, int pageIndex, int pageSize, string strWhere, out int totalCount)
    {
      if (orderField.Trim().Length <= 0)
      {
        throw new NoNullAllowedException();
      }
      if (queryFields.Trim().Length <= 0)
      {
        queryFields = "*";
      }
      totalCount = 0;
      /*
       WITH TEMP AS 
        (
	        SELECT ROW_NUMBER() OVER(ORDER BY BID) AS TOTAL,* FROM BlogList 
        )
        SELECT * FROM TEMP --WHERE TOTAL BETWEEN 1 AND 5 
       */
      /*WITH TEMP AS (SELECT ROW_NUMBER() OVER(ORDER BY IsSync DESC, BID) AS TOTAL,* FROM BlogList )SELECT *,(SELECT COUNT(*) FROM TEMP) AS TOTALROWS FROM TEMP WHERE TOTAL BETWEEN 1 AND 2  */
      var sql = new StringBuilder();
      sql.AppendFormat("WITH TEMP AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS TOTAL,{1} FROM {2}) SELECT *,,(SELECT COUNT(*) FROM TEMP) AS TOTALROWS FROM TEMP ", orderField, queryFields, tableName);
      sql.AppendFormat("WHERE TOTAL BETWEEN {0} AND {1}", (pageIndex * pageSize) + 1, pageSize * (pageIndex + 1));
      if (strWhere.Trim().Length > 0)
      {
        sql.AppendFormat(" AND ({0})", strWhere);
      }

      var ds = DbHelperSQL.Query(sql.ToString());
      if (ds != null && ds.Tables[0].Rows.Count > 0)
      {
        var c = ds.Tables[0].Columns.Count;
        totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][c - 1]);
      }
      return ds;
    }
    #endregion