Returning Multiple Result sets from stored procedure

Thread is closed for posting
12 posts, 1 answers
  1. topry
    topry avatar
    32 posts
    Member since:
    Oct 2008

    Posted 28 Dec 2011 Link to this post

    Using latest Q3 2011 build:

    I've reviewed the online documentation on mapping a stored procedure with multiple result sets ( ) and can create the domain mappings as indicated.

    What I cannot tell is if the new version supports returning multiple result sets via a single method call or if that is only supported via the API as mentioned here ( ).

    I also viewed the short video on Stored procedures but did not see it mentioned there.

    Can you please clarify this point?
  2. Answer
    Alexander avatar
    727 posts

    Posted 30 Dec 2011 Link to this post

    Hello topry,

    Unfortunately the Stored Procedure Editor does not support yet generation of single methods returning multiple result sets. This is at the top of our TODO list and we will try to implement it for Q1 2012.

    the Telerik team

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  3. Ryan
    Ryan avatar
    27 posts
    Member since:
    Aug 2010

    Posted 15 Mar 2012 Link to this post

    Has Telerik done this?  Can we use stored procs that return multiple result sets?
  4. PetarP
    PetarP avatar
    754 posts

    Posted 16 Mar 2012 Link to this post

    Hello Ryan,

     Currently we support returning multiple result sets only through separate function calls. So for example if you have a stored procedure that returns two result sets (say Customers and Employees) you will have to execute two methods of the context - one for obtaining the Customers and one for obtaining the Employees.

    All the best,
    the Telerik team
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
  5. Grant Drury-Green
    Grant Drury-Green avatar
    23 posts
    Member since:
    Jul 2009

    Posted 03 Nov 2012 Link to this post

    Is there still a plan to implement multiple results from a single call?

    I have a stored proc which is relatively heavy, having to call it twice to get the 2 result sets is a big performance issue
  6. PetarP
    PetarP avatar
    754 posts

    Posted 05 Nov 2012 Link to this post

    Hello Grant,

     Yes this is still in our plans and its priority is quite high. We would like to implement it either for the next Q or the Q after.
    Will that work for you? 
    Please note that it is still possible to manually write the code for executing the stored procedure in a single call and use it instead of the generated one.

    All the best,
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  7. Miika
    Miika avatar
    28 posts
    Member since:
    Jun 2012

    Posted 25 Apr 2013 Link to this post

    Hi there,

    Any news yet on whether/when this feature is implemented? As with the previous poster, I have a heavy stored procedure with multiple result sets, so would prefer to only call it once. If this feature is still far off, will have to write manual object mapping.

  8. PetarP
    PetarP avatar
    754 posts

    Posted 30 Apr 2013 Link to this post

    Hello Miika,

     We are yet to deliver that feature to our product. I am afraid that the much needed refactoring of our code generation templates was deemed more important and thus we decided to go with that first. Once we are done with that we will definitely provide support for this feature as well.
    For the time being it should be possible for you to add a partial class to our context and to define a method there that is using our GetReader method to translate all the result sets manually. You will still need to write a few lines of code but you will get the POCO classes and the reader generation automatically.

    Kind regards,
    the Telerik team
    Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>
  9. Matt
    Matt avatar
    3 posts
    Member since:
    Apr 2013

    Posted 05 Dec 2013 Link to this post

    Could you provide an example of working with multiple result sets on a single stored procedure call? 

    Retrieving multiple data sets with one call to a stored procedure would be extremely beneficial to a project we are working on. 
  10. Boyan
    Boyan avatar
    100 posts

    Posted 09 Dec 2013 Link to this post

    Hello Matt,

    This documentation article describes an approach to map multiple results sets – each result set is mapped to different method. In order to retrieve all result sets in single call you could define a partial class to our context and manually translate and combine all result sets. Find enclosed a short sample solution which demonstrates this approach by expanding the example provided in the documentation article.
    I would like to bring to your attention the following key points:
    1. The class CarsAndOrdersMultipleResultSet (CarsAndOrdersMultipleResultSet.cs) represents the multiple result set to be returned by the stored procedure.
    2. I defined partial class to our context – located in file SofiaCarsModel.partial.cs.
    3. I defined a method (public CarsAndOrdersMultipleResultSet GetCarInfoAndOrders() – line 12) in the partial class to map the stored procedure that returns multiple result set.
    4. All result sets were combined in an instance of CarsAndOrdersMultipleResultSet using the GetReaderForGetCarInfoAndOrders method (in file CarsAndOrdersMultipleResultSet.cs, lines 22 through 27).
    using (OADataReader reader = this.GetReaderForGetCarInfoAndOrders(oaConnection, out parameterReturnValue))
        result.CarInfoResultSet = this.Translate<CarInfo>(reader).ToList();
        result.OrderResultSet = this.Translate<RentalOrder>(reader).ToList();
    Please note that the method GetReaderForGetCarInfoAndOrders is auto generated  so you need to map the stored procedure before using this approach. 

    Do not hesitate to contact us if you have any further question.

    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  11. Kyle
    Kyle avatar
    15 posts
    Member since:
    Mar 2016

    Posted 09 Aug 2016 in reply to Boyan Link to this post

    Link is gone. Is this feature implemented yet?
  12. Doroteya
    Doroteya avatar
    498 posts

    Posted 10 Aug 2016 Link to this post

    Hello Kyle,

    Thank you for reporting the missing links. They are fixed at the moment.

    In any case, this is the article to which they should point: How to: Map a Stored Procedure Returning Multiple Result Sets.

    Telerik by Progress
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top