N-tier architecture transacions handling

Question

I'd like to implement N-tier architecture in my WinForms applications to separate (just logically - in one project) business logic from data access, however I have some doubts about using transacion in BLL. All tutorials I've found in the Internet either are very simple implementations of that architecture (without transactions), or are too complex for my needs. Trying to find my own way, I've come to the point, where I don't know the best way to handle transactions of in BLL layer.
I'll try to use some simple example to illustrate the problem (all classes are in separate files):

//DTO - Data Transfer Objects
public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class SomeOtherItem
{
    public int Id { get; set; }
    public string Name { get; set; }
}

//DAL - Data Access layer
public class ItemDAL
{
    public ItemDAL()
    {
    }

    public void Add(Item item)
    {
        using (NpgsqlConnection conn = new NpgsqlConnection(connString))
        {
            conn.Open();
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = @"INSERT INTO tbl_items (name)
                                    VALUES (@name)";
                cmd.Parameters.AddWithValue("@name", item.Name);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

public class SomeOtherItemDAL
{
    public SomeOtherItemDAL()
    {
    }

    public void Add(SomeOtherItem someOtherItem)
    {
        using (NpgsqlConnection conn = new NpgsqlConnection(connString))
        {
            conn.Open();
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = @"INSERT INTO tbl_some_other_items (name)
                                    VALUES (@name)";
                cmd.Parameters.AddWithValue("@name", someOtherItem.Name);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

//BLL - Business Logic Layer
public class SomeBLL
{
    public SomeBLL()
    {
    }

    public void Add(Item item, SomeOtherItem someOtherItem)
    {

        ItemDAL itemDAL = new ItemDAL();
        SomeOtherItemDAL someOtherItemDAL = new SomeOtherItemDAL();

        // *** this must be done in one transaction ***
        itemDAL.Add(item);
        someOtherItemDAL.Add(someOtherItem);
    }
}

Now, the problem is that if I want to use Transacion, I cannot use:

using (NpgsqlConnection conn = new NpgsqlConnection(connString))

in DAL. To use NpgsqlTransacion object I must somehow keep connection opened and visible in both DAL classes.
I've tried use TransacionScope object for that, but from from some reasons it's not working with PostgreSQL and the driver I'm using (INSERTS are done just after executed and there is no transaction rollback when exception within TransacionScope occures).

What I've come into is to make additional Singleton class to keep connection alive and manage transactions:

public class DB
{
    private static DB instance;
    private const string connString = @"Server=localhost;Port=5432;Database=db_test;User Id=usr_test;Password=pass";
    private NpgsqlConnection conn;

    private DB()
    {
        conn = new NpgsqlConnection(connString);
    }

    public static DB Instance
    {
        get
        {
            if (instance == null)
            {
                instance = new DB();
            }
            return instance;
        }
    }

    #region --- connection ---
    public NpgsqlConnection GetOpenConnection()
    {
        OpenConnection();
        return conn;
    }

    private void OpenConnection()
    {
        if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            conn.Open();
    }

    public void CloseConnection()
    {
        if (conn != null && !inTransaction)
        {
            conn.Close();
        }
    }
    #endregion

    #region --- transaction ---
    private NpgsqlTransaction trans;
    private bool inTransaction;
    public bool InTransaction { get { return inTransaction; } }

    public void TransactionStart()
    {
        OpenConnection();
        trans = conn.BeginTransaction();
        inTransaction = true;
    }

    public void TransactionCommit()
    {
        if (inTransaction)
        {
            try
            {
                trans.Commit();
                trans.Dispose();
            }
            finally
            {
                inTransaction = false;
                CloseConnection();
            }
        }
    }

    public void TransactionRollback()
    {
        if (inTransaction)
        {
            try
            {
                trans.Rollback();
                trans.Dispose();
            }
            finally
            {
                inTransaction = false;
                CloseConnection();
            }
        }
    }
    #endregion
}

and rebuild both DAL Add methods to access connection like that:

//DAL - Data Access layer
public class ItemDAL
{
    public ItemDAL()
    {
    }

    public void Add(Item item)
    {
        using (NpgsqlCommand cmd = new NpgsqlCommand())
        {
            cmd.Connection = DB.Instance.GetOpenConnection();
            cmd.CommandText = @"INSERT INTO tbl_items (name)
                                VALUES (@name)";
            cmd.Parameters.AddWithValue("@name", item.Name);
            cmd.ExecuteNonQuery();
        }
        if (!DB.Instance.InTransaction)
            DB.Instance.CloseConnection();
    }
}

public class SomeOtherItemDAL
{
    public SomeOtherItemDAL()
    {
    }

    public void Add(SomeOtherItem someOtherItem)
    {
        using (NpgsqlCommand cmd = new NpgsqlCommand())
        {
            cmd.Connection = DB.Instance.GetOpenConnection();
            cmd.CommandText = @"INSERT INTO tbl_some_other_items (name)
                                VALUES (@name)";
            cmd.Parameters.AddWithValue("@name", someOtherItem.Name);
            cmd.ExecuteNonQuery();
        }
        if (!DB.Instance.InTransaction)
            DB.Instance.CloseConnection();
    }
}  

Note that I'd like to follow the rule to "close database connection as soon as possible", so when Add method is called without transaction scope, I'd like it to close the connection.

So, the final questions are:
1. What do you think about it, is there a better way to handle that issue, any suggestions?
2. Should I dispose a connection in DB.CloseConnecion()? I surely do when using using (NpgsqlConnection conn = ...) { ... } pattern, but as Singleton is alive as long as application, does it make sense? Connection is returned to ConnectionPool after Close(), isn't it? Or maybe I should also dispose a Singleton object (together with connection), after each using?
3. It's not directly connected question, but if I use DTO objects (just properties, no methods) and have also some BusinessObjects (BO) with the same properties, but also with additional methods (validations, calculations, operations etc.), can it be inherited from DTO? Or maybe I can use full BusinessObject to transfer it between layers, and get rid off DTO?

EDIT: TransacionScope
As requested, I add some code from my tries with TransactionScope. Simply WinForm application, no Exceptions handling. As a result, there is an Exception window when I throw it, but in database I see records with values test1 ans test2. Both when debbuging in VS and executing application from .exe

using Npgsql;
using System.Transactions;
//...

private void button1_Click(object sender, EventArgs e)
{
    using (System.Transactions.TransactionScope scope = new TransactionScope())
    {
        AddValue("test1");
        AddValue("test2");
        throw new Exception("bam!");
        AddValue("test3");
        scope.Complete();
    }
}

private void AddValue(string value)
{
    string connString = "Server=localhost;Port=5432;Database=db_test;User Id=usr_test;Password=pass";

    using (NpgsqlConnection conn = new NpgsqlConnection(connString))
    {
        conn.Open();
        using (NpgsqlCommand cmd = new NpgsqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = @"INSERT INTO tbl_test (name)
                                VALUES (@name)";
            cmd.Parameters.AddWithValue("@name", value);
            cmd.ExecuteNonQuery();
        }
    }
}

Answer 1

What you did is brave, but not scalable. I'm not familiar with PGSQL, but this problem is the exact reason why the TransactionScope API was designed.

Can you show your code using the TransactionScope api? Make sure that you are not calling scope.Complete(); if an error occurs in one of the methods. Be careful to not "eat" the exceptions inside the methods because in that case the flow will continue just like if nothing happened.

More reading about TransactionScope here: http://msdn.microsoft.com/en-us/library/ms172152.aspx

Update 1

Thanks for sharing your code that uses the TransactionScope class. The code looks perfectly correct to me. According to this (http://npgsql.projects.postgresql.org/docs/manual/UserManual.html) document (the same quoted by ChrisNeil52), Enlist=true should be included in the connection string for transactions to work.

You might be dealing with a buggy API. Good luck with this.

I know this sounds peculiar, but something I would try would be to use a different NpgsqlCommand constructor. new NpgsqlCommand("sql query", connection), instead of creating the command and assigning it the connection. They should be equivalent. but who know...

Answer 2

I've never used NpgSql, but reading the documentation of NpgSql it appears they have some support of TransactionScope() if you add "enlist=true" in your connection string.

I'm looking at the "System.Transactions Support" section of the below NpgSql documentation: http://npgsql.projects.postgresql.org/docs/manual/UserManual.html

Assuming TransactionScope() did work, then you can do simething like this...

using (var scope = new System.Transactions.TransactionScope())
{
        ItemDAL itemDAL = new ItemDAL(); 
        SomeOtherItemDAL someOtherItemDAL = new SomeOtherItemDAL(); 

        // *** this must be done in one transaction *** 
        itemDAL.Add(item); 
        someOtherItemDAL.Add(someOtherItem); 

       scope.Complete()
}