>>>>>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();
>> }
>
>This is essentially doing the same thing. For each param, create a new one with the same prop values, and assign it to the cmd. The
>only drawback in my case is that I'm using a factory and not all of the Param properties are supported. The technique I used doesn't
>care about that.
My point was that you don't need to make a copy of the whole list. Just using the existing one to feed in the new parameters is more efficient.
NBD .....