如何同时获取行和行计数

我有一个同时返回rows和count的存储过程。

我尝试按照ADO.net代码进行操作，但我在项目计数上遇到异常IndexOutOfRange（项目计数包含count行）

 public List<Product> GetProductDetails(Product p)
 {
    List<Product> products = new List<Product>();
    using (SqlConnection con = new SqlConnection(_connectionString))
    {
      SqlCommand cmd = new SqlCommand("[usp_Get_ServerPagedProducts]", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@rvcName", System.Data.SqlDbType.VarChar).Value = p.Name;
      cmd.Parameters.AddWithValue("@rvcCode", System.Data.SqlDbType.VarChar).Value = p.Code;
      cmd.Parameters.AddWithValue("@riProductTypeID", System.Data.SqlDbType.Int).Value = p.ProductTypeID;
      cmd.Parameters.AddWithValue("@ristartIndex", System.Data.SqlDbType.Int).Value = p.RowIndex;
      cmd.Parameters.AddWithValue("@rimaxRows", System.Data.SqlDbType.Int).Value = p.PageSize;
      con.Open();
      using (SqlDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
           Product product = new Product(reader["Name"].ToString(), reader["Code"].ToString(), reader["Description"].ToString(), (DateTime)reader["DateCreated"], Convert.ToInt32(reader["ProductID"]), Convert.ToInt32(reader["ProductTypeID"]), reader["ProductTypeName"].ToString(), Convert.ToInt32(reader["ItemCount"]));
           products.Add(product);
        }
      }
    }     
    return products;
 }

存储过程：

SELECT ProductID,
       Name,
       [Description],
       Code,
       DateCreated,
       ProductTypeID,
       ProductTypeName
FROM (
    SELECT P.pkProductID AS ProductID,
           P.Name AS Name,
           P.[Description] AS [Description],
           P.Code AS Code,
           P.DateCreated AS DateCreated,
           P.fkProductTypeID AS ProductTypeID,
           PT.Name AS ProductTypeName,
           ROW_NUMBER() OVER (ORDER BY P.pkProductID) AS RowNumber
    FROM Product P
    INNER JOIN ProductType PT ON PT.pkProductTypeID = P.fkProductTypeID
    WHERE P.Name LIKE '%' + @rvcName + '%'
        AND P.Code LIKE '%' + @rvcCode + '%'
        AND (@riProductTypeID = 0 OR P.fkProductTypeID = @riProductTypeID)
) AS tblProduct
WHERE RowNumber >= @ristartIndex
    AND RowNumber < (@ristartIndex + @rimaxRows)
SELECT COUNT(*) AS ItemCount
FROM (
    SELECT P.pkProductID,
           P.Name,
           P.[Description],
           P.Code,
           P.DateCreated,
           P.fkProductTypeID AS 'ProductTypeID',
           PT.Name AS 'ProductTypeName',
           ROW_NUMBER() OVER (ORDER BY P.Name DESC) AS RowNumber
    FROM Product P
    INNER JOIN ProductType PT ON PT.pkProductTypeID = P.fkProductTypeID
    WHERE P.Name LIKE '%' + @rvcName + '%'
        AND P.Code LIKE '%' + @rvcCode + '%'
        AND (@riProductTypeID = 0 OR P.fkProductTypeID = @riProductTypeID)
) AS TotalCount

是因为它返回两个表吗？解决方案是什么？

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Product product = new Product();
        product.Name = reader["Name"].ToString();
        product.Code = reader["Code"].ToString();
        // ...
        products.Add(product);
    }
    if (reader.NextResult() && reader.Read())
    {
        int itemCount = reader.GetInt32(reader.GetOrdinal("ItemCount"));
        foreach(Product p in products)
            p.ItemCount = itemCount;
    }
}