Refactoring code to use the operator
I have a dal layer with a lot of methods, all of them call stored procedures, some return lists (therefore using SqlDataReader
), others only a specific value.
I have a helper method that creates SqlCommand
:
protected SqlCommand CreateSprocCommand(string name, bool includeReturn, SqlDbType returnType)
{
SqlConnection con = new SqlConnection(this.ConnectionString);
SqlCommand com = new SqlCommand(name, con);
com.CommandType = System.Data.CommandType.StoredProcedure;
if (includeReturn)
com.Parameters.Add("ReturnValue", returnType).Direction = ParameterDirection.ReturnValue;
return com;
}
Now my middle (too simplistic) method looks like this:
SqlCommand cmd = CreateSprocCommand("SomeSprocName"); //an override of the above mentioned method
try {
cmd.Connection.Open();
using (var reader = cmd.ExecuteReader()) {
//some code looping over the recors
}
//some more code to return whatever needs to be returned
}
finally {
cmd.Connection.Dispose();
}
Is there a way to refactor this so that I don't lose my helper function (this is quite a bit repetitive) and still be able to use it using
?
a source to share
One way is to change it from returning a command to accepting a delegate that uses the command:
protected void ExecuteSproc(string name,
SqlDbType? returnType,
Action<SqlCommand> action)
{
using (SqlConnection con = new SqlConnection(this.ConnectionString))
using (SqlCommand com = new SqlCommand(name, con))
{
con.Open();
com.CommandType = System.Data.CommandType.StoredProcedure;
if (returnType != null)
{
com.Parameters.Add("ReturnValue", returnType.Value).Direction =
ParameterDirection.ReturnValue;
}
action(com);
}
}
(Note that I also removed the parameter includeReturn
and made returnType
it nullable instead . Just pass null
for "no return value".)
You would use this with a lambda expression (or anonymous method):
ExecuteSproc("SomeName", SqlDbType.DateTime, cmd =>
{
// Do what you want with the command (cmd) here
});
This way, the deletion is in the same place as the creation and the caller just doesn't have to worry about it. I'm becoming a real fan of this pattern - it's much cleaner now that we have lambda expressions.
a source to share
You can do it:
protected static SqlCommand CreateSprocCommand(SqlConnection con, string name, bool includeReturn, SqlDbType returnType)
{
SqlCommand com = new SqlCommand(name, con);
com.CommandType = System.Data.CommandType.StoredProcedure;
if (includeReturn)
com.Parameters.Add("ReturnValue", returnType).Direction = ParameterDirection.ReturnValue;
return com;
}
and call it like this:
using (SqlConnection con = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = CreateSprocCommand(con, "SomeSprocName", true, SqlDbType.Int)
{
cmd.Connection.Open();
using (var reader = cmd.ExecuteReader())
{
//some code looping over the recors
}
//some more code to return whatever needs to be returned
}
a source to share