Simpler, Better

In the portfolio I am maintaining Microsoft.ApplicationBlocks.Data.dll is still being used as the Sql database access helper by most applications. As most applications use stored procedures, I witnessed lots of code snippets like the following one:
public DataSet Get(string domain, string logOnId) {
    SqlParameter[] sqlParams = new SqlParameter[2];
    sqlParams[0] = new SqlParameter("@domain", domain);
    sqlParams[1] = new SqlParameter("@loginid", logOnId);
    DataSet ds = SqlHelper.ExecuteDataset(DatabaseConstants.ConnectionString,
                 CommandType.StoredProcedure, "get_employee_by_loginid", sqlParams);
    return ds;
}

Nothing is wrong with the above code. But this small beast is quite busy in a microscopic way, compose parameters, set connection string, choose sql command type etc. If there are hundreds of stored procedures, there may be thousands of parameters, it is at least tedious to write down all those sql parameters. Needless to say there are lots of duplicated codes. If someone changes a parameter name, the related code has to be changed. From the maintenance perspective, it is a bad smell.

Is there a way to avoid hard code parameters like this? Yes, there is. Furthermore there is a simpler way to complete the same task.

In the methods Microsoft.ApplicationBlocks.Data.dll provides, a group of methods should be more emphasized. One of them is with this signature:

public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)

Equipped with it, we can refactor the above method into two statements:
public DataSet Get(string domain, string logOnId) {
       object[] parameters = new object[]{domain, logOnId};
       return SqlHelper.ExecuteDataset(DatabaseConstants.ConnectionString,
                                       "get_employee_by_loginid", parameters);
}

We can even refactor into one statement if the parameter number is small.
public DataSet Get(string domain, string logOnId) {
       return SqlHelper.ExecuteDataset(DatabaseConstants.ConnectionString,
                        "get_employee_by_loginid", new object[]{domain, logOnId};);
}

One more extreme step I prefer is to wrap up SqlHelper to create a base class with the method like this:
protected DataSet ExecuteDataset(string spName, params object[] parameterValues){
      return SqlHelper.ExecuteDataset(DatabaseConstants.ConnectionString, spName,  parameterValues);
}

So in the derived data access class we can say:

public DataSet Get(string domain, string logOnId)  {
     return ExecuteDataset("get_employee_by_loginid", new object[]{domain, logOnId});
}

The better point for this is that we only say connection string once in the same code pattern. In the context of derived classes we are not supposed to mind connection string, which is a different concern.

And also because we wrap the SqlHelper in the base class, it provides a very convenient possibility to shift to other helpers. In more general scope, all third party functionalities should be considered wrapped behind the interface or base classes to make them more updatable.

The advantages we get are:
1. Simpler code.
2. Only the order of stored procedure parameters matters.
3. Parameter values are automatically matched.
4. Parameter names are not referenced.

For Oracle SqlHelper, there is a similar group of methods.