>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;
}