Welcome to MSDN Blogs Sign in | Join | Help

Q&A: How to return multiple result sets from a stored procedure using LINQ

This one has come up for both LINQ to SQL and LINQ to Entities. The scenario is a reasonably common one. How to handle something like this:

CREATE PROCEDURE [dbo].[ReturnMultipleRS]
AS
      select * from customers
      select * from products

LINQ to SQL:

The solution is to add a method to the DataContext using a partial class. Thankfully the Swiss MSDN team have already explained this one in great detail. Enjoy.

LINQ to Entities:

The easiest approach is to download the ADO.NET Entity Framework Extensions which enables multiple result set support. The download is tiny but still includes a sample project which demonstrates (among other things) working with multiple result sets to return categories and products.

   1: /// <summary>
   2: /// Gets a category with the requested ID and populates related products using
   3: /// a single stored procedure call. The procedure returns multiple result sets:
   4: /// the first set returns the category and the second set returns related products.
   5: /// </summary>
   6: /// <param name="categoryID">Category ID.</param>
   7: /// <returns>Category with given ID or null if none is found.</returns>
   8: public Category GetCategoryAndRelatedProducts(int categoryID) {
   9:     DbCommand command = this.CreateStoreCommand("GetCategoryAndProducts", CommandType.StoredProcedure, new SqlParameter("cid", categoryID));
  10:     Category category;
  11:  
  12:     using (command.Connection.CreateConnectionScope())
  13:     using (DbDataReader reader = command.ExecuteReader()) {
  14:         // first result set includes the category
  15:         category = s_categoryMaterializer
  16:             .Materialize(reader)
  17:             .Bind(this.CategorySet)
  18:             .SingleOrDefault();
  19:  
  20:         // second result set includes the related products
  21:         if (null != category && reader.NextResult()) {
  22:             category.Products.Attach(s_productMaterializer
  23:                 .Materialize(reader)
  24:                 .Bind(this.ProductSet));
  25:         }
  26:     }
  27:  
  28:     return category;
  29: }
Published Monday, October 06, 2008 7:15 PM by Eric Nelson

Comments

# Q&amp;A: How to return multiple result sets from a stored procedure using LINQ : EasyCoded

Monday, October 06, 2008 2:34 PM by Q&amp;A: How to return multiple result sets from a stored procedure using LINQ : EasyCoded

PingBack from http://www.easycoded.com/qa-how-to-return-multiple-result-sets-from-a-stored-procedure-using-linq/

New Comments to this post are disabled
 
Page view tracker