I've been using the Microsoft Data Access Block for a while and only came across something that isn't clear in its operation and can have some very nasty side effects. Inside the SqlHelper class, there is a method called AssignParameterValues which "...assigns an array of values to an array of SqlParameters". This sounds good although it's not clear that it does this using index position rather than matching on the parameter name. Therefore, if you create a procedure with 2 parameters both varchars, the order in which you pass the command parameters array to the method is the order in which the SqlHelper executes them against the server. This defeats the purpose of naming your parameters in your code. For example, if you create a method to build your parameters like this:
private static SqlParameter[] CreateMyParameters(string firstname, string surname)
{
SqlParameter firstnameParameter = new SqlParameter("@Firstname", SqlDbType.Varchar);
firstnameParameter.Value = firstname;
SqlParameter surnameParameter = new SqlParameter("@Surname", SqlDbType.Varchar);
surnameParameter.Value = surname;
new SqlParameter[] { firstnameParameter, surnameParameter };
}
then use the
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
method, your nicely named parameters array is attached to the command object based on their position on the array position and not their names. The method that does this is called AssignParameterValues.
However, the same problem does not happen when you use the method
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
The ExecuteReader just attaches the parameters as you created directly to the command object and therefore the parameter names are preserved.
This makes naming the parameters pretty useless for nonquery or scalar operations. It would be nice to see this method modified to use the parameter names which would take advantage of the some of the methods in the SqlHelperParameterCache class.
