This sample demonstrates how to map a stored procedure that returns multiple result sets and create a domain method for each result set.
To complete this walkthrough, you will need to create a new domain model based on the SofiaCarRental database.
Suppose, you have the following stored procedure (see the code-snippet below). It is based on the SofiaCarRental database. The procedure takes no parameters, returns data from both the Categories and Cars tables, and returns all RentalOrders. The procedure returns two result sets. The first one aggregates data from two tables and the result will be mapped to a Complex Type. The second result set will contain all RentalOrders. The SofiaCarRental domain model contains a RentalOrder persistent class. You will map the second result set by using the Persistent Class option.
CREATE PROCEDURE GetCarInfoAndOrders AS BEGIN SELECT car.CarID, car.Make, car.Model, category.CategoryName FROM Cars car JOIN Categories category ON car.CategoryID = category.CategoryID SELECT * FROM RentalOrders END GO
If you have already generated a domain model, you could include the ChangeOrderStatus stored procedure by using the Update From Database Wizard.
Follow the same steps as in the How to: Create a Domain Method for a Stored Procedure topic. Except this time:
- In the Domain Method Editor, click the Retrieve Result Shape button to retrieve the schema information about the result returned by the stored procedure.
In the Result Sets drop-down, there should be two entries - ResultSet0 and ResultSet1.
The first result set (ResultSet0) will be mapped to a Complex Type. Select ResultSet0 from the drop-down and click on Create Complex Type to create a compatible CLR type.
- Rename the generated complex type or use the default name.
Change the Domain Method Name to GetCarInfo.
Click OK to generate the domain method for the first result set. The new GetCarInfo method will be added to your context class. Additionally, a new CLR type will be generated.
- The next step is to generate a domain method for the second result set. Re-run the Domain Method Editor for the GetCarInfoAndOrders stored procedure.
- Again, click the Retrieve Result Shape button to retrieve the schema information about the result returned by the stored procedure.
From the Result Sets drop-down, select the second result set (ResultSet1).
Select the Persistent Type option. From the available drop-down, you need to select the specific return type. In this demo, this is RentalOrder.
Change the Domain Method Name to GetRentalOrders.
Click OK to generate the domain method for the first result set. The new GetRentalOrders method will be added to your context class.
- Save your RLINQ file.