Delete Records using ADO.NET in ASP.NET Core Application

Delete Records using ADO.NET in ASP.NET Core Application

Previously I taught how to Update Records using ADO.NET in ASP.NET Core Application. Now it’s time to make the final functionality which is the Delete Record feature. Like the update feature, in the delete feature to – there will be a new added column called Delete in the HTML table of the Index View.

In this new column I will add a Delete Button, which will show against each of the inventory record. On clicking a Delete Button, the corresponding record will be deleted from the database.

So go to the Index View and update the table code to:

@model IEnumerable<Inventory>

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ADO - Read Inventory</title>
    <link rel="stylesheet" asp-href-include="lib/bootstrap/css/bootstrap.min.css" />
</head>
<body>
    <div class="container-fluid">
        <h1>Inventory <a asp-action="Create" class="btn btn-sm btn-secondary">Create</a></h1>
        <table class="table table-bordered table-sm table-striped">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Price</th>
                    <th>Quantity</th>
                    <th>Added On</th>
                    <th>Update</th>
                    <th>Delete</th>
                </tr>
            </thead>
            <tbody>
                @if (Model == null)
                {
                    <tr>
                        <td colspan="7" class="text-center">No Model Data</td>
                    </tr>
                }
                else
                {
                    @foreach (var p in Model)
                    {
                        <tr>
                            <td>@p.Id</td>
                            <td>@p.Name</td>
                            <td>@string.Format(new System.Globalization.CultureInfo("en-US"), "{0:C2}", p.Price)</td>
                            <td>@p.Quantity</td>
                            <td>@string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)</td>
                            <td><a asp-action="Update" asp-route-id="@p.Id">Update</a></td>
                            <td>
                                <form asp-action="Delete" method="post" asp-route-id="@p.Id">
                                    <button>Delete</button>
                                </form>
                            </td>
                        </tr>
                    }
                }
            </tbody>
        </table>
    </div>
</body>
</html>

In the above code notice that I have added a new th element inside the thead element:

<th>Delete</th>

Also, inside the foreach loop code I have added a td element like:

<td>
    <form asp-action="Delete" method="post" asp-route-id="@p.Id">
        <button>Delete</button>
    </form>
</td>

Notice the form element is provided an action attribute called Delete by using the code – asp-action="Delete".

The asp-action is a Tag Helper, if you want to learn their full usage then read my tutorial called Built-In Tag Helpers in ASP.NET Core.

Also I am setting the route’s id parameter to the form’s action value from the code – asp-route-id="@p.Id". So when the Delete button is clicked then the form posts to the Delete action method along with the id value of the clicked record.

Now I create the Delete Action Method in the Home Controller which has an id parameter. This parameter automatically get’s the id value of the clicked record (because of the asp-route-id="@p.Id" set in the form tag of the Index Action).

The Delete Action method code is given below:

[HttpPost]
public IActionResult Delete(int id)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = $"Delete From Inventory Where Id='{id}'";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
 
    return RedirectToAction("Index");
}

In this action, I create a Delete SQL Query by adding the id value of the records.

string sql = $"Delete From Inventory Where Id='{id}'";

After executing the query with the ExecuteNonQuery method I am redirecting to the Index action so that the user can confirm the clicked record is indeed deleted from the database.

Testing the delete functionality

Run your application and click the Delete button for the 2nd record i.e. the skirts one. It will be deleted and you will see only the 2 remaining records. This is illustrated by the below image:

delete record feature

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to Delete Records from a database table using ADO.NET. Now you are ready to build any type of CRUD feature in ADO.NET.

In the next tutorial I will teach you How to Catch Database Exceptions with Try-Catch block in your ADO.NET codes.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.