Using Stored Procedures
The Entity Framework lets you specify that it should use stored procedures for database access. For any entity type, you can specify a stored procedure to use for creating, updating, or deleting entities of that type. Then in the data model you can add references to stored procedures that you can use to perform tasks such as retrieving sets of entities.
Using stored procedures is a common requirement for database access. In some cases a database administrator may require that all database access go through stored procedures for security reasons. In other cases you may want to build business logic into some of the processes that the Entity Framework uses when it updates the database. For example, whenever an entity is deleted you might want to copy it to an archive database. Or whenever a row is updated you might want to write a row to a logging table that records who made the change. You can perform these kinds of tasks in a stored procedure that’s called whenever the Entity Framework deletes an entity or updates an entity.
As in the previous tutorial, you’ll not create any new pages. Instead, you’ll change the way the Entity Framework accesses the database for some of the pages you already created.
In this tutorial you’ll create stored procedures in the database for inserting
Instructor entities. You’ll add them to the data model, and you’ll specify that the Entity Framework should use them for adding
Instructor entities to the database. You’ll also create a stored procedure that you can use to retrieve
Creating Stored Procedures in the Database
(If you’re using the School.mdf file from the project available for download with this tutorial, you can skip this section because the stored procedures already exist.)
In Server Explorer, expand School.mdf, right-click Stored Procedures, and select Add New Stored Procedure.
Copy the following SQL statements and paste them into the stored procedure window, replacing the skeleton stored procedure.
CREATE PROCEDURE [dbo].[InsertStudent] @LastName nvarchar(50), @FirstName nvarchar(50), @EnrollmentDate datetime AS INSERT INTO dbo.Person (LastName, FirstName, EnrollmentDate) VALUES (@LastName, @FirstName, @EnrollmentDate); SELECT SCOPE_IDENTITY() as NewPersonID;
Student entities have four properties:
EnrollmentDate. The database generates the ID value automatically, and the stored procedure accepts parameters for the other three. The stored procedure returns the value of the new row’s record key so that the Entity Framework can keep track of that in the version of the entity it keeps in memory.
Save and close the stored procedure window.
InsertInstructor stored procedure in the same manner, using the following SQL statements:
CREATE PROCEDURE [dbo].[InsertInstructor] @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime AS INSERT INTO dbo.Person (LastName, FirstName, HireDate) VALUES (@LastName, @FirstName, @HireDate); SELECT SCOPE_IDENTITY() as NewPersonID;
Update stored procedures for
Instructor entities also. (The database already has a
DeletePerson stored procedure which will work for both
CREATE PROCEDURE [dbo].[UpdateStudent] @PersonID int, @LastName nvarchar(50), @FirstName nvarchar(50), @EnrollmentDate datetime AS UPDATE Person SET LastName=@LastName, FirstName=@FirstName, EnrollmentDate=@EnrollmentDate WHERE PersonID=@PersonID; CREATE PROCEDURE [dbo].[UpdateInstructor] @PersonID int, @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime AS UPDATE Person SET LastName=@LastName, FirstName=@FirstName, HireDate=@HireDate WHERE PersonID=@PersonID;
In this tutorial you’ll map all three functions — insert, update, and delete — for each entity type. The Entity Framework version 4 allows you to map just one or two of these functions to stored procedures without mapping the others, with one exception: if you map the update function but not the delete function, the Entity Framework will throw an exception when you attempt to delete an entity. In the Entity Framework version 3.5, you did not have this much flexibility in mapping stored procedures: if you mapped one function you were required to map all three.
To create a stored procedure that reads rather than updates data, create one that selects all
Course entities, using the following SQL statements:
CREATE PROCEDURE [dbo].[GetCourses] AS SELECT CourseID, Title, Credits, DepartmentID FROM dbo.Course
Adding the Stored Procedures to the Data Model
The stored procedures are now defined in the database, but they must be added to the data model to make them available to the Entity Framework. Open SchoolModel.edmx, right-click the design surface, and select Update Model from Database. In the Add tab of the Choose Your Database Objects dialog box, expand Stored Procedures, select the newly created stored procedures and the
DeletePerson stored procedure, and then click Finish.
Mapping the Stored Procedures
In the data model designer, right-click the
Student entity and select Stored Procedure Mapping.
The Mapping Details window appears, in which you can specify stored procedures that the Entity Framework should use for inserting, updating, and deleting entities of this type.
Set the Insert function to InsertStudent. The window shows a list of stored procedure parameters, each of which must be mapped to an entity property. Two of these are mapped automatically because the names are the same. There’s no entity property named
FirstName, so you must manually select
FirstMidName from a drop-down list that shows available entity properties. (This is because you changed the name of the
FirstName property to
FirstMidName in the first tutorial.)
In the same Mapping Details window, map the
Update function to the
UpdateStudent stored procedure (make sure you specify
FirstMidName as the parameter value for
FirstName, as you did for the
Insert stored procedure) and the
Delete function to the
DeletePerson stored procedure.
Follow the same procedure to map the insert, update, and delete stored procedures for instructors to the
For stored procedures that read rather than update data, you use the Model Browser window to map the stored procedure to the entity type it returns. In the data model designer, right-click the design surface and select Model Browser. Open the SchoolModel.Store node and then open the Stored Procedures node. Then right-click the
GetCourses stored procedure and select Add Function Import.
In the Add Function Import dialog box, under Returns a Collection Of select Entities, and then select
Course as the entity type returned. When you’re done, click OK. Save and close the .edmx file.
Using Insert, Update, and Delete Stored Procedures
Stored procedures to insert, update, and delete data are used by the Entity Framework automatically after you’ve added them to the data model and mapped them to the appropriate entities. You can now run the StudentsAdd.aspx page, and every time you create a new student, the Entity Framework will use the
InsertStudent stored procedure to add the new row to the
Run the Students.aspx page and the new student appears in the list.
Change the name to verify that the update function works, and then delete the student to verify that the delete function works.
Using Select Stored Procedures
The Entity Framework does not automatically run stored procedures such as
GetCourses, and you cannot use them with the
EntityDataSource control. To use them, you call them from code.
Open the InstructorsCourses.aspx.cs file. The
PopulateDropDownLists method uses a LINQ-to-Entities query to retrieve all course entities so that it can loop through the list and determine which ones an instructor is assigned to and which ones are unassigned:
var allCourses = (from c in context.Courses select c).ToList();
Replace this with the following code:
var allCourses = context.GetCourses();
The page now uses the
GetCourses stored procedure to retrieve the list of all courses. Run the page to verify that it works as it did before.
(Navigation properties of entities retrieved by a stored procedure might not be automatically populated with the data related to those entities, depending on
ObjectContext default settings. For more information, see Loading Related Objects in the MSDN Library.)
In the next tutorial, you’ll learn how to use Dynamic Data functionality to make it easier to program and test data formatting and validation rules. Instead of specifying on each web page rules such as data format strings and whether or not a field is required, you can specify such rules in data model metadata and they’re automatically applied on every page.