C# – Avoid enabling MSDTC when using TransactionScope

cdata-access-layertransactionstransactionscope

[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.

Best Answer

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.

Related Solutions

C# – Should ‘using’ directives be inside or outside the namespace

There is actually a (subtle) difference between the two. Imagine you have the following code in File1.cs:

// File1.cs
using System;
namespace Outer.Inner
{
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now imagine that someone adds another file (File2.cs) to the project that looks like this:

// File2.cs
namespace Outer
{
    class Math
    {
    }
}

The compiler searches Outer before looking at those using directives outside the namespace, so it finds Outer.Math instead of System.Math. Unfortunately (or perhaps fortunately?), Outer.Math has no PI member, so File1 is now broken.

This changes if you put the using inside your namespace declaration, as follows:

// File1b.cs
namespace Outer.Inner
{
    using System;
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now the compiler searches System before searching Outer, finds System.Math, and all is well.

Some would argue that Math might be a bad name for a user-defined class, since there's already one in System; the point here is just that there is a difference, and it affects the maintainability of your code.

It's also interesting to note what happens if Foo is in namespace Outer, rather than Outer.Inner. In that case, adding Outer.Math in File2 breaks File1 regardless of where the using goes. This implies that the compiler searches the innermost enclosing namespace before it looks at any using directive.

C# – Why is it important to override GetHashCode when Equals method is overridden

Yes, it is important if your item will be used as a key in a dictionary, or HashSet<T>, etc - since this is used (in the absence of a custom IEqualityComparer<T>) to group items into buckets. If the hash-code for two items does not match, they may never be considered equal (Equals will simply never be called).

The GetHashCode() method should reflect the Equals logic; the rules are:

if two things are equal (Equals(...) == true) then they must return the same value for GetHashCode()
if the GetHashCode() is equal, it is not necessary for them to be the same; this is a collision, and Equals will be called to see if it is a real equality or not.

In this case, it looks like "return FooId;" is a suitable GetHashCode() implementation. If you are testing multiple properties, it is common to combine them using code like below, to reduce diagonal collisions (i.e. so that new Foo(3,5) has a different hash-code to new Foo(5,3)):

unchecked // only needed if you're compiling with arithmetic checks enabled
{ // (the default compiler behaviour is *disabled*, so most folks won't need this)
    int hash = 13;
    hash = (hash * 7) + field1.GetHashCode();
    hash = (hash * 7) + field2.GetHashCode();
    ...
    return hash;
}

Oh - for convenience, you might also consider providing == and != operators when overriding Equals and GetHashCode.

A demonstration of what happens when you get this wrong is here.

Best Answer

Related Solutions

C# – Should ‘using’ directives be inside or outside the namespace

C# – Why is it important to override GetHashCode when Equals method is overridden

Related Topic