MVC5 Entity Framework Learning to implement basic CRUD functionality

In the previous article, we created an MVC application using the entity Framework and SQL Server LocalDB, and used it to store and display data. In this article, you will modify the CRUD (create, read, update, delete) code that is automatically created by the MVC framework.

Note: Typically, we create an abstraction layer between the controller and the data access layer to implement the warehousing model, and in order to focus attention on how to use the Entity Framework, there is no storage mode used here.

In this article, you create a Web page:

1. Create a Details page

The students index page generated by the framework code does not take into account the enrollments property because the property is a collection. In the details page, we will display the contents of the collection in the HTML table.

Opening Controllers\studentcontroller.cs, you can see the details method for the details view using the Find method to retrieve a single student entity:

Public ActionResult Details (int? id) {    if (id = = null)    {        return new Httpstatuscoderesult ( httpstatuscode.badrequest);    }    Student Student = db. Students.find (ID);    if (student = = null)    {        return httpnotfound ();    }    Return View (student);}

The ID parameter of the details method comes from the details link in the index page, called Route data.

Routing data is data that is specified in the routing table, passed through a URL, and received by the model binder. As shown below, the default route specifies the controller, action, and ID

Routes. MapRoute (    name: "Default",    URL: "{controller}/{action}/{id}",    defaults:new {controller = "Home", Action = "Index", id = urlparameter.optional});

In the following URL, the default route maps instructor to controller, the index map to action, and 1 to the ID

http://localhost:1230/Instructor/Index/1?courseID=2021

"? courseid=2021" is a query string, and if you use the ID as a query string, the model binder can parse it correctly

http://localhost:1230/Instructor/Index?id=1&CourseID=2021

In the Razor view, the URL is created by the ActionLink statement, as the ID parameter in the following code matches the default route, so the ID is taken as the route by the data

@Html. ActionLink ("Select", "Index", new {id = Item. PersonID  })

The CourseID parameter does not match the default route in the following code, so CourseID is used as the query string

Open views\student\details.cshtml, and each field uses the Displayfor helper to display the data, as shown in the following code:

<dt>    @Html. displaynamefor (model = model. LastName) </dt><dd>    @Html. displayfor (model = model. LastName) </dd>

Add the following code before,</dl> the label after the Enrollmentdata field

        <dt> @Html. displaynamefor (model = model. enrollmentdate) </dt> <dd> @Html. displayfor (model = model. enrollmentdate) </dd> <dt> @Html. displaynamefor (model = model.                    enrollments) </dt> <dd> <table class= "table" > <tr>                <th>course title</th> <th>Grade</th> </tr>                        @foreach (var item in model.enrollments) {<tr> <td> @Html. displayfor (ModelItem = Item. Course.title) </td> <td> @Html. Displa Yfor (ModelItem = Item.        Grade) </td> </tr>} </table>    </dd></dl></div><p> @Html. ActionLink ("edit", "edit", new {id = model.id}) | @Html. ActionLink ("Back to List", "Index") </p>

If the code is indented incorrectly, you can use the Ctrl-k-d shortcut key to correct it.

The above code iterates through the entities in the enrollments navigation property, showing course Title and grade for each enrollment entity. Course title is obtained from the Course entity in the Course navigation property in the enrollments entity, all of which are retrieved automatically from the database when needed. (in other words, lazy loading is used here.) You did not specify preload for the courses navigation property, so in the same query, only students data was retrieved and enrollments data was not retrieved. Instead, a new query is created and sent to the database the first time you try to access the enrollments navigation property.

Run the project, select the Students tab and click the Details link named Alexander Carson. (If you press CTRL+F5 and open details.cshtml directly, you get an HTTP 400 error because Visual Studio opens the details page without specifying any Studen, and the routing match error causes the program to fail .) In this case, you just need to remove student/details from the URL and try again)

You can see the courses and grades of the selected students.

2. Update the Create page

Open Controllers\studentcontroller.cs and replace the HttpPost create method with the following code

