CODEWITHSUNDEEP
c#sql-serverado.net
Huma Ali
Huma Ali

Reputation: 1809

How to get rows and count of rows simultaneously

I have a stored procedure that returns rows and count simultaneously.

I tried following ADO.net code but I get IndexOutOfRange Exception on ItemCount(ItemCount contains count of rows)

 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;
 }

The Stored Procedure:

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

Is it because its returning two tables? What's the solution?

Upvotes: 1

Views: 80

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You have to use NextResult if you return multiple recordsets:

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;
    }
}

But in my opinion it would be better to include the total-count in the first query. Then it's simpler and you don't need the final loop. The database can cope with such sub-queries. The optimizer will ensure that it gets executed only once if it's not a correlated subquery.

Upvotes: 4

Owen Pauling
Owen Pauling

Reputation: 11861

Check the column name that is being returned by your stored procedure. It is probably typoed or something other than "ItemCount".

As per the documentation for SqlDataReader.Item Property, and IndexOutOfRange exception is thrown in the case where no column with the specified name was found.

Upvotes: 0

Related Questions