CODEWITHSUNDEEP
sqltransactions
Feryt
Feryt

Reputation: 2302

ADO.NET Transaction and SQL Server 2008 Transaction

If i run stored procedure in ADO.NET with transaction enabled and SP begin its own transaction inside(with COMMIT TRANS). What happens when ADO.NET rollback transaction? Is transaction from SP rollbacked as well? Is DB in state as it was before calling ADO.NET?

Thank you.

Upvotes: 1

Views: 1116

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166606

I have just tested this using C# 2008, Sql Server 2005 using Code as below and it did rollback.

SqlConnection con = new SqlConnection("server=svr;database=db;uid=user;pwd=pw;Connect Timeout=900");
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MySp";
con.Open();
SqlTransaction trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Rollback();  // or trans.Commit()
con.Close();

Database code

CREATE TABLE [dbo].[TEST](
    [Val] [int] NULL
)

ALTER PROCEDURE MySp
AS 
BEGIN
    BEGIN TRANSACTION T1
    INSERT INTO TEST SELECT 1
    COMMIT TRANSACTION T1
END

Upvotes: 5

Related Questions