Sunday, July 12, 2009

Call a Multiple-Rowset stored procedure with LINQ to SQL and without the designer

by sweisfeld via Shawn Weisfeld [MVP] on 7/12/2009 4:45:23 PM

Multiple-Rowset allows you to return more than one select from your stored procedure. For example in the stored procedure below you can see we have 2 select statements.

As we learned in my last 2 posts it is possible to use LINQ to SQL without the designer.

• A simple select with LINQ to SQL and without the designer (http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2009/07/11/a-simple-select-with-linq-to-sql-and-without-the-designer.aspx)
• Call a stored procedure with LINQ to SQL and without the designer (http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2009/07/11/call-a-stored-procedure-with-linq-to-sql-and-without-the-designer.aspx)

To utilize Multiple-Rowset we just need to tweak our function in our data context a little.

Here you can see that we added 2 result type attributes. These map to our result sets. Since our stored procedure returns 2 result sets of customers we tell LINQ to SQL to mach each to the customer object. However if your stored procedure returns different types you can of course do that. We also changed our result type to that of IMultipleResults. This will allow us to pick off each of the result sets when we call our method.

Here is the call to the method. You can see when we call “GetResult” the first time we get our first result set. Then when we call it a second time we get the second result set.

That is it.