922

In my last article, which is published here, we talked about how to get Last Inserted record in SQL Server.

Requirement – Get Last Deleted Record in SQL Server

In some cases, there might be the requirement where we need to get Last Deleted record in SQL Server instead of Inserted one.

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.

So, if you are having any of following question in mind, you will get answers in this article.

• How to get ID of Last deleted row in SQL Server?
• How to get last deleted ID in SQL?
• How to return last deleted ID in SQL?
• How to get last deleted record in SQL Server?

Technical Requirement

I expect you to have some basic knowledge of ASP.NET and SQL Server. Tools required to get last deleted record in SQL server will be:

• Visual Studio for creating ASP.NET webpage
• SQL Server

Front-end Design

What is in above design?

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 an article telling to return Last Inserted Record.)
3. On Button Click, executed Delete Command, and printed deleted Record in label and Re-bind Gridview.

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();
    }

Code to get last deleted record in SQL Server

........ OUTPUT DELETED.* ...........

Apart from regular query, we have added two extra words in T-SQL Query. Rest code is to just perform Deletion operation from database. Like we have said in previous article, OUTPUT will return values of the SQL Query and DELETED.* will tell all columns of deleted row to be returned.

You can get all the information about OUTPUT Clause of T-SQL (Transact Structured Query Language) at MSDN library at below link.

Download Source code of this sample project for free.

Download “Get Last Deleted Record in SQL Server”

Get-Last-Deleted-Record-in-SQL-Demo.zip – Downloaded 120 times – 2.38 KB