« Maintenance Plans/Backup Jobs in SQL Server | Main | “The file is too large for the destination file system.” …say what? »
LINQ, Stored Procedures and Multiple Recordsets: How-To
By juliana | June 7, 2008
Okay, here’s my entire test-run of how to get LINQ to work with stored procedures that return multiple recordsets just a dummy project.
Here’s a multiple recordset-returning stored procedure. My first recordset returns a three columns, single row of overview data, and my second recordset returns the list/breakdown.
CREATE PROCEDURE spGetBookList
AS
BEGIN
-- first recordset - overview of the list
SELECT count(*) as totalbooks,
sum(pages) as totalpages,
sum(price) as totalprice
from book_list
– second recordset – the book list
select id, title, author, pages, price, published_date
from book_list
END
GO
I want to use this through LINQ, so first, I’ve got to add it to the LINQ dbml file.
1. Drag and drop in the designer as usual.
The stored procedure will appear on the stored procedure list.
The designer generates two things automatically whenever you drag a stored procedure. The function that you can use (in your coding) to call this stored procedure , and then the class that represents the data.
You can see this if you take a look at the .vb bile of the dbml file (expand the + sign on the dbml and double-click on the .vb file.).
And scroll down ’til you see a function that’s similar in name to the stored procedure you added.
Note:
- Public Function spGetBookList()
- Class spGetBookListResult
As far as I can tell, this is what always happens when we drag a stored procedure to the designer. A function with the same name as the stored procedure will be created, and a class representing our recordset. Even though our stored procedure here returns two recordsets, the designer is only capable of detecting and designing one recordset result.
Here’s where I guess we have to go in and tweak it so that this function returns TWO recordset.
2. Create a new class.
This class is going to contain the data classes that’ll represent the multiple recordsets in our stored procedure.
At first I created these classes INSIDE the DBML’s .vb file, but I discovered I would lose all my tweaks/changes whenever I refreshed the DBML designer (e.g. adding a new table/stored procedure), since it generates that file automatically.
This is a pain here, but an important one: everytime the DBML is changed, it re-generates. That means, that some of the steps here will have to be re-done every time. More on that later. For now, though, this class will help reduce the number of steps that’ll have to be re-done.
3. Move the partial class to the new class
3a. Insert Imports System.Data.Linq.Mapping to class
3b. Cut and paste the partial public class spGetBookListResult from the .vb and paste into this new class.
4. Create another partial class for the second recordset
Basically this class has all the properties that’ll represent all the columns in the second recordset. Since my second statement was
select id, title, author, pages, price, published_date
from book_list
My class had to have properties id (integer), title (string), author (string), pages (integer), price (decimal), published_date (datetime). Basically, you can pretty much copied the same format as the first recordset’s partial class and do something like this:
CHEAT: To be honest, I don’t create most of my second-onwards recordset classes manually. What I do is I create a dummy stored procedure and put the single select statement into the stored procedure, drag it to the DBML, open the .vb, and tada, “steal” the auto-generated class for that select statement and put it in my own data class. And then I repeat as necessary, and when I’m done, I delete my dummy stored procedure from the DBML.
Remember the name of this class!
5. Go back to the DBML .vb, find the function, and tweak it so that it returns the multiple recordset instead of one.
To do this, you need to change this
to this
Basically, the changes are:
- Replace the As ISingleResult(Of <whateverclass) with IMultipleResults
- Replace the function code with the one above Dim result …. Return Ctype(result.Returnvalue,IMultipleResults)
- Add the ResultType attributes in order, with the GetType value referring to the class you just created to represent each recordset.
6. Copy this entire function, paste into the data class, and comment out. JUST FOR REFERENCE.
ANNNNNND, we’re done!
Oh, except on how to actually CALL this thing. Which would be something like this:
The results variable basically holds all the multiple recordsets. To access each one, you need to call the GetResult (in order), which is sort of like NextRecordset in good ol’ ASP. The Of <your recordset class> allows you to do all those nice .<fieldname> intellisense and such.
I had to convert the results to ToList in order to iterate back and forth, but this may or may not be necessary in your case. Try with and without, YMMV.
Oh, one more thing…
Remember I said whenever you made a change to the DBML designer, the whole designer’s vb file gets regenerated? The IMultiple result function will ALSO disappear to be replaced by a single result one (and its data class). That’s where the copied Imultiple function in your own data class comes in handy. Just copy and replace and all will be well again.
AND FIIIIINALLY…
I get the feeling this is a bit hack-ish, especially the part where we’re going into the designer –that’s auto-generated– and then tweaking. Not sure if that’s the official proper way to do it… I look forward to the day when the designer will be a little bit more intuitive, generating the IMultiple result function and necessary classes itself. I would also love it if the designer could automatically REFRESH. (Ever notice that when you change a stored procedure after dragging it into the designer, you’ve got to remove and re-add?)
The End.
Topics: I Canz Code! | 9 Comments »
Use the Highlighter
June 7th, 2008 at 9:03 pm
[...] LINQ, Stored Procedures and Multiple Recordsets: How-To [...]
June 8th, 2008 at 7:05 pm
Great job. I, too, would like to see these changes in the next version of the designer. Until then, I think your method is the way to go.
July 7th, 2008 at 11:01 pm
Hi there
I tried the example. I am facing a problem. Suppose am returning 2 record sets from the SP in the order First Country and second State. When i have to access State its not returning me the values properly. When i debugged i found out that the order of the method call is important. To get the state list i have to call Country first and then call State. Other wise its not filling my State list.
For some reason the order is causing the problem. Have you encountered anything like this. Any idea why this is happening?
July 29th, 2008 at 1:59 am
Great Job, just a question….
It’s the same with “If statements” ???
CREATE PROCEDURE spGetBookList
AS
BEGIN
– first recordset – overview of the list
if(miguelito > 1)
begin
SELECT count(*) as totalbooks,
sum(pages) as totalpages,
sum(price) as totalprice
from book_list
end
else
– second recordset – the book list
select id, title, author, pages, price, published_date
from book_list
end
END
GO
Note: Miguelito its a var….
September 5th, 2008 at 10:20 pm
@Suba, I’m not very sure, but I believe it works like the old ASP .NextRecordset, where the results are returned sequentially.
For instance, your stored procedure returns results from Country first, and then State.
I don’t think you can call your results
.GetResult(Of State)
.GetResult(Of Country)
The first .GetResult will retrieve the first results (e.g. Country), but is trying to put it into your State class.. which probably won’t work.
I suggest that you grab all your results in order first (and store them in a variable), and then you can access them in whatever order you need.
September 5th, 2008 at 10:28 pm
@Alex, yes it will work for IF statements, too. But you will need to know before you call your stored procedure, which result type you will get. And then based on that knowledge, you put in your result type when calling GetResult.
For instance, if your miguelito var is outside in your code then you can have something like this:
dim miguelito as integer = 1
dim result as IMultipleResult = myLinq.spGetBookList(miguelito)
if miguelito > 1 then
dim overview= result.getResult(Of StatsClass)().ToList
else
dim booklist = result.getResult(of BookListClass)().ToList
end if
June 23rd, 2009 at 3:59 pm
one of the best code snippets.It gives the clear understanding of what is going around in Linq.
Thanks a lot
June 23rd, 2009 at 5:15 pm
Thanks juliana for commenting on if statement in this issue.but my problem is that if have stored procedure like
Create Procedure dbo.uspStudentFetch
(
@id int
)
as
Begin
if(@id=1)
Begin
select * from tblStudent1
End
else
Begin
select * from tblStudent2
End
End
Here in this case do i need to specify 2 result types or only one.if one which one to be specified.however only one datatable would be return.
The aforementioned tables(student1 and student2) are having different fields.so do i need to have two partial classes associated with the aforementioned tables with different tables, Even though only one table is return dynamically.
Thanks in advance
joseph jelasker fernando
June 24th, 2009 at 12:03 pm
@joseph,
Thanks for the comment! For your scenario, you are still getting a multiple type of results, if only just one of the two at any one time. I notice that you’re selecting directly from a table — did you know tables already ahve their own result types, so that you don’t really have to declare them?
You will still need to do step 5:
1. change the function and tweak it so that it returns multiple results, ResultType(GetType(tblStudent1)) and ResultType(GetType(tblStudent2) .
But when you call, depending on your variable, you will only GetResult(Of [whateverTable]).
Go check out Scott Gu’s article on “Handling Multiple Result Shapes from SPROCs” (just search for that header in the article): http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
He outlines the exact same scenario you’re describing, and he was one of the sources I used to come to my solution (of multiple recordsets).