How to get rows and count of rows simultaneously <p>I have a stored procedure that returns <code>rows</code> and <code>count</code> simultaneously.</p> <p>I tried following <code>ADO.net</code> code but I get <code>IndexOutOfRange</code> Exception on ItemCount(ItemCount contains <code>count</code> of rows)</p> <pre><code> public List&lt;Product&gt; GetProductDetails(Product p) { List&lt;Product&gt; products = new List&lt;Product&gt;(); 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; } </code></pre> <p>The Stored Procedure:</p> <pre><code>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 &gt;= @ristartIndex AND RowNumber &lt; (@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 </code></pre> <p>Is it because its returning two tables? What's the solution?</p>