I would like to implement the N-level architecture in my WinForms applications to separate business logic (only logically - in one project) from data access, however I have some doubts about using a transaction in BLL. All the tutorials that I found on the Internet are very simple implementations of this architecture (no transactions) or too complex for my needs. Trying to find my own path, I came to the point where I do not know how best to process transactions in the BLL layer.
I will try to use a 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 the NpgsqlTransacion object, I have to somehow keep the connection open and visible in both DAL classes.
I tried using the TransacionScope object for this, but for some reason it does not work with PostgreSQL and the driver I use (INSERTS are executed immediately after execution and there is no transaction rollback when an exception is thrown inside TransacionScope).
I came to create an additional Singleton class to keep in touch 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 the DAL Add methods to access a similar connection:
//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(); } }
Please note that I would like to follow the rule to "close the database connection as soon as possible," so when the "Send" method is called without a transaction scope, I would like to close this connection.
So the last questions:
1. What do you think about this, is there a better way to deal with this problem, any suggestions?
2. Should I establish a connection in DB.CloseConnecion ()? I am sure that when using the template (NpgsqlConnection conn = ...) { ... } , but since Singleton is alive as long as the application, does it make sense? The connection returns to ConnectionPool after Close() , right? Or maybe I should also place the Singleton object (along with the join), after each use?
3. This question is not directly related, but if I use DTO objects (just properties, methods), as well as some BusinessObjects (BO) with the same properties, but also with additional methods (validations, calculations, operations, etc.) Can it be inherited from the DTO? Or maybe I can use the full BusinessObject to transfer it between layers and get rid of DTO?
EDIT: TransacionScope
As requested, I am adding code from my attempts with TransactionScope. Just a WinForm application, no exception handling. As a result, the Exception window appears when I throw it, but in the database I see records with the values test1 ans test2. Both when debbuging in VS, and when running the 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(); } } }