Deleting database records in ASP.NET MVC

Deleting Database records in ASP.NET MVC

In this article, we will discuss how to delete database data in ASP.NET MVC.

First, let’s discuss how to delete data in MVC using GET request and then we will discuss why it is bad to do so. 

Step 1: Create a stored procedure to delete employee data by “ID”

Create Procedure spDeleteEmployee
@Id int
as
Begin
  Delete from Employee where Id = @Id
End

Step2: Add the following DeleteEmployee() method to “EmployeeBusinessLayer.cs” file in “BusinessLayer” project. This method calls the stored procedure “spDeleteEmployee” that we just created.

public void DeleteEmployee(int id)
{
    string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter paramId = new SqlParameter();
        paramId.ParameterName = "@Id";
        paramId.Value = id;
        cmd.Parameters.Add(paramId);

        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Step 3: Add the following “DELETE” action method to “EmployeeController”.

public ActionResult Delete(int id)
{
    EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
    employeeBusinessLayer.DeleteEmployee(id);
    return RedirectToAction("Index");
}

Run the application and navigate to “Index” action. Click the “Delete” link. This issues “GET” request to the following URL and deletes the record.

http://localhost/MVC_DEMO/Employee/Delete/1

Deleting database records using GET request opens a security hole and is not recommended by Microsoft. Just imagine what can happen if there is an image tag in a malicious email as shown below. The moment we open the email the image tries to load and issues a GET request which would delete the data.

<img src=”http://localhost/MVC_DEMO/Employee/Delete/2″ />

Also when search engines index our page they issue a GET request which would delete the data. In general GET request should be free of any side-effects meaning it should not change the state.

Deletes should always be performed using a POST request.

Deleting database records using POST request

Showing the client side javascript confirmation dialog box before deleting

Step 1: Mark “Delete” action method in “Employee” controller with [HttpPost] attribute. With this change, the “Delete” method will no longer respond to “GET” request. At this point, if we run the application and click on “Delete” link on the “Index” view we get an error stating – “The resource cannot be found”

[HttpPost]
public ActionResult Delete(int id)
{
    EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
    employeeBusinessLayer.DeleteEmployee(id);
    return RedirectToAction("Index");
}
Step 2: In “Index.cshtml”
REPLACE THE FOLLOWING CODE
@foreach (var item in Model)
{
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Gender)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.City)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Salary)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.DateOfBirth)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
            @Html.ActionLink("Details", "Details", new { id = item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id = item.ID })
        </td>
    </tr>
}
WITH
@foreach (var item in Model)
    {
        using (Html.BeginForm("Delete", "Employee", new { id = item.ID }))
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Gender)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.City)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Salary)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.DateOfBirth)
                </td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
                    @Html.ActionLink("Details", "Details", new { id = item.ID }) |
                    <input type="submit" value="Delete" onclick="return confirm('Are you sure you want to delete record with ID = @item.ID');" />
                </td>
            </tr>
        }
    }

Notice that we are using “Html.BeginForm()” HTML helper to generate a form tag.

Step 3: To include client-side confirmation before the data can be deleted add the “onclick” attribute to “Delete” button as shown below.

<input type=”submit” value=”Delete” onclick=”return confirm(‘Are you sure you want to delete record with ID = @item.ID’);” /> 

That’s it run the application and see everything is working as expected.

In the next article, I will discuss CRUD Operations using Entity Framework in ASP.NET MVC application.

SUMMARY

In this article, I try to explain deleting the database record in ASP.NET MVC application step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *