>>>I'v run into this problem before, and I hit it again tonight:
>>>
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/80ed858a-64e3-4821-a1a3-44940fbf4e83>>>
>>>How about this for a solution - In my DataAccess class, whenver I receive a DbParameters collection, simply make a local copy of
>>>each parameter, place each copy into a new collection, then assign
that collection to the DbCommand object?
>>>
>>>This way, my collection of parameter never really gets used, like this:
>>>
>>>
>>>
>>>private List<DbParameter> _CopyParameters(List<DbParameter> Parameters)
>>>{
>>> List<DbParameter> RetVal = new List<DbParameter>();
>>>
>>> foreach (DbParameter Param in Parameters)
>>> {
>>> DbParameter NewParam = _Factory.CreateParameter();
>>> NewParam.ParameterName = Param.ParameterName;
>>> NewParam.Value = Param.Value;
>>> NewParam.Direction = Param.Direction;
>>>
>>> RetVal.Add(NewParam);
>>>
>>> Parameters.Remove(Param);
>>>
>>> }
>>>
>>> return RetVal;
>>>}
>>>
>>>
>>>Then use it like this:
>>>
>>>public int ExecuteNonQuery(string Command, List<DbParameter> Parameters, CommandType CommandType)
>>>{
>>> int RetVal = 0;
>>>
>>> DbCommand cmd = _GetCommand(Command);
>>>
>>> try
>>> {
>>> RetVal = cmd.ExecuteNonQuery();
>>> }
>>> catch (Exception e)
>>> {
>>> throw;
>>> }
>>>
>>> return RetVal;
>>>}
>>>
>>>
>>>Then in _GetCommand()...
>>>
>>>
>>>private DbCommand _GetCommand(string Command, List<DbParameter> Parameters, CommandType CmdType)
>>>{
>>> _GetFactory();
>>> _GetConnection();
>>>
>>> DbCommand cmd = _Factory.CreateCommand();
>>> cmd.CommandText = Command;
>>> cmd.Connection = _Connection;
>>> cmd.CommandType = CmdType;
>>>
>>> if (Parameters != null)
>>> {
>>> List<DbParameter> NewParams = _CopyParameters(Parameters);
>>>
>>> cmd.Parameters.Clear();
>>> cmd.CommandType = CmdType;
>>>
>>> foreach (DbParameter p in NewParams)
>>> {
>>> cmd.Parameters.Add(p);
>>> }
>>> }
>>>
>>> return cmd;
>>>}
>>>
>>>
>>>
>>>I implemented this and it seems to work. Thoughts?
>>
>>Could you organise things so that the parameter are removed after the command executes - that would mean you wouldn't need a copy everytime:
public int ExecuteNonQuery(List<SqlParameter> l)
>> {
>> SqlCommand sc = new SqlCommand();
>> foreach (SqlParameter p in l){sc.Parameters.Add(p);}
>> int RetVal = sc.ExecuteNonQuery();
>> foreach (SqlParameter p in l){sc.Parameters.Remove(p);}
>> return RetVal;
>> }
>
>I tried your suggestion. It throws a runtime error "Collection was modified...."
:-{ Oh well, it worked without the actual .ExecuteQuery()
>Oh well. Nice try. Truth is, I'm not unhappy with my workaround.
You still don't really need to make a copy. This should work (with all parameter properties handled):
public int ExecuteNonQuery(List<SqlParameter> l)
{
SqlCommand sc = new SqlCommand();
foreach (SqlParameter p in l)
{
sc.Parameters.Add(new SqlParameter(
p.ParameterName,
p.SqlDbType,
p.Size,
p.Direction,
p.Precision,
p.Scale,
p.SourceColumn,
p.SourceVersion,
p.SourceColumnNullMapping,
p.Value, p.XmlSchemaCollectionDatabase,
p.XmlSchemaCollectionOwningSchema,
p.XmlSchemaCollectionName));
}
return sc.ExecuteNonQuery();
}