Data Accessors for multiple resultsets

Topics: Data Access Application Block, Enterprise Library Core
Sep 18, 2010 at 9:32 AM


I am using Data Accessor to read data from stored procedures and to return as Objects.
One of the challange is... I have an procedure this returns multiple result sets, say Orders and Products in same SP call.

How can I read these 2 results into two object collections.

Please guide me. A code snippet would be helpful for better understanding.

Pradeep Y.

Sep 20, 2010 at 2:21 AM
Edited Sep 20, 2010 at 2:48 AM

You can't directly return 2 collections with a single call to executing a sproc accessor.  However, an approach I could think of is to create an entity that will hold each of the collection you want.  For e.g.

public class ProductOrders
        public List Products { get; set; }

        public List Orders { get; set; }

You would then create your own implementation of IResultSetMapper that will iterate over an IDataReader and convert each row to an instance of Product and Order and add it to the corresponding collection property of the instance of ProductOrder:

public class ProductOrdersResultMapper : IResultSetMapper
        public IEnumerable MapSet(IDataReader reader)
            List productOrders = new List();
            ProductOrders prodOrders = new ProductOrders();
            prodOrders.Orders = new List();
            prodOrders.Products = new List();

            while (reader.Read())
                Product prod = new Product();
                prod.ID = int.Parse(reader["ID"].ToString());
                //map other properties of Product

                Order order = new Order();
                order.Quantity = int.Parse(reader["OrderID"].ToString());
                //map other properties of Order



            return productOrders;


In your call to ExecuteSprocAccessor, you'll only make use then of the first item in the list of ProductOrders:

ProductOrders productOrders = db.ExecuteSprocAccessor<ProductOrders>("sp_GetProductOrders", new ProductOrdersResultMapper()).FirstOrDefault();

Hope this helps.

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.