Share IDbConnection to keep transaction local

Question

Data Repository, which process a Data entity inherits from a generic interface and DAL layer is implemented as a generic class, which gets the generic interface as a Dependency injection. Following is the Design:

Data Entity - Vehicle

public class Vehicle
{
    public int Id {get; set;}

    public string Brand {get; set;}
}

Generic Interface - IData

public interface IData<T>
{
   IEnumerable<T> Select(IDictionary<string,object> parameters);

   int Update(IDictionary<string,object> parameters);

   int Delete(IDictionary<string,object> parameters);

   int insert(IDictionary<string,object> parameters);
}

Vehicle Repository

public class VehicleData : IData<Vehicle>
{
   IEnumerable<Vehicle> Select(IDictionary<string,object> parameters)
   {
      using(IDbConnection conn = (Fetch Connection))
      {
         // Select Operation
      } 
   }

   int Update(IDictionary<string,object> parameters)
    {
      using(IDbConnection conn = (Fetch Connection))
      {
         // Update Operation
      } 
   }

   int Delete(IDictionary<string,object> parameters)
    {
      using(IDbConnection conn = (Fetch Connection))
      {
         // Delete Operation
      } 
   }

   int insert(IDictionary<string,object> parameters)
    {
      using(IDbConnection conn = (Fetch Connection))
      {
         // Insert Operation
      } 
   }
}

DAL Class implementation :

public class DAL<T>
{
  public static IEnumerable<T> Select(IData<T> dataRepository, IDictionary<string,object> parameters)
   {
       return dataRepository.Select(parameters);
   }

// Implementation for Insert, Update and Delete
}

I execute the VehicleData using DAL from the calling class as follows.

IData<Vehicle> vehicleData = new VehicleData();

IDictionary<string,object> parameters = // Filled from client

DAL<Vehicle>.Select(vehicleData,parameters);

Issue that I face is, in each CRUD method, IDbConnection is created in the Using block, thus disposed in the end, as its in the local context, but there's scenario, where multiple DAL operations on various entities need to be part of single transaction context, if I continue in a similar way then transaction context opened will be promoted to distributed level, since multiple connection resources are opening, even when I can do them on same connection context as they are executed one by one. Issue remains:

1. How shall share the connection across multiple calls like DAL<Vehicle>.Update, DAL<Driver>.Update and DAL<Truck>.Update, so that transaction promotion from local to distributed can be avoided

2. Not sure, does opening and disposing a connection in a transaction context means another connection can be opened without promotion to distributed transaction, in my view this would not happen

Answer 1

Are you using IoC container for Dependency injection? In IoC there are some lifestyles (per thread, per web request and etc.) For example, with castle windsor you can use Scoped lifestyle. And your code will be like this:

Create connection (linq2db):

container.Register(
    Component.For<DataConnection>().UsingFactoryMethod(x => 
        CreateDataConnection()).LifestyleScoped(),
    Component.For<IData<Vehicle>>()
        .ImplementedBy<VehicleData>().LifestyleScoped()
);

private static DataConnection CreateDataConnection()
{
    return new DataConnection(new SqlServerDataProvider("", SqlServerVersion.v2008), 
        @"Data Source=(local);Initial Catalog=DB1;Persist Security Info=True;User ID=user;Password=pwd");
}

Do some db operations:

using (container.BeginScope())
{
    var db = container.Resolve<DataConnection>();
    db.BeginTransaction();
    container.Resolve<IData<Vehicle>>().Update(...);
    container.Resolve<IData<Vehicle>>().Update(...);
    container.Resolve<IData<Vehicle>>().Update(...);
    db.CommitTransaction();
}