This example shows how to use a stored procedure that executes inside itself a parameterized SELECT query to tables and returns table records that satisfy the query condition as the list of corresponding complex type objects.
The script for creating a stored procedure and a table for the SQL Server DBMS is as follows:
CREATE TABLE dbo.Department (
DeptNo int NOT NULL,
DName varchar(14),
Loc varchar(13),
PRIMARY KEY (DeptNo)
)
GO
CREATE TABLE dbo.Employee (
EmployeeID int IDENTITY PRIMARY KEY,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(10) NOT NULL,
Address nvarchar(60),
City nvarchar(15),
Country nvarchar(15),
DeptNO int NOT NULL,
FOREIGN KEY (DeptNO) REFERENCES dbo.Department(DeptNO)
)
GO
CREATE PROCEDURE dbo.SelectDeptsAndEmps
AS
SELECT *
FROM dbo.Department;
SELECT *
FROM dbo.Employee;
GO
First, we create a model and drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click Yes to obtain metadata of procedure resultsets.
As a result we get 2 complex types with default names SelectDeptsAndEmpResult and SelectDeptsAndEmpResult1 and a method corresponding to the stored procedure. For convenience, we rename the complex types to DeptsResult and EmpsResult and get the following method:
As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:
C#:
public SelectDeptsAndEmpMultipleResult SelectDeptsAndEmp ()
Visual Basic:
Public Function SelectDeptsAndEmp () As SelectDeptsAndEmpMultipleResult
If we need to retrieve the whole tables, we do the following: we drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click No not to obtain metadata of procedure result set; then we drag the required tables from the Database Explorer window to the design area.
Then we double-click the method and in the Value Types drop-down list select the Department and Employee tables:
As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:
C#:
public SelectDeptsAndEmpMultipleResult SelectDeptsAndEmp ()
Visual Basic:
Public Function SelectDeptsAndEmp () As SelectDeptsAndEmpMultipleResult
Complex types and tables can be combined in one method.
|
In the case of multiple resultsets, result type mapping is not supported and objects are mapped sequentially. If automatic sequential mapping is wrong, it has to be fixed manually in the code. |
