Stored Procedure Mapping

This topic describes how to specify insert, update and delete stored procedures for an entity type.

For the purpose of the following example we use the Employee class and three stored procedures: EmployeesInsert, EmployeesUpdate and EmployeesDelete.

The code of the EmployeeInsert procedure for MS SQL Server is as follows:

CREATE procedure dbo.EmployeesInsert (
           @LastName nvarchar(20), @FirstName nvarchar(10), @Title nvarchar(30),
           @TitleOfCourtesy nvarchar(25), @BirthDate datetime, @HireDate datetime,
           @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15),
           @PostalCode nvarchar(10), @Country nvarchar(15), @HomePhone nvarchar(24),
           @Extension nvarchar(4), @Photo image, @Notes ntext, @PhotoPath nvarchar(255))
as
BEGIN
INSERT INTO [dbo].[Employees]
           ([LastName] ,[FirstName] ,[Title]
           ,[TitleOfCourtesy] ,[BirthDate] ,[HireDate]
           ,[Address] ,[City] ,[Region]
           ,[PostalCode] ,[Country] ,[HomePhone]
           ,[Extension] ,[Photo] ,[Notes] ,[PhotoPath])
     VALUES
           (@LastName, @FirstName, @Title,
           @TitleOfCourtesy, @BirthDate, @HireDate,
           @Address, @City, @Region,
           @PostalCode, @Country, @HomePhone,
           @Extension, @Photo, @Notes, @PhotoPath);
  SELECT @@IDENTITY as EmployeesIDENTITY
END

Code of the EmployeeUpdate and EmployeeDelete procedures is of no importance for this example.

The following sequence of actions illustrates the process of mapping of stored procedures residing in the storage part to CRUD operations on the Employee entity residing in the conceptual part of the model.

To map the stored procedures to CRUD operations on the entity, do the following:

1.	Right-click the Employee entity either in the diagram area or in the Model Explorer window and choose Stored Procedure Mapping from the popup menu. The Stored Procedure Mapping dialog box is displayed.

In the Commands area of the dialog box select Insert and in the Mapping area use the Storage Procedure drop-down list to select the required EmployeesInsert stored procedure. Columns having values that will be assigned to properties, must be present in the resultset returned by this function, so we use the Result Column Bindings options to set the required mapping:

o	In the Column field of the Result Column Bindings grid we enter the name of the parameter returned by the EmployeesInsert stored procedure, which in this case is EmployeesIDENTITY.

o	In the Property field of the Result Column Bindings grid we enter the name of the corresponding entity property, which is EmployeeID.

3.	Select the Update command and set the corresponding EmployeesUpdate procedure in the Storage Procedure drop-down list.

4.	Select the Delete command and set the corresponding EmployeesDelete procedure in the Storage Procedure drop-down list. Now the Stored Procedure Mapping dialog box displays all the required mapping:

Images_stored-procedure-mapping-EF

5.	Click OK to finish the procedure.

The stored procedures are now mapped to the insert, update, and delete operations on the Employee class.

Stored Procedure Mapping

See Also