none
Transaction without DTC???

    Question

  • Hi all!

     

    I need some help in coding Transaction that are not using MS DTC! I am rather not allowed to open the ports 134 or 135 on our firewall, but need to change 3 different tables on a SQL server covered in a transaction.

     

    I am using TableAdapter in an ASP.NET 2.0 Project (written in C#) to manipulate Tables on an SQL 2005 server (different machine). How do I do that without using the DTC?

     

    Base: my code:

    TransactionScope scope = null;

    using (scope = new TransactionScope() ) {

                this.tableAdapter1.Update( table1 );

                this.tableAdapter2.Update( table2 );

                this.tableAdapter3.Update( table3 );

                scope.Complete();

    }

     

    It looks like if the 2nd Update is running that the .NET automatically opens a new connection and that for it uses a DTCTransaction on the SQL 2005 server later on.

     

    How can I avoid using the ‘Distributet Transactions’, in order to avoid special ports?

     

    Thanks for your help

    Friday, February 16, 2007 8:21 AM

Answers

  • Actually I think that above idea is wrong as I'm pretty sure the connection is closed by the table adapter.

    Maybe try opening the connection first...

    TransactionScope scope = null;

    using (scope = new TransactionScope() ) {


                // Share the connection between adapters

                SqlConnection conn = this.tableAdapter1.Connection;

                conn.Open();

                this.tableAdapter2.Connection = conn;

                this.tableAdapter3.Connection = conn;

                //


                this.tableAdapter1.Update( table1 );

                this.tableAdapter2.Update( table2 );

                this.tableAdapter3.Update( table3 );


                conn.Close();

                scope.Complete();

    }


    Saturday, February 17, 2007 9:30 AM

All replies

  • TransactionScope uses DTC and there is no way around it.

    Instead use the SqlTransaction (Database Transactions) object as described here:

    http://www.quepublishing.com/articles/article.asp?p=30181&seqNum=3&rl=1

    You can then assign the Transaction to the TableAdapters.  To access some properties of the UpdateCommand you may need to create a partial class as described here:

    http://www.codeproject.com/cs/database/TableAdaptrCommandTimeout.asp

    Friday, February 16, 2007 2:42 PM
  • You are right in that System.Transactions will always try and use a lightweight (that is non-DTC) transaction where possible.  And since you are using SQL Server 2005 it should be possible.

    I suppose one way of getting the table adapters to share the connection would be...

    TransactionScope scope = null;

    using (scope = new TransactionScope() ) {

                this.tableAdapter1.Update( table1 );


                // Share the connection between adapters

                this.tableAdapter2.Connection = this.tableAdapter1.Connection;

                this.tableAdapter3.Connection = this.tableAdapter1.Connection;

                //


                this.tableAdapter2.Update( table2 );

                this.tableAdapter3.Update( table3 );

                scope.Complete();

    }


    Perhaps you could try then and let me know, I would be interested to see the solution to this.
    Saturday, February 17, 2007 9:16 AM
  • Actually I think that above idea is wrong as I'm pretty sure the connection is closed by the table adapter.

    Maybe try opening the connection first...

    TransactionScope scope = null;

    using (scope = new TransactionScope() ) {


                // Share the connection between adapters

                SqlConnection conn = this.tableAdapter1.Connection;

                conn.Open();

                this.tableAdapter2.Connection = conn;

                this.tableAdapter3.Connection = conn;

                //


                this.tableAdapter1.Update( table1 );

                this.tableAdapter2.Update( table2 );

                this.tableAdapter3.Update( table3 );


                conn.Close();

                scope.Complete();

    }


    Saturday, February 17, 2007 9:30 AM
  • Thanks for your help!

    The solution (marked as 'Answer' from DotNetHeaven) works fine!

    I haven't tried the suggestion with the SqlTransaction.

     

    Monday, February 19, 2007 10:44 AM
  • @Pascal Frey: Hi Pascal, I am new to DTC ( from yesterday :) ) , and what I have noticed in couple of articles is that DTC starts to manager transaction where multiple resources are involved ( multiple connection strings ). I just wonder, does your table adapters have diffirent connection strings, or DTC starts even though all adapters perform updates in the same database ?

    Thanks , Paweł

    Wednesday, February 28, 2007 6:42 AM
  • Hi Pawel

     

    In my solution I am using just one single database with different tables I need to access. The connection string of each of the three TableAdapter is exactly the same. So it makes actually no difference which one of my connection of different TableAdapter I use.

     

    I hope this helped you out

    Wednesday, February 28, 2007 1:55 PM