How to use accessors when stored procedure return multiple resultsets

Topics: Data Access Application Block
Dec 19, 2014 at 10:19 PM
Hi,

Could someone please provide a sample on how to map multiple resultsets returned from a stored procedure using Enterprise Library accessors. We also need to create a parent-child relation between these entities.

The documentation says it is possible by implementing the interface IResultSetMapper but there are no sample examples of how to do it.

Thanks
Dec 23, 2014 at 12:58 AM
The IResultMapper interface looks like:
public interface IResultSetMapper<TResult>
{
  IEnumerable<TResult> MapSet(IDataReader reader);
}
Basically you need to create a class that implements the interface and internally process an IDataReader to return an IEnumerable of whatever type you are creating.

For example:
public class ProductResultMapper : IResultSetMapper<Product>
{
        public IEnumerable<Product> MapSet(IDataReader reader)
        {
            List<Product> products = new List<Product>();

            while (reader.Read())
            {
                Product prod = new Product();
                prod.ID = int.Parse(reader["ProdID"].ToString());
                prod.ProdName = reader["ProdName"].ToString();
                prod.Price = double.Parse(reader["Price"].ToString());

                products.Products.Add(prod);
            }

            return products;
        }
}
One use case that is fairly typical is to create an object with a parent/child relationship. That could look something like this (assuming that multiple results are returned):
public class OrderResultMapper : IResultSetMapper<Order>
{
    public IEnumerable<Order> MapSet(IDataReader reader)
    {
        List<Order> orders = new List<Order>();
        Order order = null;

        while (reader.Read())
        {
            order = new Order();
            order.Id = reader.GetInt32(0);
            // etc.
            orders.Add(order);
        }

        reader.NextResult();

        while (reader.Read())
        {
            int orderId = reader.GetInt32(0);
                
            order = orders.Single(o => o.Id == orderId);

            OrderItem item = new OrderItem();
            item.Id = reader.GetInt32(1);
            // etc.
            order.Items.Add(item);
        }

        return orders;
    }
}

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to