Returning Multiple Result sets from stored procedure

[topry]

Using latest Q3 2011 build:

I've reviewed the online documentation on mapping a stored procedure with multiple result sets (http://www.telerik.com/help/openaccess-orm/developer-guide-crud-sp-support-map-multiple-result-sets.html ) 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 (http://www.telerik.com/help/openaccess-orm/openaccess-tasks-adonet-stored-procedures-multiple-result-set.html ).

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

Can you please clarify this point?

[Alexander]

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.

Greetings,
Alexander
the Telerik team

[Ryan]

Has Telerik done this?  Can we use stored procs that return multiple result sets?

[PetarP]

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,
Petar
the Telerik team

[Grant Drury-Green]

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

[PetarP]

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,
Petar
the Telerik team

[Miika]

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.

Regards,
Miika

[PetarP]

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,
Petar
the Telerik team

[Matt]

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. 

[Boyan]

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();
    reader.NextResult();
    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.

Regards,
Boyan
Telerik

 

 

[Kyle]

Link is gone. Is this feature implemented yet?

[Doroteya]

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.

Regards,
Doroteya
Telerik by Progress