One of my colleagues asked me today if I could write a utility function that would make his life more easy.
The objective was to call a stored procedure n-times for every item in a list. Of course some of the properties on every item had to be mapped to parameters in the stored procedure.
The desired syntax would be something like:
builder.SetStoredProcedureNameTo( " some sproc name ")
.Execute ( customerList ,
(s,t) => s.AddParameterWIthValue( parameterName, t.PROPERTY_NAME_1) ,
(s,t) => s.AddParameterWIthValue( parameterName, t.PROPERTY_NAME_2) ,
(s,t) => s.AddParameterWIthValue( parameterName, t.PROPERTY_NAME_3)
);
I already have a fluent wrapper class to make our ADO.Net a bit more friendly, the interface looks like:
public interface ICustomSqlCommandBuilder
{
ICustomSqlCommandBuilder SetStoredProcedureNameTo(string storedProcedureName);
ICustomSqlCommandBuilder AddParameterWithValue(string parameterNameInDatabase, object value);
ICustomSqlCommandBuilder AddExlicitOutputIntParameterToTheCommandFor(string parameterName);
ICustomSqlCommandBuilder AddExlicitOutputDateTimeParameterToTheCommandFor(string parameterName);
IList<TReturnType> ExecuteReaderFor<TReturnType>()
where TReturnType : new();
}
Nothing special except for the ExecuteReaderFor<TReturnType>() but that's for another post.
Having the fluent interface on ICustomSqlCommandBuilder combined with the lambda magic explained in this post I came up with the following signature:
ICustomSqlCommandBuilder ExecuteScalarForThisList<T>(
IList<T> list,
params Action<ICustomSqlCommandBuilder, T>[] actionList);
The general idea is to apply
(s, t) => s.AddParameterWithValue("parameterName", t.PROPERTY_NAME)
to every property in the object<T> that needs to be mapped against a parameter in the stored procedure. Having the params allows us to map as many properties to parameters in the stored procedure as we like. If we then loop through all the items in the list first and apply all actions in the actionList per item, we can then call ExecuteScalar on the stored procedure.
foreach (var item in list)
{
foreach (var action in actionList)
{
action.Invoke(this, item);
}
var result = sqlCommand.ExecuteScalar();
sqlCommand.Parameters.Clear();
}
Now, another requirement was to wrap it in a transaction and also to have the verification of the result a bit more flexible. All in all not too complicated, just a matter of introducing another Action:
public ICustomSqlCommandBuilder ExecuteScalarForThisList<T>(
IList<T> list,
Action<object> verify,
params Action<ICustomSqlCommandBuilder, T>[] actionList)
{
sqlCommand.Connection.Open();
SqlTransaction transaction = sqlCommand.Connection.BeginTransaction();
sqlCommand.Transaction = transaction;
try
{
foreach (var item in list)
{
foreach (var action in actionList)
{
action.Invoke(this, item);
}
var result = sqlCommand.ExecuteScalar();
verify(result);
sqlCommand.Parameters.Clear();
}
transaction.Commit();
}
catch (SqlException)
{
transaction.Rollback();
throw;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
return this;
}
Including a bit of exceptionally brittle exception handling and calling the stored procedure on a list of Customers now resolves to:
Action<object> resultConstraint = result =>
{
if (result != null)
{
if ((int)result > 1000)
{
throw new Exception("result should be less than 1000!");
}
}
};
builder
.SetStoredProcedureNameTo("sens_sp_modifyCustomerIncome")
.ExecuteScalarForThisList(
customerList,
resultConstraint,
(s, t) => s.AddParameterWithValue("id", t.ID),
(s, t) => s.AddParameterWithValue("income", t.Income)
//etc for the rest of the properties
);
I really, really like this syntax!