CODEWITHSUNDEEP
sqlsql-server-2008t-sql
c00000fd
c00000fd

Reputation: 22283

Can I first SELECT and then DELETE records in one t-SQL transaction?

I can't figure out if this is an acceptable operation. I need to select records from the SQL Server 2008 database and then delete them, all as a single transaction from an ASP.NET code. Note that the .NET code must be able to retrieve the data that was first selected.

Something as such:

SELECT * FROM [tbl] WHERE [id] > 6;
DELETE FROM [tbl] WHERE [id] > 6

I'm trying it with the SQL Fiddle but then if I do:

SELECT * FROM [tbl]

I get the full table as if nothing was deleted.

EDIT As requested below here's the full .NET code to retrieve the records:

string strSQLStatement = "SELECT * FROM [tbl] WHERE [id] > 6;" +
    "DELETE FROM [tbl] WHERE [id] > 6";

using (SqlCommand cmd = new SqlCommand(strSQLStatement, connectionString))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            //Read values
            val0 = rdr.GetInt32(0);
            val3 = rdr.GetInt32(3);
            //etc.
        }
    }
}

Upvotes: 10

Views: 8246

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44336

This will do the select and delete simultanious:

delete from [tbl] output deleted.* WHERE [id] > 6

Upvotes: 26

user2246674
user2246674

Reputation: 7719

The "easiest" way to achieve transactions with a compatible provider (SQL Server works great!) is to use a TransactionScope. Just make sure the scope is created before the connection is opened so that everything is correctly enlisted.

The content of the SelectStuff and DeleteStuff methods doesn't matter much - just use the same connection, don't manually mess with the connection or with transactions, and perform the SQL operations however is best.

// Notes
// - Create scope OUTSIDE/BEFORE connection for automatic enlisting
// - Create only ONE connection inside to avoid DTC and "advanced behavior"
using (var ts = new TransactionScope())
using (var conn = CreateConnection()) {
    // Make sure stuff selected is MATERIALIZED:
    // If a LAZY type (Enumerable/Queryable) is returned and used later it
    // may cause access to the connection outside of when it is valid!
    // Use "ToList" as required to force materialization of such sequences.
    var selectedStuff = SelectStuff(conn);

    DeleteStuff(conn);

    // Commit
    ts.Complete();

    // Know stuff is deleted here, and access selected stuff.
    return selectedStuff; 
}

Upvotes: 1

Ken White
Ken White

Reputation: 125728

The return value from multiple SQL statements is the result of the last statement run, which in this case is the DELETE. There are no rows returned from a DELETE, so there is nothing to read for val0 and val3.

There are two solutions I can think of here:

  1. Change your code to expressly start a transaction, perform the SELECT, read the values, and then issue the DELETE, or

  2. SELECT into a #temp table, execute the DELETE, and then SELECT from the #temp table, do what you need to with the rows, and then DROP th.

Upvotes: 0

TGH
TGH

Reputation: 39268

It is possible to select and delete in the same transaction as long as both operations are enlisted in the same transaction.

Look at this post Transactions in .net

Upvotes: 1

Related Questions