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:

<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>

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>

The form element created here is provided it’s action attribute to ‘Delete’ by using the code – asp-action=”Delete”.

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’ post to the ‘Delete’ action method along with the ‘id’ value of the clicked record.

Now I make the Delete action method in the Controller which has an ‘id’ parameter. This parameter automatically get’s the id value of the record which is clicked (because of the asp-route-id=”@p.Id” set in the form tag).

The 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 renaming 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 in your 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.