.NET TableAdapters Inside TransactionScope

Both TableAdapter and TransactionScope are new in .NET 2.0. Auto-generated TableAdapter and DataTable could play an ORM (Object-relational Mapping) role and act as DAL (Data Access Layer) in an application. TransactionScope wraps the complexity of transaction, and it allows you to put DB interactions inside a transactional block. Using TableAdapter and TransactionScope properly can significantly simplify developers work.

I have just read an article Managing Transactions using TransactionScope that provides an example of using TableAdapter and TransactionScope together:

public static int AddDepartmentWithEmployees(Department dept)
{
    int res = 0;

    DepartmentAdapter deptAdapter = new DepartmentAdapter();
    EmployeeAdapter empAdapter = new EmployeeAdapter();
    using (TransactionScope txScope = new TransactionScope())
    {
        res += deptAdapter.Insert(dept.DepartmentName);
        //Custom method made to return Department ID after inserting the department "Identity Column"
        dept.DepartmentID = deptAdapter.GetInsertReturnValue();
        foreach(Employee emp in dept.Employees)
        {

            emp.EmployeeDeptID = dept.DepartmentID;
            res += empAdapter.Insert(emp.EmployeeName, emp.EmployeeDeptID);
        }
        txScope.Complete();
    }
    return res;
}

The code demos using TableAdapter and TransactionScope to insert a Department record and a collection of Employee records in that department into database, presuming SQL Server 2005 in this case to take advantage of lightweight transaction.

Looks really great and simple. But there's an issue of such implementation: Distributed Transaction Coordinator (DTC) is promoted at run time to complete this transaction. A run time error page will show up if DTC is not properly setup in the server; even with DTC configured, overhead of DTC would introduce big performance issues.

The reason is that each TableAdapter maintains its own database connection, and multiple operations with the same TableAdapter would lead to multiple database connections. A lightweight transaction is used in TransactionScope by default with SQL Server 2005, but DTC is promoted if multiple connections exist inside the same TransactionScope, which is the case in the above example.

We all know the cost of DTC is too expensive. Thus it's recommended to avoid using TableAdapters with TransactionScope, or avoid letting TableAdapters to manage the connections. You should manually and explicitly maintain all connections that are involved in a transaction scope if you have to use them together.

.NET Object Construction Sequence

How's a .NET object constructed and what's the order of initialization of object fields & static fields? Let's do a simple test:

class Program
{
    static void Main()
    {
        new SubClass();
        Console.Read();
    }

    class BaseClass
    {
        Logging baseField = new Logging("BaseClass field initializer");
        static Logging baseStaticField = new Logging("BaseClass static field initializer");

        static BaseClass()
        {
            Logging.Write("BaseClass static constructor");
        }

        public BaseClass()
        {
            Logging.Write("BaseClass constructor");
        }
    }
    class SubClass : BaseClass
    {
        Logging subClassield = new Logging("SubClass field initializer");
        static Logging subClassStaticField = new Logging("SubClass static field initializer");

        static SubClass()
        {
            Logging.Write("SubClass static constructor");
        }

        public SubClass()
        {
            Logging.Write("SubClass constructor");
        } 
    }

    class Logging
    {
        static int count = 1;
        public Logging(string info)
        {
            Write(info);
        }

        public static void Write(string info)
        {
            Console.WriteLine("{0}: {1}", count++, info);
        }
    }
}

Result:

1: SubClass static field initializer
2: SubClass static constructor
3: SubClass field initializer
4: BaseClass static field initializer
5: BaseClass static constructor
6: BaseClass field initializer
7: BaseClass constructor
8: SubClass constructor

The result shows a few interesting things:
1. Derived class field initializer first and base class field initializer next.
2. Class field initializer first and class constructor next.
3. Base class constructor first and derived class constructor next.
4. Static field initializer first and static constructor next.
5. Static constructor first and class constructor next.
5. Derived class static constructor first and base class static constructor next.

What's the reason for such order? Class constructor would reference those fields or static fields thus those fields must be initialized before the class constructor; same as static fields need to be initialized before the static constructor; on the other hand, base class constructor runs before derived class constructor because subclass construction may depend on the the state initialized by the base class, and base class usually has no knowledge about the subclass.

A common mistake is that a class field inialializer is using another non-static field, property or method. For example following code snippet will get compilation error:

public int GetTotalCount()
{
    return  Service.GetTotalCount();
}

// Compile error: "A field initializer cannot reference the non-static field, method, or property..."
private int _totalNum = GetTotalCount();

// Compile error: "An object reference is required for the non-static field, method, or property..."
static int _totalNum = GetTotalCount();  

The easy way to resolve above problem would be making the static GetTotalCount method. You can also initialize the field inside the class constructor if you don't like the static approach.