Tech Off Thread

8 posts

Transaction Management between layers

Back to Forum: Tech Off
  • User profile image
    ferguslogic

    I am looking for an answer for what seems to be a very common problem but I can't seem to find any answers for it.

    I am developing a simple C# 2.0 application. It is a winform app which is ran on small networks.  The app is divided in typical fashion into 3 logical layers UI, business and data. Pretty straight forward.

    In C# 2.0 I can't seem to locate a good way to handle transaction management between layers.

    Here is an example:

    User needs to save an order.

    Business Tier Code example #1

    public int SaveOrder(Order myOrder)
    {
           //verify the order meets our business requirements prior to saving
           Validate(myOrder)

           // make 2 calls to the Data Access Layer
            int orderId = DALOrder.SaveOrder(myOrder)
            DALOrderItems.SaveOrderItems(myOrder);
            return orderId;
    }

    Each Data Access method above is located in seperate class modules and each method opens and closes its own connection to the database.

    Because the business tier is calling mutliple Data Access methods in the data tier, I need to create a transaction in the business tier and utilize this transaction across all of the data layer methods. Upon success the business tier can then commit the transaction.

    Today I am handling this like so:

    Business Tier Code example #2

    public int SaveOrder()
    {
           SqlConnection myConnection = DataHelper.GetConnection();
           SqlTransaction transaction = myConnection.BeginTransaction();

           try()
            {

           // makes 2 calls to the Data Access Layer, pass the transaction
            DAL.SaveOrder(order,transaction)
            DAL.SaveOrderItems(order,transaction);
            transaction.Commit();
             }
             catch(System.Exception)
             {
                      transaction.RollBack();
             }
             finally
             {
                myConnection.Close();
             }
    }

    In order for this to work my data access layer methods now have to accept a transaction parameter, look at the transaction parameter being passed and utilize the connection property of that transaction object (transaction.Connection) to connect to the database instead of opening and closing their own connections.

    So if a transaction is not passed, the methods  open and close their own connection, otherwise they use Transaction.Connection and do not close a connection at all.

    This works okay, but it is kind of messy.

    I am looking for an easier way to handle transactions.
    Perhaps, one that does not require me to pass a sqlTransaction object as a parameter to every single Data Access Layer Save, Update or delete method.

    For example, what happens now if the above SaveOrder() business tier method needs to be called by the SaveCustomer() method and the SaveCustomer() method needs to be the transaction root.  Today the SaveOrder() method is hardcoded as the root and always begins a transaction each time it is fired so should that situation arrive, our current methodology may fail.

    I am looking for someone who has faced this issue who can offer advice on how  to handle transactions between business and data tier layers of an application.

    These layers today are logical only and all reside on the same server.

    Microsofts examples seem to be simplified samples and only seem to  show you how to manage transactions in the same layer or even worse..the same exact function.  They do talk a bit about how to handle multiple connections to multiple databases (Distributed transactions) but that does not apply here.  I have multiple connections to the same exact database.

    Any advice is greatly appreciated.

    thank you in advance

  • User profile image
    mot256

    Have not used it myself but happen to read an article 2 days ago that mentioned the TransactionScope class in the System.Transactions namespace... I think it is exactly what you're looking for... hope it helps…

    Edit: Hope you have MSDN to lookup the help on it, they explain it very well...

  • User profile image
    Lee_Dale

    Have you thought about using COM+?

    Or is this not the recommended way of doing enterprise systems anymore?

    I tend to handle my transactions at database level these days.

  • User profile image
    mot256

    leeappdalecom wrote:
    Have you thought about using COM+?

    Or is this not the recommended way of doing enterprise systems anymore?

    I tend to handle my transactions at database level these days.


    Dude we're in the .NET era... rather use something like remote classes... Wink

  • User profile image
    Lee_Dale

    mot256 wrote:
    
    leeappdalecom wrote: Have you thought about using COM+?

    Or is this not the recommended way of doing enterprise systems anymore?

    I tend to handle my transactions at database level these days.


    Dude we're in the .NET era... rather use something like remote classes...


    Errr the CLR supports ServicedComponents better that COM ever did and works with .Net Remoting just fine Expressionless

    .Net remoting just replaces the DCOM architecture not COM+ which services objects with stuff like Transactions, Object Pooling, Object Contruction, JIT activation etc etc

  • User profile image
    blowdart

    Just to be pedantic; I'd argue that saving an order should, by default, save the order lines too.

    So if I call Order.Save() it would create a transaction, save the order details, then look at the order lines it has, save any changed/unsaved ones with the transaction it created to save itself, by calling OrderLines.Save(Transaction).

    If I call Customer.Save() it would create a transaction, save the customer details, then look at the orders it contains, calling Order.Save(Transaction) which in turn goes to OrderLines etc.

    Now, this does create tighly coupled dependancies, however you have that already if your Customer object contains collections of Order objects.

    The question left is do you make Customer responsible for holding the save code, or do you move it to a DAL layer? In which case the DAL layer gets a bunch of dependancies too, and the Save() method in the business layer, which defines the Customer object is a facade to the DAL (which should created via a factory to allow ease of changes)

    Whether you use .net 2.0's transaction scopes, or SQL transactions is up to you. Just remember 2.0's scopes will work over multiple data sources. Which is so so yummy.

  • User profile image
    ferguslogic

    To be honest the Customer.Save stuff is just an example to illustrate the issue that I face today with passing sqlTransaction objects around.  Basically I am attempting to illustrate that this can be an error prone process.

    The other option that I see (as pointed out  above) is to use TransactionScope. This is actually the first method that I tried before I attempted to pass sqlTransaction objects around.

    The problem with this is that when I utilize TransactionScope the transaction is being promoted from a lightweight transaction to a distributed transaction using MSDTC  as soon as a second connection is made to the same database. This  causes two issues.

    1) It has a lot more overhead that light transaction management.

    2) The MSDTC service must be running for the transaction to succeed.

    Users download this winform application and install it themselves. It runs on small Lans from 1-15 computer systems.

    The application has has to be easy to install and should run on 2000, xp home, xp pro and Vista machines

    So now I am afraid that users are going to run into issues with MSDTC configuration on their network and face high overhead due to the promotion from Light transaction management to a distributed transaction.

    When I use the new

    using(TransactionScope transScope = new TransactionScope())

    functionality in C# 2.0  I receive an error the first time I execute this code telling me that MSDTC is disabled on the current system.

    Then, when I run the code again it works fine because MSDTC automatically enables itself when it is called the first time.  The event log is indicating that this service is turning itself on after the first call.

    I saw a post on from an ADO.NET team member and my understanding is that Microsoft may address this in the future. 

    Multiple connections to the same database should not promote a transaction to MSDTC.   If two connections are made to the same database in the same transactionscope then the first connection should be pooled and used for each simultaneous connection until the transactionscope ends so that the transaction can remain in LTM and not Distributed state.

    At least that is my 2 cents anyway and I think a lot of other people will face this issue too when they begin looking at transaction management in C# 2.0/ ADo.net/Sql Server 05.

    As of right now, I am not sure what I will do. I may try TransactionScope and see what the performance penalty looks like.

    I will try to figure out if I can auto-enable MSDTC when I install the application to prevent errors for my users.

  • User profile image
    ferguslogic

    So upon further investigation and in depth research on this I have realized that there is no way i am going to be able to use TransactionScope.  TransactionScope is causing my transactions to be promoted to MSDTC just because I am opening 2 connections to the same database...and not even at the same time...just one after the other and the first one is always closed before the second is open.

    When MSDTC is utilized, MSDTC is turned off by default on XP home and Pro. (At least it appears to be on my development box - xp pro). 

    So on my first transaction my application errors out until MSDTC is requested 1 time. After the first request MSDTC starts or "spins up" in COM+  and all subsequent calls work.  If I reboot the system MSDTC will stop and the first call to MSDTC errors out again for me.

    If my users have to connect to another computer on a small LAN they have to enable MSDTC remote and incoming connections and even when I do that I can't seem to get remote MSDTC connections to work.  You have to ensure firewalls are disabled and that MSDTC can pass through firewalls if they are still enabled etc. etc. it is a huge nightmare.

    My users are mechanics, book keepers and small business owners. They download our software from our website to try it out.

    I am not going to take support call after support call trying to help non technical users configure MSDTC so that they can savea  customer record. 

    Microsoft doesn't appear to have any way to programmatically configure MSDTC (maybe I am missing some .net assembly to do this somewhere)

    It  just seems like such a huge hassle to try to deploy your application today as a Microsoft ISV.  I can't imagine making my users go through all this configuration just to network 2 computers together.

    So thats my 2 cents on the new TransactionScope, Distributed Transactions and MSDTC anyway. I am going back to passing SQlTransaction objects around so my users don't have to deal with this administration nightmare of MSDTC configuration.

    Can you imagine making mechanics, book keepers and other non technical users  have to configure MSDTC everytime they want to hook up a second computer? I just can't see it.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.