In the previous tutorial you created an MVC application that stores and displays data using the Entity Framework and SQL Server Compact. In this tutorial you will review and customize the CRUD (create, read, update, delete) code that the MVC scaffolding automatically creates for you in controllers and views.
Note?? It's a common practice to implement the repository pattern in order to create an abstraction layer between your controller and the data access layer. To keep these tutorials simple, you won't implement a repository until a later tutorial in this series (Implementing the Repository and Unit of Work Patterns).
In this tutorial, you will create the following web pages:
Creating a Details Page
The scaffolded code for the Index page left out the Enrollments property, because that property holds a collection. In the Details page you will display the contents of the collection in an HTML table.
In Controllers\StudentController.cs, the action method for the Details view resembles the following example:
public ViewResult Details(int id)
{
Student student = db.Students.Find(id);
return View(student);
}
The code uses the Find method to retrieve a single Student entity corresponding to the key value that's passed to the method as the id parameter. The id value comes from a query string in the Details hyperlink on the Index page.
Open Views\Student\Details.cshtml. Each field is displayed using a DisplayFor helper, as shown in the following example:
<div class="display-label">LastName</div>
<div class="display-field">
@Html.DisplayFor(model => model.LastName)
</div>
To display a list of enrollments, add the following code after the EnrollmentDate field, immediately before the closing fieldset tag:
<div class="display-label">
@Html.LabelFor(model => model.Enrollments)
</div>
<div class="display-field">
<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.DisplayFor(modelItem => item.Grade)
</td>
</tr>
}
</table>
</div>
This code loops through the entities in the Enrollments navigation property. For each Enrollment entity in the property, it displays the course title and the grade. The course title is retrieved from the Course entity that's stored in the Course navigation property of the Enrollments entity. All of this data is retrieved from the database automatically when it's needed. (In other words, you are using lazy loading here. You did not specify eager loading for the Courses navigation property, so the first time you try to access that property, a query is sent to the database to retrieve the data. You can read more about lazy loading and eager loading in the Reading Related Data tutorial later in this series.)
Run the page by selecting the Students tab and clicking a Details hyperlink. You see the list of courses:
Creating a Create Page
In Controllers\StudentController.cs, replace the HttpPost Create action method with the following code to add a try-catch block to the scaffolded method:
[HttpPost]
public ActionResult Create(Student student)
{
try
{
if (ModelState.IsValid)
{
db.Students.Add(student);
db.SaveChanges();
return RedirectToAction("Index");
}
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
}
return View(student);
}
This code adds the Student entity created by the ASP.NET MVC model binder to the Students entity set and then saves the changes to the database. (Model binder refers to the ASP.NET MVC functionality that makes it easier for you to work with data submitted by a form; a model binder converts posted form values to .NET Framework types and passes them to the action method in parameters. In this case, the model binder instantiates a Student entity for you using property values from the Form collection.)
The try-catch block is the only difference between this code and what the automatic scaffolding created. If an exception that derives from DataException is caught while the changes are being saved, a generic error message is displayed. These kinds of errors are typically caused by something external to the application rather than a programming error, so the user is advised to try again. The code in Views\Student\Create.cshtml is similar to what you saw in Details.cshtml, except that EditorFor and ValidationMessageFor helpers are used for each field instead of DisplayFor. The following example shows the relevant code:
<div class="editor-label">
@Html.LabelFor(model => model.LastName)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.LastName)
@Html.ValidationMessageFor(model => model.LastName)
</div>
No changes are required in Create.cshtml.
Run the page by selecting the Students tab and clicking Create New.
Data validation works by default.? Enter names and an invalid date and click Create to see the error message.
In this case you're seeing client-side validation that's implemented using JavaScript. But server-side validation is also implemented. Even if client validation failed, bad data would be caught and an exception would be thrown in server code.
Change the date to a valid value such as 9/1/2005 and click Create to see the new student appear in the Index page.
Creating an Edit Page
In Controllers\StudentController.cs, the HttpGet Edit method (the one without the HttpPost attribute) uses the Find method to retrieve the selected Student entity, as you saw in the Details method. You don't need to change this method.
However, replace the HttpPost Edit action method with the following code to add a try-catch block:
[HttpPost]
public ActionResult Edit(Student student)
{
try
{
if (ModelState.IsValid)
{
db.Entry(student).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
}
return View(student);
}
This code is similar to what you saw in the HttpPost Create method. However, instead of adding the entity created by the model binder to the entity set, this code sets a flag on the entity that indicating it has been changed. When the SaveChanges method is called, the Modified flag causes the Entity Framework to create SQL statements to update the database row. All columns of the database row will be updated, including those that the user didn't change, and concurrency conflicts are ignored. (You will learn how to handle concurrency in the Handling Concurrency tutorial later in this series.)
Entity States and the Attach and SaveChanges Methods
The database context keeps track of whether entities in memory are in sync with their corresponding rows in the database, and this information determines what happens when you call the SaveChanges method. For example, when you pass a new entity to the Add method, that entity's state is set to Added. Then when you call the SaveChanges method, the database context issues a SQL INSERT command.
An entity may be in one of the following states:
-
Added. The entity does not yet exist in the database. TheSaveChangesmethod must issue anINSERTstatement. -
Unchanged. Nothing needs to be done with this entity by theSaveChangesmethod. When you read an entity from the database, the entity starts out with this status. -
Modified. Some or all of the entity's property values have been modified. TheSaveChangesmethod must issue anUPDATEstatement. -
Deleted. The entity has been marked for deletion. TheSaveChangesmethod must issue aDELETEstatement. -
Detached. The entity isn't being tracked by the database context.
In a desktop application, state changes are typically set automatically. In this type of application, you read an entity and make changes to some of its property values. This causes its entity state to automatically be changed to Modified. Then when you call SaveChanges, the Entity Framework generates a SQL UPDATE statement that updates only the actual properties that you changed.
However, in a web application this sequence is interrupted, because the database context instance that reads an entity is disposed after a page is rendered. When the HttpPost Edit action method is called, this is the result of a new request and you have a new instance of the context, so you have to manually set the entity state to Modified. Then when you call SaveChanges, the Entity Framework updates all columns of the database row, because the context has no way to know which properties you changed.
If you want the SQL Update statement to update only the fields that the user actually changed, you can save the original values in some way (such as hidden fields) so that they are available when the HttpPost Edit method is called. Then you can create a Student entity using the original values, call the Attach method with that original version of the entity, update the entity's values to the new values, and then call SaveChanges. For more information, see Add/Attach and Entity States and Local Data on the Entity Framework team blog.
The code in Views\Student\Edit.cshtml is similar to what you saw in Create.cshtml, and no changes are required.
Run the page by selecting the Students tab and then clicking an Edit hyperlink.
Change some of the data and click Save. You see the changed data in the Index page.
Creating a Delete Page
In Controllers\StudentController.cs, the template code for the HttpGet Delete method uses the Find method to retrieve the selected Student entity, as you saw in the Details and Edit methods. However, to implement a custom error message when the call to SaveChanges fails, you will add some functionality to this method and its corresponding view.
As you saw for update and create operations, delete operations require two action methods. The method that is called in response to a GET request displays a view that gives the user a chance to approve or cancel the delete operation. If the user approves it, a POST request is created. When that happens, the HttpPost Delete method is called and then that method actually performs the delete operation.
You will add a try-catch block to the HttpPost Delete method to handle any errors that might occur when the database is updated. If an error occurs, the HttpPost Delete method calls the HttpGet Delete method, passing it a parameter that indicates that an error has occurred. The HttpGet Delete method then redisplays the confirmation page along with the error message, giving the user an opportunity to cancel or to try again.
Replace the HttpGet Delete action method with the following code, which manages error reporting:
public ActionResult Delete(int id, bool? saveChangesError)
{
if (saveChangesError.GetValueOrDefault())
{
ViewBag.ErrorMessage = "Unable to save changes. Try again, and if the problem persists see your system administrator.";
}
return View(db.Students.Find(id));
}
This code accepts an optional Boolean parameter that indicates whether it was called after a failure to save changes. This parameter is null (false) when the HttpGet Delete method is called in response to a page request. When it is called by the HttpPost Delete method in response to a database update error, the parameter is true and an error message is passed to the view.
Replace the HttpPost Delete action method (named DeleteConfirmed) with the following code, which performs the actual delete operation and catches any database update errors.
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
try
{
Student student = db.Students.Find(id);
db.Students.Remove(student);
db.SaveChanges();
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
return RedirectToAction("Delete",
new System.Web.Routing.RouteValueDictionary {
{ "id", id },
{ "saveChangesError", true } });
}
return RedirectToAction("Index");
}
This code retrieves the selected entity, then calls the Remove method to set the entity's status to Deleted. When SaveChanges is called, a SQL DELETE command is generated.
If improving performance in a high-volume application is a priority, you could avoid an unnecessary SQL query to retrieve the row by replacing the lines of code that call the Find and Remove methods with the following code:
Student studentToDelete = new Student() { StudentID = id };
db.Entry(studentToDelete).State = EntityState.Deleted;
This code instantiates a Student entity using only the primary key value and then sets the entity state to Deleted. That's all that the Entity Framework needs in order to delete the entity.
As noted, the HttpGet Delete method doesn't delete the data. Performing a delete operation in response to a GET request (or for that matter, performing an edit operation, create operation, or any other operation that changes data) creates a security risk. For more information, see ASP.NET MVC Tip #46 ― Don't use Delete Links because they create Security Holes on Stephen Walther's blog.
In Views\Student\Delete.cshtml, add the following code between the h2 heading and the h3 heading:
<p class="error">@ViewBag.ErrorMessage</p>
Run the page by selecting the Students tab and clicking a Delete hyperlink:
Click Delete. The Index page is displayed without the deleted student. (You'll see an example of the error handling code in action in the Handling Concurrency tutorial later in this series.)
Ensuring that Database Connections Are Not Left Open
To make sure that database connections are properly closed and the resources they hold freed up, you should see to it that the context instance is disposed. That is why you will find a Dispose method at the end of the StudentController class in StudentController.cs, as shown in the following example:
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
The base Controller class already implements the IDisposable interface, so this code simply adds an override to the Dispose(bool) method to explicitly dispose the context instance.
You now have a complete set of pages that perform simple CRUD operations for Student entities. In the next tutorial you'll expand the functionality of the Index page by adding sorting and paging.
Links to other Entity Framework resources can be found at the end of the last tutorial in this series.