ADO.NET – Delete Records in ASP.NET Core

Previously we understood how to Update Records using ADO.NET in ASP.NET Core Application. Now it’s time to make the final functionality of the CRUD Operations 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.

This tutorial is a part of ADO.NET series.

• 1. Learn ADO.NET by building CRUD Operations in ASP.NET Core
• 2. ADO.NET – Create Records in ASP.NET Core
• 3. ADO.NET – Read Records in ASP.NET Core
• 4. ADO.NET – Update Records in ASP.NET Core
• 5. ADO.NET – Delete Records in ASP.NET Core
• 6. How to do ADO.NET SqlTransaction with Try Catch Block
• 7. Learn to use ADO.NET SqlBulkCopy class with Example

In this new column we will add a Delete Button, which will show next to 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 of the home controller and update the table code to:

@model IEnumerable<Inventory>
@{
	ViewData["Title"] = "Read Inventory";
}

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

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

<th>Delete</th>

Also, inside the foreach loop code we 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".

Also we are 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 we create the Delete Action Method in the Home Controller. It will have an id parameter which 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, we created a Delete SQL Query by adding the id value of the records to the sql delete statement.

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

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

What is difference between ADO.NET and Entity Framework? ADO.NET works directly with the databases while Entity Framework Core is an ORM, a sort of application layer build over ADO.NET and uses LINQ to work with the databases. ADO.NET has a faster execution time and less learning curve compared to Entity Framework Core.

Run the project 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:

You can download the source code using the below link:

Download

In this tutorial we 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.