ASP.NET WP - Delete Database Data

In this chapter, we will be covering how to delete an existing database record. This topic is similar to the previous chapter except that − instead of updating the record, we will delete it. Both deleting and updating processes are almost the same, except that deleting is simpler. This example will also contain two web pages.

  • On the first page, the users will select a record which they want to delete.

  • On the second page, the record to be deleted is then displayed so that the user can confirm that he/she wants to delete that record.

How to Delete a Database Record?

Let’s have a look into a simple example in which we will delete an existing database record. First of all, we need to create a new CSHTML page.

Delete Record

Enter ListCustomersForDelete.cshtml in the Name field and click OK.

Now replace the following code in the ListCustomersForDelete.cshtml file.

@{
   var db = Database.Open("WebPagesCustomers");
   var selectQueryString = "SELECT * FROM Customers ORDER BY FirstName";
}

<!DOCTYPE html>
<html>
   
   <head>
      <title>Delete a Customer</title>
      <style>
         table, th, td {
            border: solid 1px #bbbbbb;
            border-collapse: collapse;
            padding: 2px;
         }
      </style>
   
   </head>
   <body>
      <h1>Delete a Customer</h1>
      
      <table>
         <thead>
            <tr>
               <th> </th>
               <th>First Name</th>
               <th>Last Name</th>
               <th>Address</th>
            </tr>
         </thead>
         
         <tbody>
            @foreach(var row in db.Query(selectQueryString)){
               <tr>
                  <td><a href = "@Href("~/DeleteCustomer", row.Id)">Delete</a></td>
                  <td>@row.FirstName</td>
                  <td>@row.LastName</td>
                  <td>@row.Address</td>
               </tr>
            }
         </tbody>
      </table>
   
   </body>
</html>

As you can see, the above page is similar to the EditCustomers.cshtml page, the only difference is that instead of displaying an Edit link for each customer. Use the following code to add the Delete link. Once this is done, it will display a Delete link that will help in deleting the selected record.

<td><a href = "@Href("~/DeleteCustomer", row.Id)">Delete</a></td>

Delete a Customer from the Database

We should start with creating a CHTML file as shown in the following screenshot.

Delete Customer

Enter DeleteCustomer.cshtml in the name field and click OK. Now replace DeleteCustomer.cshtml file with the following code.

@{
   var db = Database.Open("WebPagesCustomers");
   var CustomerId = UrlData[0];
   
   if (CustomerId.IsEmpty()) {
      Response.Redirect("~/ListCustomersForDelete");
   }
   var customer = db.QuerySingle("SELECT * FROM CUSTOMERS WHERE ID = @0", CustomerId);
   
   if( IsPost && !CustomerId.IsEmpty()) {
      var deleteQueryString = "DELETE FROM Customers WHERE Id=@0";
      db.Execute(deleteQueryString, CustomerId);
      Response.Redirect("~/ListCustomersForDelete");
   }
}

<!DOCTYPE html>
<html>
   
   <head>
      <title>Delete Customer</title>
   </head>
   
   <body>
      <h1>Delete Customer - Confirmation</h1>
      
      <form method = "post" action = "" name = "form">
         <p>Are you sure you want to delete the following Customer?</p>
         <p>FirstName: @customer.FirstName <br />
         LastName: @customer.LastName <br />
         Address: @customer.Address</p>
         <p><input type = "submit" value = "Delete" /></p>
      </form>
   
   </body>
</html>

Now let’s run the application and specify the following url − http://localhost:36905/ListCustomersForDelete and you will see the following web page.

Delete A Customer

As you can see all the customers from the database and also the Delete link for each customer. Let’s select the Delete link for Kerry Hill and you will see the following page.

Delete Confirmation

All the information is displayed for that customer. When you click on the Delete button then this customer will be deleted from the database.

Let’s click the Delete button and you will see that it is deleted from the database as shown in the following screenshot.

Delete Customer Record

Now the database only has two records.

Advertisements