2
\$\begingroup\$

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;
    }
\$\endgroup\$
4
\$\begingroup\$

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);
}
\$\endgroup\$
  • 1
    \$\begingroup\$ I hadn't heard of the Action delegate before -- thanks for pointing it out! \$\endgroup\$ – Brian Jan 14 '14 at 20:06
2
\$\begingroup\$

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;
    });
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.