« ScottGu – Da LINQ Man! | Main | Maintenance Plans/Backup Jobs in SQL Server »
LINQ, Stored Procedures and Multiple Recordsets
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
AS
--first set
SELECT value1 FROM table1
--second set
SELECT t1.value1, t2.value2
FROM table1 t1 LEFT JOIN
table2 t2 on t1.value1 = t2.value2
GO
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!
Topics: I Canz Code! | 2 Comments »
Use the Highlighter
June 4th, 2009 at 4:28 am
[...] LINQ Stored Procedures and Multiple Recordsets two geeks Posted by root 1 day 14 hours ago (http://twogeeks.mindchronicles.com) May 15 2008 i wanted to use a linq with a stored procedure that would return multiple recordsets like this i found scottgu linq to sql part 6 retrieving data using stored in the comment trail someone asks about multiple tables and two geeks is proudly pow Discuss | Bury | News | LINQ Stored Procedures and Multiple Recordsets two geeks [...]
June 5th, 2009 at 3:20 pm
Right, but when the resultset is empty, converting to toList() throws an exception, so need to check it first.
I think there is a problem with dbml, if you create a stored procedure returning multiple results, but you also do some logics before returning them, for example declaring a variable of some type (Note: you are not returning that variable, it is being used internally). But when you will drop that SP into the dbml Designer, dbml will create ISingleResult with the compatible type of that variable which you created in that SP.
I solved that problem by opening dbm into XML editor, manually addming multiple elements. and saved it. Now when I opened dbml again in visual studio, it automatically created addition classes in designer.cs(with the same elements names which i created), and change the ISingleResult with IMultipleResult.