how can I overload this data access layer method with a sql connection object

Question

Both Services use the AddChildrenUnit method of the IUnitDataProvider.

The TemplateService has to pass this method an already open connection object because the CreateTemplate method must run in a transaction for AddTemplate and "Create the root unit node".

The UnitService does not pass a connection object to the AddChildrenUnit method therefore the code does not compile !!!

My question is now: I can not change the AddChildrenUnit method and remove the sqlconnection parameter else the AddChildrenUnit in the CreateTemplate method will not compile anymore.

So what can I do now? The only thing I can think of is an overloaded version of the AddChildrenUnit one time with a SqlConnection parameter and one method without this parameter.

Thats cumbersome...

Do you know a better solution?

TemplateService:

public void CreateTemplate(Template template)
{
    using (var transaction = new TransactionScope()) 
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        _templateDataProvider.AddTemplate(template,connection);
        Unit rootUnit = new Unit{ TemplateId = template.TemplateId, ParentId = null, Name = "Root" };
        _unitDataProvider.AddChildrenUnit(rootUnit,connection);

        transaction.Complete();
    }
}

UnitService:

public void AddChildrenUnit(Unit unit)
{
    lock (this)
    {
        IEnumerable<Unit> childrenUnits = _unitDataProvider.GetChildrenUnits(unit.UnitId); // Selected ParentId
        int hierarchyIndexOfSelectedUnitId = childrenUnits.Select(u => u.HierarchyIndex).DefaultIfEmpty(0).Max(c => c);
        int hierarchyIndexOfNewChild = hierarchyIndexOfSelectedUnitId + 1;
        unit.HierarchyIndex = hierarchyIndexOfNewChild;

        _unitDataProvider.AddChildrenUnit(unit);
    }
}

UNITDATAPROVIDER:

/// <summary>
///  INSERT new child at the end of the children which is the highest HierarchyIndex 
/// </summary>
/// <param name="unit"></param>
public void AddChildrenUnit(Unit unit) // 10 ms
{
    using (var trans = new TransactionScope())
    using (var con = new SqlConnection(_connectionString))
    using (var cmd = new SqlCommand("INSERT INTO UNIT (Name,TemplateId,ParentId,CreatedAt,HierarchyIndex) VALUES (@Name,@TemplateId,@ParentId,@CreatedAt,@HierarchyIndex);Select Scope_Identity();",con))
    {
        con.Open();

        // INSERT new child at the end of the children which is the highest HierarchyIndex                
        cmd.Parameters.AddWithValue("HierarchyIndex", unit.HierarchyIndex); 
        cmd.Parameters.AddWithValue("TemplateId", unit.TemplateId);
        cmd.Parameters.AddWithValue("Name", unit.Name);
        cmd.Parameters.Add("CreatedAt", SqlDbType.DateTime2).Value = unit.CreatedAt; 

        unit.UnitId = Convert.ToInt32(cmd.ExecuteScalar());

        trans.Complete();
    }             
}

score 0 · Answer 1 · answered Nov 26 '12 at 22:06

How about this?

public void AddChildrenUnit(Unit unit) // 10 ms
{
    AddChilrenUnit(unit, new SqlConnection(_connectionString));
}

public void AddChildrenUnit(Unit unit, SqlConnection connection)
{
    using (var trans = new TransactionScope())
    using (connection))
    using (var cmd = new SqlCommand("INSERT INTO UNIT (Name,TemplateId,ParentId,CreatedAt,HierarchyIndex) VALUES (@Name,@TemplateId,@ParentId,@CreatedAt,@HierarchyIndex);Select Scope_Identity();",con))
    {
        con.Open();

        // INSERT new child at the end of the children which is the highest HierarchyIndex                
        cmd.Parameters.AddWithValue("HierarchyIndex", unit.HierarchyIndex); 
        cmd.Parameters.AddWithValue("TemplateId", unit.TemplateId);
        cmd.Parameters.AddWithValue("Name", unit.Name);
        cmd.Parameters.Add("CreatedAt", SqlDbType.DateTime2).Value = unit.CreatedAt; 

        unit.UnitId = Convert.ToInt32(cmd.ExecuteScalar());

        trans.Complete();
    }             

}

score 0 · Answer 2 · answered Nov 26 '12 at 22:34

Here is what I will do.

public interface IUnitDataProvider
{
    void AddChildrenUnit(Unit unit, string connectionString);
}


public class UnitService:IUnitDataProvider
{
    //Implement AddChildrenUnit the way want
    //pass the connection string but u dont use it
}

public interface UnitDataProvider:IUnitDataProvider
{
    //Implement AddChildrenUnit the way want
}

public class templateClass {

private IUnitDataProvider _unitDataProvider;

public templateClass(IUnitDataProvider provider)
{
        _unitDataProvider=provider
}

public void CreateTemplate(Template template)
{
    //pre -addUnit code here

   _unitDataProvider.AddChildrenUnit(unit, connectionstring);

    //Post -addUnit code here

}

}

so based on the your usage you pass the right concrete implementation to the constructor of the TemplateClass ( i named it TemplateClass , I don't know what you called it)

how can I overload this data access layer method with a sql connection object

2 Answers2