In this article, we shall learn how to delete a record from the SQL Server 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
- How to update record into the database?
- How to insert records into the database?
- 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!