How to delete a record from the database?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In last couple of articles, I have covered several Data manipulation operation using ADO.NET. If you have missed, below is the list

1. How to update record into the database?
2. How to insert records into the database?
3. How to retrieve data from the database in ASP.NET? 

In this scenario, we are going to learn how to delete record into the database.

Below is my aspx page (aspx.page).

ASPX PAGE

<asp:label id="lblMessage" runat="server" forecolor="Green" />
    <asp:label id="lblData" runat="server" forecolor="Green" />

In the above code snippet, we have two Label control. lblMessage is used to write the success or failure message and lblData is used to write data from the database.

CODE BEHIND

 string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
 protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
      // if coming for edit mode, populate the data into the textboxes
 if (!string.IsNullOrWhiteSpace(Request.QueryString["AutoId"]))
      {
         int id = int.Parse(Request.QueryString["AutoId"]);
         if (!id.Equals(0))
         {
            if (Request.QueryString["Com"] != null &&
Request.QueryString["Com"].Equals("delete"))
                    {
                       DeleteRecord(id);
                    }
                    else
                    {
                         // PopulateTheDataInEditMode(id);
                    }
               }
        }
        FetchRecords();
     }
  }
 
 
/// <summary>
    /// Deletes the record.
    /// </summary>
    /// <param name="id">The id.</param>
    private void DeleteRecord(int id)
    {
       int returnValue = 0;
       // get the connection
       using (SqlConnection conn = new SqlConnection(_connStr))
       {
          // write the sql statement to execute
         string sql = "DELETE FROM PersonalDetail WHERE AutoId = @AutoId";
        // instantiate the command object to fire
       using (SqlCommand cmd = new SqlCommand(sql, conn))
       {
           // this is another way of adding the parameters to the command, however
use the Add method in the production
                cmd.Parameters.AddWithValue("@AutoId", id);
                conn.Open();
                returnValue = cmd.ExecuteNonQuery();
                conn.Close();
           }
      }
 
      if (!returnValue.Equals(0))
      {
          lblMessage.Text = "Record deleted successfully !";
 
          this.FetchRecords();
      }
}
 
 
/// <summary>
    /// Fetches the records.
    /// </summary>
    private void FetchRecords()
    {
        DataTable table = new DataTable();
 
       // get the connection
       using (SqlConnection conn = new SqlConnection(_connStr))
       {
           // write the sql statement to execute
           string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM
PersonalDetail Order By AutoID ASC";
            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
              // get the adapter object and attach the command object to it
              using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
              {
                  // fire Fill method to fetch the data and fill into DataTable
                  ad.Fill(table);
              }
             // DataAdapter doesn't need open connection, it takes care of opening and
closing the database connection
            }
        }
 
       // in case of concatenating multiple string we should use StringBuilder
       StringBuilder strb = new StringBuilder();
 
     // loop through the rows of the table
     foreach (DataRow row in table.Rows)
     {
        strb.Append("<p><a href=\"?AutoId=" + row["AutoId"] + "\">Edit</a> | "+
row["AutoId"] + " > " + row["FirstName"] + " > " + row["LastName"] + " > " + row["Age"] +
" > " + row["Active"] + " | <a href=\"?AutoId="+ row["AutoId"] + "&Com=delete\"><font
color=\"red\">Delete</font></a></p>");
        }
        lblData.Text = strb.ToString();
   }

In FetchRecords method we are using ADO.NET code to fetch data from database to the DataTable and then we are looping through the rows of the DataTable and writing in the lblData label.

Look at the output below, we have a Delete link for each record that redirects to the same page with AutoId and Com querystrings. AutoId querystring has the value of the AutoId column of the database table and Com has delete value to notify that the request on the page is to delete the record.

In the Page_Load event, we are checking the AutoId and querystring and then Com querystring value if there is an AutoId value and Com querystring value is “delete” then calling the DeleteRecord method by passing the AutoId value (id) as parameter.

In the DeleteRecord method, we have used the Delete SQL statement to delete the record from the database and have written the success message.

OUTPUT

Hope by going through last several articles, you have come to know many how to's of ASP.NET. Stay tuned for more articles.

Thanks for reading!