Feddas - Shawn Featherly

We don't use Linq to SQL in my current project, instead we're creating our own Data Access layers. We pass the information through a DataAdapter, that formats the information for the DB, to the DataAccess layer, that creates the text command for SQL using SqlParameter types. I wanted a way to pass an array as a SqlParameter so that I could easily follow this coding pattern.

The fesibility was assured when I learned CSharp SqlParameters can by of any Object type before they're evaluated. So I could use them to transport Array types. I'd only have to convert them back to SQL friendly types in the DataAccess layer using a utility method of some sort.

I came across this post, http://www.willasrari.com/blog/using-sqlcommand-parameters-with-multiple-inserts/000199.aspx
Then developed the method below that should be called between a SqlCommand.Connection.Open and Close. This method detects arrays in the SqlParmeters and converts them to their base type. An example paramter that the SqlCommand can now contain would be:

SqlParameter columnNameParam = new SqlParameter("@ColumnName", new int[] {3,4,5,6});

The method (using our non-bubbling execption handling) is as follows:

private static Exception allowParamArrayOnExecuteNonQuery(SqlCommand command)
{
    List normalParams = new List();
    List arrayParams = new List();
    int arrayLength = 0;
    SqlParameter param;
    for (int index = 0; index < command.Parameters.Count; index++)
    {
        param = command.Parameters[index];
        if (param.Value as Array != null)
        {
            arrayParams.Add(command.Parameters[index]);

            if (arrayLength == 0)
                arrayLength = (param.Value as Array).Length;
            else if ((param.Value as Array).Length != arrayLength)
            {
                return new ArgumentException(String.Format("parameter arrays of Insert values must all have the same length. {0} has a different number of elements", param.ParameterName));
            }
        }
        else
        {
            normalParams.Add(command.Parameters[index]);
        }
    }

    //execute multiple inserts using parameter arrays
    if (arrayLength > 0)
    {
        List arrayValueAtIndex;
        for (int insertIndex = 0; insertIndex < arrayLength; insertIndex++)
        {
            arrayValueAtIndex = new List();
            command.Parameters.Clear();
            command.Parameters.AddRange(normalParams.ToArray());
            foreach (SqlParameter arrayParam in arrayParams)
            {
                arrayValueAtIndex.Add(new SqlParameter(arrayParam.ParameterName, (arrayParam.Value as Array).GetValue(insertIndex)));
            }
            command.Parameters.AddRange(arrayValueAtIndex.ToArray());
            command.ExecuteNonQuery();
        }
    }

    //execute single insert
    else if (command.ExecuteNonQuery() < 1)
    {
        return new ArgumentException(String.Format("SQL query did not effect any rows: {0}", command.CommandText));
    }

    return null; //no errors
}

Labels: ExcuteNonQuery, SQL