Call stored procedure with output parameter using Builder pattern

My custom DAL requirements is:

1. By given Business objects (BOs) call stored procedures
2. Handle output parameters (updating BOs fields)
3. Throw exceptions, if error code returned from stored procedure

Coding this functionality using ADO.NET is a bit annoying. The majority of code consists of communication with framework interface, and does not explicitly express developer's intensions. Example.
Such annoying code can be refactored nicely using Builder pattern. Target interface for me is interface like this:

public void Update(ObjectContext Ctx, BusinessObject Row)
{
 CommandBuilder Builder = InsertOrUpdateBuilder(Ctx, "updateBusinessObject", Row);
 Builder.ErrorCodes
  .Parameter("err_code_out")
  .Handle<ConcurrencyException>(-1);

 Builder
  .Parameter("id", Row.Id)
  .Parameter("last_update_date", Row.LastUpdateDate)
  .Parameter("last_updated_by", Row.LastUpdatedBy)
  .Output<DateTime>("last_update_date_out", (d) => Row.LastUpdateDate = d)
  .Create()
  .ExecuteUpdate();
}

public void Insert(ObjectContext Ctx, BusinessObject Row)
{
 InsertOrUpdateBuilder(Ctx, "insertBusinessObject", Row)
  .Parameter("created_by", Row.CreatedBy)
  .Output<int>("id_out", (Id) => Row.Id = Id)
  .Create()
  .ExecuteUpdate();
}

This interface is versatile enough to express required stored procedures call scenarios and explicit enough to be readable by human.

Literally Update call do this:

1. If Stored procedure returns –1 from parameter "err_code_out", Concurrency exception will be thrown.
2. Parameters "id", "last_update_date" and "last_updated_by" will be added to stored procedures
3. Parameter "last_update_date_out" will be threaded as output parameter of type DateTime and mapped to Row.LastUpdateDate.

Implementation

The idea of Builder pattern, is to hide complex construction logic behind clear and explicit API.
Builder responsibility is to collect consumer "wishes" and return something that consumer can execute, to make "wishes" come true.

Here is builder implementation:

public class CommandBuilder
{
 private readonly DbCommand _Command;
 private readonly OutputParameterMapper _ParameterMapper = new OutputParameterMapper();
 private readonly ErrorCodeHandler _ErrorCodeHandler;

 public CommandBuilder(ObjectContext Ctx, string Name)
 {
  _ErrorCodeHandler = new ErrorCodeHandler(Name);
  _Command = Ctx.CreateStoreCommand(
   Name, 
   CommandType.StoredProcedure);
 }

 public IErrorCodeHandlerBuilder ErrorCodes
 {
  get
  {
   return _ErrorCodeHandler;
  }
 }

 public CommandBuilder Parameter(string Name, object Value)
 {
  if (Value == null)
  {
   Value = DBNull.Value;
  }

  _Command.Parameters.Add(new SqlParameter(Name, Value));
  return this;
 }

 public CommandBuilder AddPagingParameters(PageSortInfo PageSortInfo)
 {
  _Command.AddPagingParameters(PageSortInfo);
  return this;
 }

 public CommandExecutor Create()
 {
  if (_ErrorCodeHandler.ParameterName != null)
  {
   Output>int<(_ErrorCodeHandler.ParameterName, (ErrorCode) => { _ErrorCodeHandler.ThrowOnError(ErrorCode); });
  }

  return new CommandExecutor(
   _Command, 
   _ParameterMapper);
 }

 public CommandBuilder Output>T<(string ParameterName, Action>T< Func)
 {
  var Parameter = new SqlParameter(ParameterName, default(T));
  Parameter.Direction = ParameterDirection.Output;

  _Command.Parameters.Add(Parameter);
  _ParameterMapper.AddMap(ParameterName, Func);
  return this;
 }
}

After class consumer calls create on Builder, it returns Command executor. Command executor is already configured to execute desired stored procedures with parameters and map output parameters after execution.

I have cheated a bit with ErrorCodeHandler, it is a builder and an executor in the same time (say hello to SRP), but it doesn't causes much damage for now. The execution interface is hidden by IErrorCodeHandlerBuilder.

public class ErrorCodeHandler : IErrorCodeHandlerBuilder
{
 private readonly IDictionary>int,type< _ErrorsDictionary = new Dictionary>int,type<();

 private readonly string _ProcedureName;
 private string _ParameterName;

 public string ParameterName
 {
  get
  {
   return _ParameterName;
  }
 }

 public ErrorCodeHandler(string ProcedureName)
 {
  _ProcedureName = ProcedureName;
 }

 public IErrorCodeHandlerBuilder Handle(int ErrorCode, Type Exception)
 {
  _ErrorsDictionary.Add(ErrorCode, Exception);
  return this;
 }

 public IErrorCodeHandlerBuilder Handle<T>(int ErrorCode)
 {
  _ErrorsDictionary.Add(ErrorCode, typeof(T));
  return this;
 }

 public IErrorCodeHandlerBuilder Parameter(string Name)
 {
  _ParameterName = Name;
  return this;
 }

 public void ThrowOnError(int ErrorCode)
 {
  Type ExceptionType;
  if (_ErrorsDictionary.TryGetValue(ErrorCode, out ExceptionType))
  {
   var Exception = (Exception)Activator.CreateInstance(
    ExceptionType,
    String.Format("Stored procedure {0} returned error code {1}", _ProcedureName, ErrorCode));
   throw Exception;
  }
 }
}

The output parameter mapper is a bit tricky. It introduces 2 classes Map<T> and Map to bypass C# generic typing systems, and make polymorphism to work.

public class OutputParameterMapper
{
 private readonly List<IMap> _Map = new List<IMap>();
 private interface IMap
 {
  string ParameterName { get; set; }
  void InvokeSetter(object Value);
 }
 public void AddMap<T>(string ParameterName, Action<T> Setter)
 {
  _Map.Add(new Map<T>()
  {
   ParameterName = ParameterName, 
   Setter = Setter
  });
 }

 public void MapParameters(DbParameterCollection Parameters)
 {
  foreach (var Map in _Map)
  {
   var Value = Parameters[Map.ParameterName].Value;
   Map.InvokeSetter(Value);
  }
 }
 private class Map<T> : IMap
 {
  public Action<T> Setter { get; set; }
  public string ParameterName { get; set; }
  public void InvokeSetter(object Value)
  {
   Setter((T)Value);
  }
 }
}

Command executor class, when called blows all that stuff (triggers exception throwing and parameters mapping)

public class CommandExecutor
{
 private readonly DbCommand _Command;
 private readonly OutputParameterMapper _Mapper;
 private readonly ErrorCodeHandler _ErrorHandler;

 public CommandExecutor(DbCommand Command, OutputParameterMapper Mapper)
 {
  _Command = Command;
  _Mapper = Mapper;
 }

 public DbCommand Command
 {
  get
  {
   return _Command;
  }
 }

 public void ExecuteUpdate()
 {
  using (_Command.Connection.CreateConnectionScope())
  {
   _Command.ExecuteNonQuery();
   _Mapper.MapParameters(_Command.Parameters);
  }
 }

 public T ExecuteScalar<T>()
 {
  using (_Command.Connection.CreateConnectionScope())
  {
   var Scalar = (T)_Command.ExecuteScalar();
   _Mapper.MapParameters(_Command.Parameters);
   return Scalar;
  }
 }
}

The idea of builder pattern was given to me by my colleague, thanks. :)