locked
transactions with C# and class library RRS feed

  • Question

  • Im dealing with a class library which contain several of the methods Id like to wrap in a single transaction.  Each of these methods in turn, call stored procedures.  How can I wrap a single transaction around calls like so:

     

    BusinessObject1 biz1 = new BusinessObject1();
    BusinessObject2 biz2 = new BusinessObject2();
    BusinessObject3 biz3 = new BusinessObject3();

     

    Then using them,

    biz1.updateSale(someguid)
    biz2.insertUserInfo(sometext)
    biz3.addNote(someguid)

    How can I wrap the above three calls into a single transaction ?

     

     

    Wednesday, February 7, 2007 10:37 PM

Answers

  • Yes, you do not need System.EnterpriseServices.

    When you create the transaction using TransactionScope, it's ambient to all inner calls and object creations.  Anything that is System.Transactions aware in that inner code will automatically pick up the fact that there's an ambient transaction -- this is all handled at the System.Transaction's layer.

    If what you say is true -- everything uses the same SqlConnection (i.e. it's created once and only once inside that TransactionScope) -- MSDTC may not be involved at all since SQL 2005 has PSPE support from System.Transactions giving you a large performance increase.  If at any time 2 SQL connections are involved, and or the transaction is 'promoted' (see http://msdn2.microsoft.com/en-us/library/ms229978.aspx), then MSDTC will get involved and performance will match what you are familiar with from the old COM+ service style transactions.

    Thursday, February 8, 2007 7:05 PM
    Moderator

All replies

  • Ideally, your code will look something like the following:

    using (TransactionScope scope = new TransactionScope())

    {

        biz1.updateSale(someguid);

        biz2.insertUserInfo(sometext);

        biz3.addNote(someguid);

       

        scope.Complete();

    }

    And that’s it.  This assumes 2 things: 1) You are using a System.Transaction aware connection to SQL (things like SqlConnection) and 2) You are opening the database connection inside the TransactionScope.  A good example of this can be found here: http://msdn2.microsoft.com/en-us/library/ms172152.aspx .  And in general here: http://msdn2.microsoft.com/en-us/library/0abf6ykb.aspx 

    To help you further we will need to know how you’re connecting to the database (which API’s etc) and where those connections are created/destroyed.

     

    Thursday, February 8, 2007 1:19 AM
    Moderator
  • The last time I used transactional components and COM+ was using VB6. 

    Do I still need to load the assembly into a COM+ application? 

    In the example given, all the classes are part of a single assembly and none of them (yet) reference the System.EnterpriseServices namespace, will this be needed ?

    Im not sure I have the luxury of modifying the class library to add the ref to the enterpriseservices namespace.

    Also in the example given, each of the three object methods wil in turn create instances of other objects which in turn create a connection and call a stored procedure.  Will wrapping these top level method calls in a transaction such as you describe (implicit transaction).

    I suppose my real question boils down to using transactions and a class library that has several layers to it. 

     

    Thursday, February 8, 2007 2:01 PM
  • I think I can answer my own question here.  For implicit transactions, and using TransactionScope, I need the System.Transactions namespace, not System.EnterpriseServices.  The EnterpriseServices would be needed if I wanted to use COM+ transactions and services

    correct ?

     

    So, now im wondering how does this work ?  Lets say I have my code that creates instances of a class library object and calls several methods, which in turn create instances of other class library objects and finally end up calling stored procedures.  This all happens using the same SqlConnection, so I suppose this is what ties it all together.  What is handling this, the MSDTC ?

    Thursday, February 8, 2007 3:21 PM
  • Yes, you do not need System.EnterpriseServices.

    When you create the transaction using TransactionScope, it's ambient to all inner calls and object creations.  Anything that is System.Transactions aware in that inner code will automatically pick up the fact that there's an ambient transaction -- this is all handled at the System.Transaction's layer.

    If what you say is true -- everything uses the same SqlConnection (i.e. it's created once and only once inside that TransactionScope) -- MSDTC may not be involved at all since SQL 2005 has PSPE support from System.Transactions giving you a large performance increase.  If at any time 2 SQL connections are involved, and or the transaction is 'promoted' (see http://msdn2.microsoft.com/en-us/library/ms229978.aspx), then MSDTC will get involved and performance will match what you are familiar with from the old COM+ service style transactions.

    Thursday, February 8, 2007 7:05 PM
    Moderator
  • You shouldn't use TransactionScope because when you hosting, it maybe hard to config Distributed Transaction Coordinator service ! And I think it' not hard code to do this

     

    SqlConnection conn = new SqlConnection("Data Source = localhost; Initial Catalog = MobileShop; UID = sa; PWD = sa");

    conn.Open();

    SqlTransaction transaction = conn.BeginTransaction();

    try

    {

    SqlDataReader rdr = new SqlCommand("SELECT * FROM Category", conn, transaction).ExecuteReader();

    while (rdr.Read())

    {

    this.listBox1.Items.Add(rdr["Name"].ToString());

    }

    rdr.Close();

    new SqlCommand("INSERT INTO Category VALUES('W210i', g1,I love you)", conn, transaction).ExecuteNonQuery();//error and roll it back

     

     

    transaction.Commit();

    MessageBox.Show("Committed !");

    }

    catch

    {

    transaction.Rollback();

    MessageBox.Show("Rolled back !");

    }

    finally

    {

    conn.Close();

    }

     

    you only use one Transaction object to do many actions by use individual SqlCommand object

    Friday, June 22, 2007 5:29 PM