Unwanted return values in lambda functions?

Question

From a previous question, I got the idea of moving boilerplate code structures into a helper function and passing in a lambda function to it.

I then applied the idea to a data access class (code below). I'm not sure how to handle the result of my ProcessDatabaseCall helper function; it seems irrelevant to my pattern of use-cases. In this example I'm returning an IEnumerable; in other cases I'm returning int, School, etc.

 public IEnumerable<School> SchoolsList(string searchChars)
 {
 DataSet ds = new DataSet();
 List<School> schools = new List<School>();
 bool cool = ProcessDatabaseCall("dbo.usp_esrvs_API_SchoolList", cmd => {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@SearchValue", searchChars));
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 da.Fill(ds);
 return true;
 });
 //Foreach row in the data set add a json array object
 foreach (DataRow row in ds.Tables[0].Rows)
 {
 schools.Add(new School((int) row["esrvs_Account_ID"], row["SchoolName"].ToString()));
 }
 return schools;
 }
 private bool ProcessDatabaseCall(string procedureName, Func<SqlCommand,bool> processor)
 {
 using (SqlConnection Mackin1Conn = new SqlConnection(connectionString))
 {
 using (SqlCommand cmd = new SqlCommand(procedureName, Mackin1Conn))
 {
 return processor(cmd);
 }
 }
 }

The function used to look like this:

public IEnumerable<School> SchoolsList(string searchChars)
 {
 DataSet ds = new DataSet();
 List<School> schools = new List<School>();
 using (SqlConnection Mackin1Conn = new SqlConnection(connectionString))
 {
 using (SqlCommand cmd = new SqlCommand("dbo.usp_esrvs_API_SchoolList", Mackin1Conn))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@SearchValue", searchChars));
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 da.Fill(ds);
 }
 }
 foreach (DataRow row in ds.Tables[0].Rows)
 {
 schools.Add(new School((int) row["esrvs_Account_ID"], row["SchoolName"].ToString()));
 }
 return schools;
 }

Answer 1

Just replace Func delegate with Action delegate if you don't want return value:

private void ExecuteCommand(string commandText, Action<SqlCommand> action)
{
 using (var conn = new SqlConnection(connectionString)) 
 using (var cmd = new SqlCommand(commandText, conn))
 action(cmd);
}

Then getting schools will look like (with help of LINQ you can get rid of local list of schools):

public IEnumerable<School> GetSchools(string searchChars)
{
 DataSet ds = new DataSet();
 ExecuteCommand("dbo.usp_esrvs_API_SchoolList", cmd => {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@SearchValue", searchChars));
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 da.Fill(ds);
 });
 return from row in ds.Tables[0].AsEnumerable()
 select new School(row.Field<int>("esrvs_Account_ID"),
 row.Field<string>("SchoolName"));
}

NOTE: Consider to use Dapper both methods above are equivalent to:

private IEnumerable<School> GetSchools(string searchChars)
{
 using (var conn = new SqlConnection(connectionString)) 
 return conn.Query<School>("dbo.usp_esrvs_API_SchoolList",
 new { SearchValue = searchChars },
 commandType: CommandType.StoredProcedure);
}

• Brian: I hadn't heard of the Action delegate before -- thanks for pointing it out!

Answer 2

I'd say the best course of action is to make your ProcessDatabaseCall generic:

private T ProcessDatabaseCall<T>(string procedureName, Func<SqlCommand, T> processor)
{
 using (SqlConnection Mackin1Conn = new SqlConnection(connectionString))
 {
 using (SqlCommand cmd = new SqlCommand(procedureName, Mackin1Conn))
 {
 return processor(cmd);
 }
 }
}

Your SchoolList call then turns into:

public IEnumerable<School> SchoolsList(string searchChars)
{ 
 return ProcessDatabaseCall("dbo.usp_esrvs_API_SchoolList", cmd => {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@SearchValue", searchChars));
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds);
 var schools = new List<School>();
 //Foreach row in the data set add a json array object
 foreach (DataRow row in ds.Tables[0].Rows)
 {
 schools.Add(new School((int) row["esrvs_Account_ID"], row["SchoolName"].ToString()));
 }
 return schools;
 });
}