[HttpPost] [Validateantiforgerytoken] public ActionResult Create ([Bind (Include = "lastname,firstmidname,enrollmentdate                    ")] Student Student) {try {if (modelstate.isvalid) { Db.                    Students.add (student); Db.                    SaveChanges ();                Return redirecttoaction ("Index"); }} catch (DataException) {//log the error (uncomment DEX variable name a                nd add a line here to write a log. Modelstate.addmodelerror ("", "unable to save changes.            Try again, and if the problem persists see your system administrator. ");        Return View (student); }

The above code adds the student entity created by the ASP. NET MVC model binder to the students entity set and saves it to the database. (model binders make it easier for you to submit form data, and you can convert the submitted form values to CLR values and pass them as parameters to the method in the controller.) In this project, the model binder instantiates a student entity using the attribute values from the form collection.

This removes the ID parameter from the BIND attribute because the ID is primary KEY,SQL server automatically sets the value when inserting data.

Security Note: The Validateantiforgerytoken property helps prevent cross-site request forgery (Cross-site requests forgery) attacks, but requires that the corresponding Html.antiforgerytoken () statement be set in the view.

The bind attribute prevents excessive commits (over-posting). For example, suppose the student entity contains a secret field, and you don't want to update it in a Web page

public class Student   {public      int ID {get; set;}      public string LastName {get; set;}      public string Firstmidname {get; set;}      Public DateTime enrollmentdate {get; set;}      public string Secret {get; set;}      Public virtual icollection<enrollment> enrollments {get; set;}   }

Even if there is no secret field in the Web page, hackers can submit form data including secret values to the server through tools such as Fiddler or JavaScript. If you do not use the Bind attribute to restrict the fields required by the model binding, the model binder updates the received secret value to the database, and the following is the Fiddler tool to submit the form data

The Overpost value will be successfully updated to the database, which you do not want to see.

For security reasons, it is best to use the include parameter of the Bind property, or you can use the exclude parameter to exclude properties that you do not want to update. But it is recommended to use include, because if you add a new attribute to the entity, exclude will not exclude this newly added attribute.

Another alternative is to use the view model when the model is bound, and the view model contains only the properties that you want to bind.

In addition to the bind attribute, only the Try-catch block is added to the code above, and if the dataexception exception is thrown when the change is saved, the corresponding error message is displayed in the page. DataException exceptions are sometimes raised by external events rather than by program errors, so it is recommended that the user retry. Remember that in a production environment, all application errors should be recorded.

The code in views\student\create.cshtml is similar to that in details.cshtml, except that Displayfor is replaced by editorfor and validationmessagefor helpers

<div class= "Form-group" >    @Html. labelfor (model = model. LastName, new {@class = "Control-label col-md-2"})    <div class= "col-md-10" >        @Html. editorfor (model = Model. LastName)        @Html. validationmessagefor (model = model. LastName)    </div></div>

Create.cshtml also contains the @html.antiforgerytoken () method to prevent cross-site request forgery attacks.

Run the project, select the Students tab, and click Create New

Enter a name and a date that is not valid, and then click Create to view the error message

By default, server-side validation is used, and later it is taught to generate client validation by adding properties, and the following code shows the model validation checks in the Create method

if (modelstate.isvalid) {    db. Students.add (student);    Db. SaveChanges ();    Return redirecttoaction ("Index");}

Modify the date to a valid value, click Create to see the newly added student information

3. Update the Edit HttpPost page

In Controllers\studentcontroller.cs, the HttpGet Edit method (which does not use the HttpPost property) and the details method use the Find method to retrieve the selected student entity.

Replace the HttpPost Edit method with the following code:

 [HttpPost ] [Validateantiforgerytoken] public ActionResult Edit ([Bind (Include = "Id,lastname,firstmidname,enrollmentda                    TE ")] Student Student) {try {if (modelstate.isvalid) { Db. Entry (student).                    state = entitystate.modified; Db.                    SaveChanges ();                Return redirecttoaction ("Index"); }} catch (DataException/* dex */) {//log the error (Uncomment Dex Varia                ble name and add a line here to write a log. Modelstate.addmodelerror ("", "unable to save changes.            Try again, and if the problem persists see your system administrator. ");        Return View (student); }

The above code is similar to the HttpPost create method, but the difference is that a flag bit is set in the entity to indicate that it has been changed, rather than adding entities created by the model binder to the entity set. When the SaveChanges method is called, the modified flag causes the Entity framework to create the SQL statement and update the database. All columns of the row in the database are updated, including those users who have not changed, and ignore concurrency conflicts.

Entity state, attach, and SaveChanges methods

The database context keeps track of whether the in-memory entity is synchronized with rows in the database, and thus determines what happens when the SaveChanges method is called, such as when you call the Add method to add an entity, the entity's state is set to added, The database context then generates a SQL INSERT command when the SaveChanges method is called.

An entity may be in one of the following states:

• Added, the entity does not exist in the database, and the SaveChanges method must generate an INSERT statement.
• Unchanged, for this entity, the SaveChanges method does nothing, and when an entity is read from the database, that entity is the state.
• Modified, the property values of some or all entities are changed, and the SaveChanges method must generate an UPDATE statement.
• Deleted. The SaveChanges method must generate a DELETE statement if the entity has been flagged as a delete state.
• Detached, the entity is not tracked by the database context.

In a desktop application, the state change is usually automatic, and when you read an entity and change some of its property values, the entity's state is automatically changed to modified, and then when you call the SaveChanges method, the Entity Framework generates a SQL Update for updating the database.

DbContext is destroyed when an entity is read and rendered to the page, and when the HttpPost edit method is called, a new request and DbContext instance is generated, so you must manually set the entity status to modified. Then, when you call the SaveChanges method, the Entity Framework updates all columns of the database row because the database context has no way of knowing which properties you have changed.

If you want the SQL UPDATE statement to update only those fields that the user actually changed, you can save the original value in some way (such as a hidden field) so that you can use them when you call the HttpPost edit method. You can then use the original value to create a student entity, call the Attach method, update the entity with the new value, and finally call the SaveChanges method.

The HTML and razor codes in views\student\edit.cshtml are similar to those in create.cshtml.

To run the project, select the Students tab and click on one of the student's edit links

Modify the values, click Save, you can see the modified data in the index page

4. Update the Delete page

In Controllers\studentcontroller.cs, the HttpGet Delete method generated by the template uses the Find method to retrieve the selected student entity. However, when calling the SaveChanges method fails in order to display the custom error message, you need to add some functionality to the method and to the corresponding view.

Just like the update and create operations, the delete operation also requires two action methods. The method used to respond to a GET request is used to display a view that allows the user to < approve or cancel the delete operation, and if a delete operation is performed with a confirmation, a POST request is generated and the HttpPost Delete method is called, which performs a real delete operation.

Adding Try-catch blocks in the HttpPost Delete method can be used to capture any errors that may occur when the database is updated, and if an error occurs, the HttpPost Delete method invokes the HttpGet Delete method. and pass a parameter to it indicating that an error occurred, and then HttpGet Delete displays an error message and gives the user an opportunity to cancel or retry.

Replace the HttpGet Delete method with the following code:

Public ActionResult Delete (int? ID, bool savechangeserror=false) {    if (id = = null)    {        return new Httpstatuscoderesult (httpstatuscode.badrequest);    }    if (Savechangeserror.getvalueordefault ())    {        viewbag.errormessage = "Delete failed. Try again, and if the problem persists see your system administrator. ";    }    Student Student = db. Students.find (ID);    if (student = = null)    {        return httpnotfound ();    }    Return View (student);}

The above code accepts an optional parameter that indicates whether the method is called after saving the change. When the HttpGet Delete method is not called because of an error, the parameter value is false, and when the HttpPost delete error occurs, the HttpGet Delete method is called True and an error message is displayed on the corresponding view.

Replace the HttpPost Delete method with the following code (the one named Deleteconfirmed), which is used to perform a real delete operation and capture any database update errors

[HttpPost] [Validateantiforgerytoken]public actionresult Delete (int id) {    try    {        Student Student = db. Students.find (ID);        Db. Students.remove (student);        Db. SaveChanges ();    }    catch (dataexception/* dex */)    {        //log the error (uncomment DEX variable name and add a line here to write a log.< C9/>return redirecttoaction ("Delete", new {id = id, savechangeserror = true});    }    Return redirecttoaction ("Index");}

The above code retrieves the entity to be deleted from the database, then calls the Remove method to set the state of the entity to deleted, and finally calls the SaveChanges method and generates a SQL Delete command. Alternatively, you can change the method name deleteconfirmed to delete. The framework code names the HttpPost Delete method deleteconfirmed to set a unique name for it (CLR overloaded methods require different parameters). Now abide by the MVC conventions, the HttpPost and httpget Delete methods use the same names and set different parameters for them.

If you want to improve the performance of high-traffic applications, you should avoid using unnecessary SQL queries. Replace the Find and remove methods with the following code

Student studenttodelete = new Student () {id = id};d b. Entry (Studenttodelete). state = entitystate.deleted;

The above code instantiates a student entity with a unique primary key value and sets the entity state to deleted, which is what the Entity Framework needs to do to delete an entity.

As mentioned earlier, the HttpGet Delete method does not perform a data delete operation, and performing a delete operation in a GET request response (performing any edit operations, create operations, or other actions that make changes to the data) poses a security risk.

Add an error message to the views\student\delete.cshtml

To run the project, click on the Students tab and click on one of the student's delete links:

Click Delete and you will see that the student has been deleted in the index page.
5. Ensure the database connection is closed at a timely time
To ensure that the database connection is properly shut down and free of resources, you must destroy the database context when you are finished using it, which is why the framework code provides a Dispose method in the last part of StudentController.cs
protected override void Dispose (bool disposing) {    db. Dispose ();    Base. Dispose (disposing);}
The Controller class implements the Idisposeable interface, so the above code explicitly destroys the database context instance by overriding the Dispose (bool) method.
6. Handling Transactions
By default, the Entity framework implements transactions implicitly. When you make changes to multiple rows or tables and call the SaveChanges method, the Entity framework automatically ensures that all changes are either successful or all fail. If an error occurs after some changes have been made, all changes, including what has been done, are automatically rolled back.

Project Source:https://github.com/johnsonz/MvcContosoUniversity
The END

MVC5 Entity Framework Learning to implement basic CRUD functionality