Avoid enabling MSDTC when using TransactionScope

Question

[Using: C# 3.5 + SQL Server 2005]

I have some code in the Business Layer that wraps in a TransactionScope the creation of an order and its details:

        DAL.DAL_OrdenDeCompra dalOrdenDeCompra = new GOA.DAL.DAL_OrdenDeCompra();
        DAL.DAL_ItemDeUnaOrden dalItemDeUnaOrden = new GOA.DAL.DAL_ItemDeUnaOrden();            
        using (TransactionScope transaccion = new TransactionScope())
        {
            //Insertion of the order
            orden.Id = dalOrdenDeCompra.InsertarOrdenDeCompra(orden.NumeroOrden, orden.PuntoDeEntregaParaLaOrden.Id, (int)orden.TipoDeCompra, orden.FechaOrden, orden.Observaciones);
            foreach (ItemDeUnaOrden item in orden.Items)
            {                       
                //Insertion of each one of its items. 
                dalItemDeUnaOrden.InsertarItemDeUnaOrden(orden.Id, item.CodigoProductoAudifarma, item.CodigoProductoJanssen, item.CodigoEAN13, item.Descripcion, item.CantidadOriginal, item.ValorUnitario);
            }
            transaccion.Complete();
        }
        return true;

And here is the DAL code that perform the inserts:

public int InsertarOrdenDeCompra(string pNumeroOrden, int pPuntoEntregaId, int pTipoDeCompra, DateTime pFechaOrden, string pObservaciones)
    {
        try
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection conexion = new SqlConnection())
            {
                using (SqlCommand comando = new SqlCommand())
                {
                    ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["CSMARTDB"];
                    conexion.ConnectionString = conString.ConnectionString;
                    conexion.Open();                        
                    comando.Connection = conexion;
                    comando.CommandType = CommandType.StoredProcedure;
                    comando.CommandText = "GOA_InsertarOrdenDeCompra";
                    //...parameters setting
                    return (int)comando.ExecuteScalar();                  
                 ...

public int InsertarItemDeUnaOrden(int pOrdenDeCompraId, string pCodigoProductoAudifarma, string pCodigoProductoJanssen, string pCodigoEAN13, string pDescripcion, int pCantidadOriginal, decimal pValorUnitario)
{
    try
    {
        DataTable dataTable = new DataTable();
        using (SqlConnection conexion = new SqlConnection())
        {
            using (SqlCommand comando = new SqlCommand())
            {
                ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["CSMARTDB"];
                conexion.ConnectionString = conString.ConnectionString;                        
                conexion.Open();
                comando.Connection = conexion;
                comando.CommandType = CommandType.StoredProcedure;
                comando.CommandText = "GOA_InsertarItemDeUnaOrden";
                //... parameters setting
                return comando.ExecuteNonQuery();

Now, my problem is in the items insertion; when the InsertarItemDeUnaOrden tries to open a new connection an exception is rised because that would cause the TransactionScope to try promoting to MSDTC, wich I don't have enabled and I would prefer not to enable.

My doubts:

• Understandig that the method tht starts the transaction is in the business layer and I don't want there any SqlConnection, ¿can I use another design for my data access so I'm able to reuse the same connection?
• Should I enable MSDTC and forget about it?

Thanks.

EDIT: solution

I created a new class in the DAL to hold transactions like this:

namespace GOA.DAL
{
    public class DAL_Management
    {
        public SqlConnection ConexionTransaccional { get; set; }

        public bool TransaccionAbierta { get; set; }

        public DAL_Management(bool pIniciarTransaccion)
        {
            if (pIniciarTransaccion)
            {
                this.IniciarTransaccion();
            }
            else
            {
                TransaccionAbierta = false;
            }
        }

        private void IniciarTransaccion()
        {
            this.TransaccionAbierta = true;
            this.ConexionTransaccional = new SqlConnection();
            ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["CSMARTDB"];
            this.ConexionTransaccional.ConnectionString = conString.ConnectionString;
            this.ConexionTransaccional.Open();
        }

        public void FinalizarTransaccion()
        {
            this.ConexionTransaccional.Close();
            this.ConexionTransaccional = null;
            this.TransaccionAbierta = false;
        }
    }
}

I modified the DAL execution methods to receive a parameter of that new class, and use it like this:

public int InsertarItemDeUnaOrden(int pOrdenDeCompraId, string pCodigoProductoAudifarma, string pCodigoProductoJanssen, string pCodigoEAN13, string pDescripcion, int pCantidadOriginal, decimal pValorUnitario, DAL_Management pManejadorDAL)
        {
            try
            {
                DataTable dataTable = new DataTable();
                using (SqlConnection conexion = new SqlConnection())
                {
                    using (SqlCommand comando = new SqlCommand())
                    {
                        if (pManejadorDAL.TransaccionAbierta == true)
                        {
                            comando.Connection = pManejadorDAL.ConexionTransaccional;
                        }
                        else
                        {
                            ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["CSMARTDB"];
                            conexion.ConnectionString = conString.ConnectionString;
                            conexion.Open();
                            comando.Connection = conexion;
                        }                        
                        comando.CommandType = CommandType.StoredProcedure;
                        comando.CommandText = "GOA_InsertarItemDeUnaOrden";

And finally, modified the calling class:

        DAL.DAL_OrdenDeCompra dalOrdenDeCompra = new GOA.DAL.DAL_OrdenDeCompra();
        DAL.DAL_ItemDeUnaOrden dalItemDeUnaOrden = new GOA.DAL.DAL_ItemDeUnaOrden();            
            using (TransactionScope transaccion = new TransactionScope())
            {
                DAL.DAL_Management dalManagement = new GOA.DAL.DAL_Management(true);
                orden.Id = dalOrdenDeCompra.InsertarOrdenDeCompra(orden.NumeroOrden, orden.PuntoDeEntregaParaLaOrden.Id, (int)orden.TipoDeCompra, orden.FechaOrden, orden.Observaciones, dalManagement);
                foreach (ItemDeUnaOrden item in orden.Items)
                {                        
                    dalItemDeUnaOrden.InsertarItemDeUnaOrden(orden.Id, item.CodigoProductoAudifarma, item.CodigoProductoJanssen, item.CodigoEAN13, item.Descripcion, item.CantidadOriginal, item.ValorUnitario, dalManagement);
                }
                transaccion.Complete();                    
            }
            dalManagement.FinalizarTransaccion();

With this changes I'm inserting orders and items without enabling MSDTC.

Answer 1

Can't you create the connection outside the methods and pass the same connection to both methods through the parameters?

That way you use the same connection avoiding the promotion.

My good solution would be to rethink the architecture of the DAL. Something like having an central DAL, that stores an connection object, and have an reference to your DAL_OrdenDeCompra and DAL_ItemDeUnaOrden objects, and passing the reference of the DAL to this objects so they can interact with the connection stored in the DAL. And then the DAL could have an Open and Close method with reference count, open increments, close decrements and it should only dispose the connection when it reaches zero and create a new one when incrementing to one. Also the DAL should implement the IDisposable to clean the resources of the connection. Then in your Business Layer you do something like this:

using(DAL dal = new DAL())
{
    DAL.DAL_OrdenDeCompra dalOrdenDeCompra = dal.OrdenDeCompra;
    DAL.DAL_ItemDeUnaOrden dalItemDeUnaOrden = dal.ItemDeUnaOrden;
    using (TransactionScope transaccion = new TransactionScope())
    {
        dal.Open();
        //Insertion of the order
        orden.Id = dalOrdenDeCompra.InsertarOrdenDeCompra(orden.NumeroOrden, orden.PuntoDeEntregaParaLaOrden.Id, (int)orden.TipoDeCompra, orden.FechaOrden, orden.Observaciones);
        foreach (ItemDeUnaOrden item in orden.Items)
        {                       
            //Insertion of each one of its items. 
            dalItemDeUnaOrden.InsertarItemDeUnaOrden(orden.Id, item.CodigoProductoAudifarma, item.CodigoProductoJanssen, item.CodigoEAN13, item.Descripcion, item.CantidadOriginal, item.ValorUnitario);
        }
        transaccion.Complete();
    }
    return true;
}

Answer 2

You could have a method in DAL.DAL_ItemDeUnaOrden which receives a collection of ItemDeUnaOrden instead of a single item, that way you can use a SqlTransaction (or TransactionScope) and iterate over the items within the DA method.

orden.Id = dalOrdenDeCompra.InsertarOrdenDeCompra(...);
dalItemDeUnaOrden.InsertarVariosItemsDeUnaOrden(orden.Items);

Depending on your code, you might not have access to your busiess objects (ItemDeUnaOrden) within you DAL, so you might need to pass the values some other way, maybe DTOs or a DataTable.

Answer 3

When using TransactionScope with multiple connections against SQL Server 2005, the transaction will always escalate to a distributed one (meaning MSDTC will be used).

This is a known issue, fixed in SQL Server 2008.

One option you have is to write a single stored procedure that does all the required operations (folding up GOA_InsertarOrdenDeCompra and all calls GOA_InsertarItemDeUnaOrden). With SQL Server 2005 this can be accomplished with an XML parameter, though SQL Server 2008 (apart from not having this issue) has table-valued parameters.