Why my Transaction doesn't work?

Question

I have a method (with WebMethod attribute), I define a transaction, in my method and in my transaction I call 2 stored procedures, first one is GetAllBook :

select * 
from Book_TBL 
where IsActive = 'True'

and second one is updateBook :

update Book_TBL 
set IsActive = 'False' 
where IsActive = 'True'

and my method :

 public struct BOOK
 {
        public string BOOK_NAME;
        public string BOOK_DESC;          
 }

[WebMethod]
public static List<BOOK> GetMyBooks()
{
    using (TransactionScope _transactionScope = new TransactionScope(TransactionScopeOption.Required))
    {
        string _connString = "Data Source=.;Initial Catalog=BookStore;Integrated Security=True";
        SqlConnection _conn = new SqlConnection(_connString);
        _conn.Open();

        SqlCommand _com = new SqlCommand();
        _com.CommandType = System.Data.CommandType.StoredProcedure;
        _com.CommandText = "GetAllBook";
        _com.Connection = _conn;

        SqlDataAdapter bookdataAdapter = new SqlDataAdapter(_com);
        DataSet bookDS = new DataSet();
        bookdataAdapter.Fill(bookDS, "Book_TBL");

        List<BOOK> bookList = new List<BOOK>();
        _conn.Close();

        BOOK book;

        foreach (DataRow dr in bookDS.Tables["Book_TBL"].Rows)
        {
            book = new BOOK();
            book.BOOK_NAME = dr["book_name"].ToString();
            book.BOOK_DESC = dr["book_desc"].ToString();
            bookList.Add(book);
        }

        SqlCommand updateCommand= new SqlCommand();
        _conn.Open();
        updateCommand.CommandText = "updateBook";
        updateCommand.CommandType = System.Data.CommandType.StoredProcedure;
        updateCommand.Connection = _conn;
        updateCommand.ExecuteNonQuery();
        _conn.Close();

        return bookList;
   }
}

When I run the project myMethod gives me the list of books which have IsActive = True but it did not update my table! What is the problem?

Answer 1

You have to call TransactionScope.Complete, the equivalent of a commit. Without it, the using blocks disposes it and that's equivalent to a rollback.