I'll show two ways to use stored procedures for updating data using the Entity Framework; first I'll show the "built-in" way and then I'll show my preferred way using Extensions (provided by Microsoft).
Entity Framework with .NET 3.5 and VS 2008 SP1
To easily configure your model to use stored procedures right click on the Entity and select "Stored Procedure Mapping".
You'll then click to the right of the <Select Insert Function> and point it to the stored procedure that you want to use (all imported stored procedures will show in the list). Do the same for the Update and Delete functions.
Note: During compile VS complained because I didn't have an Insert function (my Update stored procedure handles the uniqueness constraints internally; it does both insert and update). I had to create a stub stored procedure and update my model to make it happy
Once your stored procedures are mapped you can do the following to add and commit changes
[TestMethod]
public void User_CanAddUserToTable()
{
User usr = new User();
usr.ID = -1;
usr.FirstName = "Jane";
usr.LastName = "Doe";
usr.LoginName = "JaneDoe";
db.AddToUser(usr);
db.SaveChanges(true);
}
I ran the test a second time to ensure my stored procedure was being run; I knew it was because I didn't have a duplicate record; I have built in uniqueness constraints on LoginName - inserts become updates if LoginName already exist.
Updating Entity Framework using EF Extensions
I won't duplicate what was a very comprehensive blog HERE which has download link. Once I followed the steps provided I was able to update my Unit Test to look as follows:
[TestMethod]
public void User_CanAddUserToTable()
{
object[] parameters =
{
new SqlParameter("ID",-1),
new SqlParameter("FirstName","John"),
new SqlParameter("LastName","Doe"),
new SqlParameter("LoginName","JohnDoe")
};
DbCommand command = db.CreateStoreCommand("uspUser_Update",
CommandType.StoredProcedure, parameters);
using (db.Connection.CreateConnectionScope())
{
int retValue = (int)command.ExecuteScalar();
}
}
Tags:
entity framework,
stored procedures
Categories: