By juliana | May 15, 2008
Update: This post has a follow up here: LINQ, Stored Procedures and Multiple Recordsets: How-To
I wanted to use a LINQ with a stored procedure that would return multiple recordsets, like this:
CREATE PROCEDURE spReturnMultiple
SELECT value1 FROM table1
SELECT t1.value1, t2.value2
FROM table1 t1 LEFT JOIN
table2 t2 on t1.value1 = t2.value2
I found ScottGu’s LINQ to SQL (Part 6 – Retrieving Data Using Stored Procedures) article and Guy Burstein’s Linq to SQL Stored Procedures with Multiple Results – IMultipleResults article that pointed me into the right direction, but not QUITE all the way.
ScottGu’s article showed how you can use a SPROC to return different types of recordset (usually due to an IF statement within the SPROC) and how IMultipleResults were used; in the comment trail someone asks about multiple tables and ScottGu said it could be done in the same way, calling GetResult twice. Burstein’s article showed exactly how you did this. My problem was that the stored procedures returned TABLES. However, my results are based on my own SELECT statements, so there was a little bit of extra that had to be done.
HOW TO (overview):
- Create the multiple recordset stored procedure
- Drag the stored procedure into the DBML. Note, this will create the functions and classes for retrieving the first recordset.
- For each recordset, create the partial class needed based on the columns returned. Use the first recordset’s class as a guide.
- Now that you’ve got all your recordset classes, convert the function for the stored procedure into IMultipleResults
- In the application layer, call the stored procedure and use GetResults (and the recordset class) to access the multiple results.Self-note: had to convert the results ToList() in order to use it/iterate later,etc.
I’ll come back here and illustrate each step later. Need to finish up that page now that I’ve got access to my multiple results!