Friday, June 24, 2011
Multiple Inserts with ExecuteNonQuery
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:
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)
{
ListnormalParams = new List ();
ListarrayParams = 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)
{
ListarrayValueAtIndex;
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
Subscribe to Posts [Atom]