Get Last Deleted Records in SQL
This article is about to get the Last deleted records in ASP.NET using T-SQL. There are so many methods but we will be talking about OUTPUT here.
OUTPUT DELETED.* will be described in Simple words below. Open link for complete article.
Hello,
In my last article, which is published here, we talked about how to get Last Inserted record.
Actually we can return any values using Stored Procedure also, which are some faster also, but due to convenience or other reasons most of Developers use Direct SQL syntax inside Adaptor or SQL command. And we need to return some value in some cases while creating application. Requirement
Visual Studio
SQL Server (Database)
C# or VB (Programming Language)
What I did below:
1. Created a Simple Design with a Textbox and Button in one Row, Gridview to hold data in another Row and some Labels in third Row to Print Returned Value.
2. On Page Load, Bind Gridview with Data that we inserted Last time (in a article telling to return Last Inserted Record.)
3. On Button Click, executed Delete Command and printed deleted Record in label and Re-bind Gridview.Design for This Task:
Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Returning Last Deleted Records in SQL using ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<table width="60%" align="center">
<tr>
<td>
<h1 align="center">Delete Record</h1>
</td>
</tr>
<tr>
<td>
<table>
<tr>
<td colspan="5">Record Number :
<asp:TextBox ID="txtRollNo" runat="server" Width="120px"></asp:TextBox>
<asp:Button ID="btnDelete" runat="server" Text="Delete Record" OnClick="btnDelete_Click" />
</td>
</tr>
<tr>
<td colspan="5">
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" Width="440px">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<SortedAscendingCellStyle BackColor="#FEFCEB" />
<SortedAscendingHeaderStyle BackColor="#AF0101" />
<SortedDescendingCellStyle BackColor="#F6F0C0" />
<SortedDescendingHeaderStyle BackColor="#7E0000" />
</asp:GridView>
<br />
</td>
</tr>
<tr>
<td colspan="5" align="center">
<h1>Returned Values with ID</h1>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblRecordID" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblRollNo" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblFirstName" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblLastName" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblHomeAdd" runat="server"></asp:Label></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>Backend Code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
SqlConnection con = new SqlConnection("Data Source=.\\sqldb;database=test;integrated security=true");
protected void btnDelete_Click(object sender, EventArgs e)
{
string query = "delete from student output deleted.* where RecordID=@RecordID";
SqlDataAdapter adp = new SqlDataAdapter(query, con);
adp.SelectCommand.CommandType = CommandType.Text;
adp.SelectCommand.Parameters.AddWithValue("@RecordID", txtRollNo.Text);
DataSet ds = new DataSet();
try
{
adp.Fill(ds);
lblRecordID.Text = ds.Tables[0].Rows[0]["RecordID"].ToString();
lblRollNo.Text = ds.Tables[0].Rows[0]["RollNo"].ToString();
lblFirstName.Text = ds.Tables[0].Rows[0]["FirstName"].ToString();
lblLastName.Text = ds.Tables[0].Rows[0]["LastName"].ToString();
lblHomeAdd.Text = ds.Tables[0].Rows[0]["HomeAdd"].ToString();
txtRollNo.Text = "";
BindData();
}
catch (Exception)
{
throw;
}
finally
{
con.Dispose();
adp.Dispose();
ds.Dispose();
}
}
void BindData()
{
SqlDataAdapter adp = new SqlDataAdapter("Select * from Student", con);
DataSet ds = new DataSet();
adp.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}Main part of Code
........ OUTPUT DELETED.* ...........
Lets see output also.Output of Above Code
Description of Above Code:
In last article, where we need to return Last inserted Record we used OUTPUT INSERTED.* because we have inserted Record. Note: Insert, Update and Merge are same and uses same INSERTED to tell the Last inserted table Name while in case of Delete command, we need to use DELETED.* to get the Last deleted name of Table or the Name of Table which was defined at the time of Query.
A compressed file is attached with source code of above design and concept. You can get all the information about OUTPUT Clause of T-SQL (Transact Structured Query Language) at MSDN library at below link.
Article No : ms177564.
All the Best.
Glad to be,
John Bhatt
P.Yar.B.Complex