none
Loading multiple shapes via a stored proc? RRS feed

  • Question

  • Hi Folks,

        I've read that you can return multiple recordsets (shapes) via a stored proc and the Linq 2 Sql can pick it up fine.

    ScottGu first highlighted this to me, via this post: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    And here's another one: http://blogs.msdn.com/dinesh.kulkarni/archive/2008/05/16/linq-to-sql-tips-7.aspx

     

    the key here is the IMultipleResults.

     

    Ok. so that all makes sence.

     

    BUT .. is it possible to have a stored proc return two recordsets where the 2nd recordset is a child to the first one? For example, if we use the Northwind DB example....every Category has 0 or more products.

     

    So if we have a stored proc that return all the data for a category and all the products for that one category, both the category entity and the products aggregation list are populated.

     

    eg pseduo sql syntax:-

     

    Code Snippet

    SELECT [t0].*, [t1].*

    FROM Categories t0 INNER JOIN Products t1

    WHERE CategoryID = 1

     

     

     

    This will mean we have a single category instance with the products aggregation (child) populated with a list of products, for this category.

     

    Is this possible to do with one sql round trip? or do we need to make a second roundtrip for each aggregation? Alternatively, i've thought of having the stored proc return the parent ID's .. and then do a second roundtrip, using lots of DataLoadOptions and a CONTAINS where clause to grab the heirachy/all the data.

     

    thoughts? cheers!

    Tuesday, July 8, 2008 12:33 AM

Answers

  • Your SQL syntax there will not return two sets but rather one combined set. You can use that but it won't map directly to your entities.

     

    You would need to change this to:

     

    SELECT * FROM Categories WHERE CategoryID = 1

    SELECT * FROM Products WHERE CategoryID = 1

     

    And then you can map the IMultipleResults with Categories and Products to have both result sets automatically mapped to these entities.

     

    [)amien

    Wednesday, August 6, 2008 1:49 AM
    Moderator

All replies

  • Your SQL syntax there will not return two sets but rather one combined set. You can use that but it won't map directly to your entities.

     

    You would need to change this to:

     

    SELECT * FROM Categories WHERE CategoryID = 1

    SELECT * FROM Products WHERE CategoryID = 1

     

    And then you can map the IMultipleResults with Categories and Products to have both result sets automatically mapped to these entities.

     

    [)amien

    Wednesday, August 6, 2008 1:49 AM
    Moderator
  •  

    Hi [)amien.

     

    when u return two sets and map the IMultipleResults to both object types ... each set is individual and unique. how can u define a relationship between then automatically?

     

    so with the example SQL above... the category set will prolly contain 1 result. the products set will contain multiple results (it's assumed).

     

    but is there a relationship with the Category.Products property ... that the data are mapped?

    Wednesday, August 6, 2008 7:13 AM
  • Data returned from stored procedures via ISingleResult/IMultipleResults have a number of restrictions and not taking part in associations is one of them.

     

    [)amien

    Wednesday, August 6, 2008 7:55 PM
    Moderator