Entity Framework 6 Recipes 2nd Edition (10-8), entityrecipes

Entity Framework 6 Recipes 2nd Edition (10-8), entityrecipes

10-8. Map insert, modify, and delete operations to stored procedures

Problem

You want to map the insert, modify, and delete operations to the stored procedure.

Solution

Suppose there is an athlete entity model, as shown in Figure 10-8. The corresponding database table is shown in Figure 10-9. We want to use the stored procedure to perform the insert, modify, and delete operations.

 

Figure 10-8.Athlete entity model

 

Figure 10-9.Athletes table containing some basic information

To insert, modify, or delete an object ing stored procedure, perform the following operations:

1. Create a stored procedure in the database, as shown in Listing 10-21.

Listing 10-21.The Stored Procedures for the Insert, Update, and Delete Actions

Create procedure [chapter10]. [InsertAthlete]

(@ Name varchar (50), @ Height int, @ Weight int)

As

Begin

Insert into Chapter10.Athlete values (@ Name, @ Height, @ Weight)

Select SCOPE_IDENTITY () as AthleteId

End

Go

 

Create procedure [chapter10]. [UpdateAthlete]

(@ AthleteId int, @ Name varchar (50), @ Height int, @ Weight int)

As

Begin

Update Chapter10.Athlete set Name = @ Name, Height = @ Height, [Weight] = @ Weight

Where AthleteId = @ AthleteId

End

Go

 

Create procedure [chapter10]. [DeleteAthlete]

(@ AthleteId int)

As

Begin

Delete from Chapter10.Athlete where AthleteId = @ AthleteId

End

2. Right-click the model design view, select "update model from database", select the three stored procedures created above, and click "finish" to add the stored procedure to the model.

3. right-click the Athlete object and select "Stored Procedure ing" to select the corresponding stored procedure for each operation. bind "AthleteId" to the "result column" of the insert operation (see Figure 10-10 ).

 

Figure 10-10. ing Stored Procedure, Parameter, and operation, modification, and deletion after the return value

How does it work?

We updated the model with the stored procedure so that the model can use the stored procedure, and then mapped the stored procedure to the insert, modify, and delete operations of the object.

In this section, we try to make the stored procedure as simple as possible. they get parameters from object properties and perform operations. in the insert operation, we need to return the stored procedure to return the ID value of the auto-incrementing column to the object. therefore, it is important to map the Id value returned by the stored procedure to the AthleteId attribute of the object. without this step, EF cannot obtain the ID of the newly created object.

You may ask, "When will I map a stored procedure to an object operation ?", In most cases, EF automatically generates efficient code for insert, modify, and delete operations.

You may also want to know: "When do I need to map stored procedures ?", There is a best practice for the answer to this question:

When your company asks you to use a stored procedure to insert, modify, or delete a table.

You have an additional task that requires additional operations. For example, you want to manage an audit operation, execute some complicated business logic, or perform a permission security check on a user.

Your object is based on the query view (see Chapter 2 and Chapter 15)

Static void Main (string [] args)

{

Using (var context = new EFRecipesEntities1008 ())

{

Context. Database. ExecuteSqlCommand ("delete from chapter10.Athlete ");

Context. Athletes. Add (new Athlete

{

Name = "Nancy Steward ",

Height = 167,

Weight = 53

});

Context. Athletes. Add (new Athlete

{

Name = "Rob Achers ",

Height = 170,

Weight = 77

});

Context. Athletes. Add (new Athlete

{

Name = "Chuck Sanders ",

Height = 171,

Weight = 82

});

Context. Athletes. Add (new Athlete

{

Name = "Nancy Rodgers ",

Height = 166,

Weight = 59

});

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities1008 ())

{

Var all = context. Athletes;

Context. Athletes. Remove (all. First (o => o. Name = "Nancy Steward "));

All. First (o => o. Name = "Rob Achers"). Weight = 80;

Context. SaveChanges ();

}

 

Using (var context = new EFRecipesEntities1008 ())

{

Console. WriteLine ("All Athletes ");

Console. WriteLine ("============== ");

Foreach (var athlete in context. Athletes)

{

Console. WriteLine ("{0} weighs {1} Kg and is {2} cm in height ",

Athlete. Name, athlete. Weight, athlete. Height );

}

}

Console. WriteLine ("\ nPress any key to exit ...");

Console. ReadKey ();

}

The output result is as follows:

All Athletes

================

Rob Achers weighs 80Kg and is 170 in height

Chuck Sanders weighs 82Kg and is 171 in height

Nancy Rodgers weighs 59Kg and is 166 in height

 

 

Appendix: script file of the database used in the Creation example