How to delete a record from the database?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 5940 red flag

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

  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! 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)