LINQ to SQL and multiple result sets in Stored Procedures

  • Article

In this post I'm going to demonstrate how you would return and consume multiple result sets from a stored procedure in LINQ to SQL.

Imagine you have a stored procedure like this one below. Very simple – it’s just returns all customers and all orders.

clip_image001

In my LINQ to SQL Data Context, I have the following tables.

clip_image002

As well as the stored procedure definition.

clip_image003

Simple enough so far!

The first thing we need to do is create a partial class and define a new method returning type ‘IMultipleResults’. I’ve defined the one below to accept one parameter, which is a customerId.

clip_image004

You’ll notice that there’s a few attributes you need to add.

1. Function – this is the name of the stored procedure that will be called. It’s the same one we defined in our data context above. Note: the actual stored procedure name is irrelevant - it must be the name you have given the stored  procedure on the data context (which will be the same 99% of the time I’d imagine.)

2. ResultType – This is the mapping that basically says “Make the first result type of ‘Customer’ and the second result type of ‘Order’”.

Once we’ve defined this method, we can go ahead and consume it from our UI and/or OM as shown below.

clip_image005

As shown above, you call the method you defined, passing in a customer id in this case, which returns you a type of IMultipleResults. From there it’s just a case of calling the ‘GetResult’ method making sure to pass in the type of the object you want.

The results are shown below!

image

I hope this helps!

Jason

Comments

  • Anonymous
    May 06, 2008
    PingBack from http://www.travel-hilarity.com/travel-airline-tickets/?p=1147

  • Anonymous
    May 06, 2008
    Jason, what if we want to return multiple resultsets that are not typed? like anonymous types? like for example: select pr.Title, pr.Description from products select o.orderid, u.userid from orders o inner join users u on o.userid = u.userid

  • Anonymous
    July 21, 2009
    If you get errors like 'Procedure expects parameter <@Name&gt;, which was not supplied: Take a look at the stored procedure code created in the Designer.cs file, and copy the parameter list and attributes that match them to the SP params: [Parameter(Name="CustomerID", DbType="Int")] When passing multiple parameters to ExecuteMethodCall, you don't need to create an object array, just list each parameter. Can you skip over one of the results, or is each result set returned in order?

  • Anonymous
    November 13, 2009
    Hi!!           I have understand you requierment but here I am having a query that in in combination of another table and returns 2 tables one is having same structure of one of the table of database and other is having just only one field now i want to convert in to dataset how can i do this please help me ?            I am having following error when converting and the 2nd table have the field which is not acctually in the table so how can it be done ? [Error] The required column 'UserId' does not exist in the results. [/Error] Regards, Milnd Kansagara

  • Anonymous
    December 15, 2009
    Thank you! This post helped me a lot! :-)

  • Anonymous
    September 30, 2010
    Jason, what if we want to return multiple resultsets that are not typed? like anonymous types? for example: select * from EmployeeDetails inner join              Department on EmployeeDetails.DeptID=Department.DeptID

  • Anonymous
    January 25, 2011
    I think this is good when you have one customer selected. What if you want to return multiple customers like WHERE CustomerID < 500 the results come independent and you can't call for each Customer->Orders.

  • Anonymous
    April 06, 2012
    How can it be resolved  if both results are from the same table means if the queries would be like this, 1 : Select c., o. From Customers c join Orders o on c.CustomerId = o.CustomerID 2 : Select * From Customers In this case what we do?? and more issue with results.GetResult<>

  • Anonymous
    August 04, 2014
    Please help me. I get this error "does no contain a definition for ExecuteMethodCall....." I use these namespaces : using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Reflection; using System.Data.Linq; using System.Data.Linq.Mapping; what should  I do?