Open the OrderDB.edmx file in you project. This assumes you
created a web application as described in Part
1 of this series. When you open the OrderDB.edmx file the entities will
appear in the designer and the Model Browser window will show you the database
with the Entities, Complex Types, and Associations listed in a folder
structure. Right click on the OrderDB.edmx node in the Model Browser and
select Update Model From Database… from the pop-up menu. The Update Wizard
should appear and it should recognize the five stored procedures added in step
1.
Check the box next to the Stored Procedure node to check all
the stored procedures and then click the Finish button. The stored procedures
will now appear under the Stored Procedures in the Model Browser.
Right click on the UserAccounts_SelectAll stored procedure
in the Model Browser and select Add Function Import… from the pop-up menu. The
Add Function Import dialog should appear. This allows you to add a method to
the OrdersDBContainer class that will execute this stored procedure.
You need to choose the return type when the stored procedure
is executed. It would be nice if you could return a list of UserAccount
entities but complex type properties aren't supported by the framework. To get
around this issue you need to create a new complex type that represents the
return set from the stored procedure. Since the SelectAll and SelectById
stored procedures both return the same fields we'll create a single complex
type that both return.
Click the Get Column Information button. This will
determine the fields that are returned by the stored procedure. Once the
fields are listed, click on the Create New Complex Type button. When you click
the button the Complex option will automatically be selected and the name of
the complex type that will be created is added to the drop down list next to
the Complex type option. By default the name of the complex type is the name
of the stored procedure with the word Result appended to it. Since we'll share
this complex type with two stored procedures we'll make the name generic.
Change the name to UserAccounts_Select_Result. Now click the OK button.
You should see the UserAccounts_SelectAll function under the
Function Imports folder in the Model Browser. You should also see the new
UserAccounts_Select_Result complex type under the Complex Types node.
Now right click on the UserAccounts_SelectById procedure in
the Model Browser. Select Add Function Import again and select Complex type for
the return type and select the UserAccounts_Select_Result complex type. Click
the OK button.
Now you need to associate the Insert, Update, and Delete stored
procedures with the UserAccount Entity. To do this you should right click on
the UserAccount entity in the designer. Select Stored Procedure Mapping from
the pop-up menu. You should see Mapping Details window.
Click on <Select Insert Function> from the first row
and select UserAccounts_Insert from the drop down list. The list of parameters
will appear in the grid. You need to map the property from the entity to the
parameter in the stored procedure. Since this procedure returns the Id of the
newly added record you need to define which property will be set by the return
value of the procedure. Under "Result Column Bindings" enter Id
where it displays <Add Result Binding>. It is important that your stored
procedure defined above contains the line SELECT SCOPE_IDENTITY() AS Id. If
you don't it won't work. The Mapping Details should look like the following
image.
Now you need to associate the Update stored procedure. Set
the <Select Update Function> to the UserAccounts_Update stored procedure.
You'll have to manually map the AuditFields_UpdateDate to the
AuditFields.UpdateDate property.
The last step is to map the Delete stored procedure. Set
the <Select Delete Function> to the UserAccounts_Delete stored procedure.
The Id parameter should be mapped correctly because it is named the same as the
property.
Save your project at this time. The OrdersDBContainer now
"knows" about the stored procedures and they are mapped to the
UserAccount entity to allow for insert, update, and delete.