ADO.NET Transaction and SQL Server 2008 Transaction

Question

Hi. 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.

Answer 1

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