Adding Stored Procedures for Insert, Update, Delete

If you want to use stored procedures for inserting, updating, and deleting entities, here are some guidelines you should know:

No random parameters - Every stored procedure input parameter must line up with a property in the entity
Associations to Entity References - Associations to other entities require input parameters on all three functions (insert, update, delete).This usually means you have to add meaningless, unused parameters to the delete stored procedure simply to satisfy the Entity Data Model.

Your first step is to add your insert, update, and delete stored procedures to the Entity Data Model. To do this, right-click on the design surface of the Entity Data Model and select Update Model from Database... from the shortcut menu. This launches the Update Wizard. Go to the Add tab and select the three (3) stored procedures you want to add, then click the Finish button:

Note: You will not see any difference in the Entity Data Model. However, behind the scenes, the stored procedures have been added to the SSDL section of the model file as function elements. You have to perform another step to implement the stored procedures so they can be used from your business object code.
Right-click the background of the Entity Data Model's design surface and select Model Browser from the shortcut menu. Expand the Stored Procedures node and you should see the stored procedure you just added:
Now you need to wire up the new functions to an entity. To do this, go to the Entity Data Model design surface and select the entity for which you want to use the insert, update, and delete stored procedures. In the Mapping Details window, click the bottom button in the upper left hand corner (it's tool tip says Map Entity to Functions):
Click the first item, Select Insert Function. This displays an arrow to the right you can click to select an Insert function. After you select the function, the designer displays its best guess at a mapping between the stored procedure parameters and the entity properties (if they are named exactly the same, then it's obviously much easier):

Note: If any parameters are unmapped, you can manually select a corresponding entity property from the associated Property drop down list. If you have to map the parameter for one function, you will normally have to map it in the other functions too. If you don't, you will get compiler Error 2037.

If you are mapping stored procedures to an Entity that has relationships and corresponding navigation properties defined, you need to select the "extended" entity property as shown here for the Order entity's EmployeeID:
Notice the Employee.EmployeeID property was selecting for this mapping. Also note that you need to do something similar for the Order entity's ShipVia mapping.
Beneath the Result Column Bindings node, is a node labeled <Add Result Binding>. This allows you to retreive new database-generated primary key values when inserting new entities into the database. For this to work, your Insert stored procedure must return the new primary key value. Insert stored procedures generated by the Business Layer Generator automatically add the following SQL (for SQL Server stored procedures) to the end of the Insert stored procedure (when you have the Entity Framework checkbox and Refresh DataSet checkbox selected):
```
SELECT SCOPE_IDENTITY() AS NewID WHERE @@ROWCOUNT > 0;
```
Notice the new identity column value is returned as NewID. To store this value back into the primary key of the entity, you can specify the following Result Column Binding:
Next, select the Update function to be used for the entity, following the instructions listed in the previous step.
Finally, select the Delete function to be used for the entity, following the instructions listed in the previous step.
Note: If the entity you are mapping stored procedures to has relationships and associated navigation properties, you need to add each navigation property as a parameter to the Delete stored procedure--even though they are not used. This is simply to satisfy the Entity Data Model (apparently this won't be required in the next release of the Entity Framework). So, for example, the Delete stored procedures for the Order entity might look something like this:
```
ALTER PROCEDURE [dbo].[OrdersDelete]
```
```
(
```
```
    @OrderID int,
```
```
    @ShipVia int,
```
```
    @EmployeeID int
```
```
)
```
```
AS
```
```
    SET NOCOUNT OFF;
```
```
    DELETE FROM [dbo].[Orders]
```
```
    WHERE 
```
```
        ([OrderID] = @OrderID)
```
This makes the mapping look like this:

Now that these functions have been mapped to the entity, whenever you call SaveEntity() these functions will automatically be used to perform inserts, updates, and deletes.