April 2017
M T W T F S S
« May    
 12
3456789
10111213141516
17181920212223
24252627282930

Archives

Meta

Recent Posts

Tags

asp .net asp .net membership browsers computer crash Guy Burstein javascript link-o-rama LINQ office 2007 optimization outlook 2007 performance ScottGu SQL Server t-sql web servers workarounds xmlhttprequest

« 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):

  1. Create the multiple recordset stored procedure
  2. Drag the stored procedure into the DBML. Note, this will create the functions and classes for retrieving the first recordset.
  3. For each recordset, create the partial class needed based on the columns returned. Use the first recordset’s class as a guide.
  4. Now that you’ve got all your recordset classes, convert the function for the stored procedure into IMultipleResults
  5. 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!

Click on pen to Use a Highlighter on this page

Topics: I Canz Code! | 29 Comments »

29 Responses to “LINQ, Stored Procedures and Multiple Recordsets”

  1. LINQ Stored Procedures and Multiple Recordsets two geeks | Paid Surveys Says:
    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 [...]

  2. Mazhar Karimi Says:
    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.

  3. dcolumbus Says:
    November 25th, 2010 at 6:01 pm

    I’d really like to see an example of this… I’m currently fighting with the face that the generated code only handles one resultset. I have multiple resultsets and need to work with that information WITHOUT having tables in the design view. Just simply calling the stored procedure and displaying the results.

  4. juliana Says:
    November 25th, 2010 at 6:34 pm

    Hi dcolumbus, you could check out the follow-up post at http://twogeeks.mindchronicles.com/?p=28 where I expand (with some examples) on this method. Hope it helps!

  5. Tom Says:
    May 17th, 2011 at 9:13 pm

    Try this instead:

    https://tomperren.wordpress.com/2011/05/17/the-delights-of-sqlmetal-multiple-resultsets-from-a-stored-procedure/

    You can include it in the project build process and things go swimmingly – hopefully!

  6. ankit nandekar Says:
    February 28th, 2012 at 6:05 pm

    sir can you please tell what will be the coding when we are using join query in store procedure.

  7. lead generation Dallas Says:
    March 9th, 2015 at 2:16 pm

    Asking questions are genuinely pleasant thing if you are not understanding something totally, however this post offers fastidious understanding even.

  8. my advertising pays scam Says:
    March 9th, 2015 at 3:37 pm

    My family members all the time say that I am killing my time here
    at web, but I know I am getting familiarity every day by reading
    thes good articles or reviews.

  9. cash for junk cars philadelphia Says:
    March 10th, 2015 at 8:38 am

    Right away I am ready to do my breakfast, after having my breakfast coming over again to read further news.

  10. ling.ga Says:
    March 18th, 2015 at 2:59 pm

    It is not one of the more difficult instruments to learn, although none are very easy.

    The first in the line up is the Marqii, which is a much more desirable headphone than the Giiq, but it is a very good headphone also.
    There is nothing on the floor that is so interesting you can neglect a room full
    of people all looking in one direction, at you.

  11. ac Says:
    April 25th, 2015 at 2:27 pm

    ӏ used to be able tto find ǥood advice from your Ьlog posts.

  12. http://jammiestips.tumblr.com Says:
    April 27th, 2015 at 8:40 am

    Great web site. Plenty of useful information here. I’m sending it
    how to get and keep an erection (http://jammiestips.tumblr.com) a few friends ans additionally sharing in delicious.
    And obviously, thank you for your effort!

  13. fulltips.tumblr.com Says:
    April 27th, 2015 at 8:54 am

    I got this website from how to make my dick bigger naturally (fulltips.tumblr.com) friend who shared with me on the topic of this site and at the
    moment this time I am browsing this web site and reading
    very informative articles at this place.

  14. watch hot pursuit online Says:
    April 29th, 2015 at 4:32 pm

    I know it is possibly not quite realistic to drive a Lamborghini by way of sand dunes
    but the game is not based on realism in the 1st place.

  15. tube led t8 Says:
    May 12th, 2015 at 6:15 pm

    In Philips Fluorescent and Compact Fluorescent groups there
    are at least 38 bulb shapes. Though these lights are expensive it
    does not consume much power and lasts longer. Resource from here: LED Truck Trailer Lighting and LED Dome Lights.

  16. buty Says:
    May 14th, 2015 at 2:16 am

    Most of the girls are choosing the basic sheepskin slip shoes, as they are comfortable and stylish.
    Thread and stitching – thread used in stitching is always the same as the colour
    of the leather. Moccasin UGG slippers are quite popular among the men.

  17. watch aloha online Says:
    May 18th, 2015 at 2:39 am

    You will be capable to stream or Watch Aloha Complete Movie Streaming
    from your computer, tablet, Television or mobile device.

  18. www.facebook.com Says:
    May 24th, 2015 at 4:01 pm

    If some one needs to be updated with newest technologies afterward he must be pay a quick visit this site
    and be up to date all the time.

  19. Oakley Sunglasses Says:
    October 10th, 2015 at 7:47 pm

    one another. Be flexile when it is a cagy shopper.
    money grayness adornment defile-clear between wearings, sandwich
    the icepack in a bad hop. Now you can to prolong your
    income importantly. mention that much different
    physically sporty and constantly be moving movingto, the alter
    your unimpaired look. Cheap Jordans For Sale Coach Outlet Nike Free Run Oakley Sunglasses Wholesale Giuseppe Zanotti Sneakers Nike Free Run
    Michael Kors Handbags michael Kors Wallet Air Max Michael Kors Outlet Cheap Jordan Christian Louboutin Outlet
    Coach Handbags Outlet Oakley Sunglasses Outlet Cheap Oakley Sunglasses Giuseppe Zanotti Sneakers Cheap Oakley Sunglasses CHI Flat Iron CHI Flat Iron Website Cheap Ray Ban Sunglasses gucci handbags that wants
    your adornment if you make pocket-sized surfaces and distance pickings supplements passim the send doesn’t set up.
    Use what you determine to loosen up your person, go for field general!
    rest a mate of nonentity, cheap ballet footwear in the tips you read thetranslate
    bind downstairs to pick up how to

    Here is my webpage … Oakley Sunglasses

  20. www.facebook.com Says:
    November 25th, 2015 at 12:21 am

    Google and Div – X have partnered to allow Google Video to
    be accessible on all electronics devices, allowing consumers to take their movie
    and video downloads with them wherever they go.
    ll simply write about the cast or director and producer.
    What more movie addicts can demand for in this age of ever-rising
    prices.

  21. Shona Says:
    December 8th, 2015 at 4:20 am

    This format will enable the user to play and create high quality videos
    quickly and conveniently while maintaining the best quality possible.
    If you have landed on this web page, it means that you have got your destination. He was then called in the outer space because he was chosen by the ring.

  22. Deneen Says:
    December 10th, 2015 at 5:45 am

    You can also watch movies, which are licensed for downloading and viewing online.
    Asif included some interesting, but limited, footage taken from Formula 1 archives.
    The filmmakers make the most of the characters and settings without
    ever trying to teach us a lesson or make us cry.

  23. Noble Says:
    December 20th, 2015 at 3:19 am

    Now you have to connect your Mobile System on which yoou play Swamp Attack hackwith USB to yoour Laptop!

    My web-site; swamp attack hack tooll free download –
    Noble -

  24. http://cerrajerias-en-Barcelona.Com/author/jhakenneth1/ Says:
    January 5th, 2016 at 3:25 am

    If you sell items over the Internet, it is possible to ask the customer to pay you via Pay – Pal.

    To use any kind of credit or debit card with Pay – Pal, you need to
    link it for a Pay – Pal account. After an incident is
    made, Paypal puts a short lived hold for the seller’s Paypal account
    to the amount in dispute while reviewing the truth.
    The Pay – Pal Master – Card is not open to applicants away from United States.

    potential sellers are encouraged to link their e – Bay. paypal hong kong (http://cerrajerias-en-Barcelona.Com/author/jhakenneth1/) To
    use any kind of credit or debit card with Pay – Pal, you need to link it for a
    Pay – Pal account. After an incident is made, Paypal puts a short lived hold for the seller’s Paypal account to the amount in dispute while reviewing the truth.
    The Pay – Pal Master – Card is not open to applicants away
    from United States. potential sellers are encouraged to link their e – Bay.

  25. youtube.com Says:
    May 1st, 2016 at 2:13 am

    It’s an awesome post in support of all the internet people; they will obtain benefit from it I am sure.

  26. kitchen bridal shower invitations Says:
    July 11th, 2016 at 6:14 pm

    I’ve been riding for years and have ridden over 120k miles in the last 4 years, so I’m pretty Even experienced riders can fuck I was riding a 2013 Triumph Trophy SE at the This was not one but several wrecks in the 4 day period of the first leg of the 2015 Iron Butt Day 1, my GPS devices I need to take a shortcut down a BIA road in I’ve been on these roads before and they totally suck, but I’m on the clock so I decided to take it and turn around if it gets too 5 miles in and I’m looking for a spot to turn around, but I hit some deep ruts and dropped the bike at about 10mph before I get together Duct tape to the rescue and I’m back on the Get turned around and drop it twice more before getting out of the Finally get back to pavement, and only lost an Later that night in Wyoming, I was weaving my way through deer infested national forest area when a doe decided to change directions and run directly into She hit my turn signal and broke that, the mirror behind it, and ripped off my auxiliary light underneath She then smacked my saddle bag and broke that She died, but I didn’t go down, so more duct tape and I was back on the A day later day 3 in Kansas, I pulled over to a low shoulder, and should have just come to a complete stop and slowly reentered the road, but I I pulled back onto the road at about 10mph and the rear wheel caught, putting me into a whip, resulting in a high I landed on my forehead and broke off my face plate, ground up the top of my helmet, and scraped my nose But I thought I was out of the rally when I discovered I bent my shifter and couldn’t get my foot under Luckily, a guy stopped with a couple of pipe wrenches and we were able to get it bent back into usable A couple of modifications and more duct tape, and I was back on the Finally, on the last day of the first leg, I had collected my last bonus and was on my way to the I got bored with my audio book and looked down to switch the audio to my When I looked up, there was an SUV doing 65 in a 75mph I couldn’t get stopped and I rear ended I didn’t go down, so my only injury was putting my tooth through my Lots of blood but no real The bike however was I was able to ride the last 30 miles to the check point and score my first leg 4th place , but dropped out of the rally because I didn’t want to risk the next crash to be the one I can’t walk away I now have a 2016 Triumph Trophy SE and have been getting it ready for TLDR; dropped 3 times on BIA road, hit a deer, high sided, then rear ended a slow moving This story is actually published in the 2016 winter issue of Ride Texas "Wait till next Available at every Barnes and Noble in Texas on January

  27. Maryjo Says:
    July 18th, 2016 at 6:34 pm

    Awesome article it’s really useful.

    Also visit my web blog: sex addiction therapy
    - Maryjo,

  28. Catherina Roccio Says:
    August 20th, 2016 at 12:52 am

    Nearly all of the things you articulate happens to be supprisingly precise and that makes me ponder the reason why I had not looked at this with this light previously. Your piece truly did turn the light on for me personally as far as this subject matter goes. Nevertheless at this time there is actually one particular factor I am not too cozy with so whilst I try to reconcile that with the main idea of the position, allow me see exactly what the rest of your readers have to point out.Very well done.

  29. AdrianHStebe Says:
    August 31st, 2016 at 10:00 pm

    Hi there, You’ve done a great job. I will definitely digg it and personally
    suggest to my friends. I’m sure they’ll be benefited from this
    site.

    Feel free to surf to my page – AdrianHStebe

Comments