Getting The SqlParameter is already contained by another SqlParameterCollection error with c# / ASP.net app

Question

I have a generic method which is adding parameters to a cmd.parameters collection. I am getting the following error when 2 people are hitting the same stored procedure at the same time:

The SqlParameter is already contained by another SqlParameterCollection.

I have already searched within StackOverflow and also elsewhere on the web but so far I am not able to rectify the problem. My code is as follows:

protected DataSet ExecuteDataSet(string StoredProcName, List<TParameter> Params) {
bool internalOpen = false;
DataSet resultDataSet = null;
TDataAdapter dataAdapter;
TCommand cmd;

try {
    resultDataSet = new DataSet();
    dataAdapter = new TDataAdapter();
    cmd = new TCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = StoredProcName;

    if (transaction != null) {
        cmd.Transaction = transaction;
        cmd.Connection = transaction.Connection;
    } else {
        cmd.Connection = connection;
    }

    if (Params != null && Params.Count > 0) {
        foreach (TParameter param in Params) {
            cmd.Parameters.Add(param);
        }
    }

    dataAdapter.SelectCommand = cmd;

    if (connection.State == ConnectionState.Closed) {
        connection.Open();
        internalOpen = true;
    }

    dataAdapter.Fill(resultDataSet);
    dataAdapter.SelectCommand.Parameters.Clear();

    cmd.Parameters.Clear();
    return resultDataSet;
} catch {
    throw;
} finally {
    if (internalOpen) {
        connection.Close();
    }

}

The error is happening during the foreach loop but despite clearing parameters and applying some other attempted fixes I have been unable to prevent this error from happening.

This is being called from another method using the following:

 result = base.ExecuteDataSet(procName,this.ConvertArrayList(this.ParamsList));

It is an older c# / asp.net (web forms) application that I am using, so the code reflects this.

The error message begins like so:

[ArgumentException: The SqlParameter is already contained by another SqlParameterCollection.] System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value) +5955779 System.Data.SqlClient.SqlParameterCollection.Add(Object value) +34

I am testing using 2 browsers logging in as 2 users at the same time and clicking a link to a page which pulls multiple records. One of these users gets the error while the other gets the correct page with returned results as expected.

Any help would be appreciated. Apologies if I haven't explained this well enough but I've been looking at this all day and haven't been able to progress.

Thanks in advance.

EDIT:

The ParamsList is set/get like this:

public ArrayList ParamsList {
        get {
            //Check to see if the parameters list has been initialised.
            if (m_paramsList == null) {
                //Create a new empty parameters list to pass back.
                m_paramsList = new ArrayList();
            }
            return m_paramsList;
        }
        set {
            m_paramsList = value;
        }
    }

And populated like so:

internal void AddParameter(string name, string value) {
        IDbDataParameter param = CreateStringParameter(name);

        param.Value = GetValueFromString(value);

        Dal.ParamsList.Add(param);
    }

for every type of parameter type....

internal void AddParameter(string name, double? value) {

internal void AddParameter(string name, byte[] value) {

etc...

Answer 1

Looking through the code in the question... a lot of it didn't accomplish what the author likely intended. The try/catch/finally, for example, was completely worthless because the catch block just re-threw the same exception and using the Fill() method meant the finally block was not needed. Other code has similar issues.

Except for the transactions, you could reduce the code down to just this, assuming TCommand and company fully implement the ADO.Net providers, where the reduced code actually increases performance, safety, and utility:

protected DataSet ExecuteDataSet(string StoredProcName, IEnumerable<TParameter> Params = null) 
{    
    DataSet resultDataSet = new DataSet();
    using (var cn = new TConnection(connection.ConnectionString))
    using (var cmd = new TCommand(StoredProcName, cn))
    using (var adapter = new TAdapter(cmd))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        if (Params != null) 
        {
            foreach (TParameter param in Params)
            {
                cmd.Parameters.Add(param);
            }
        }    
        adapter.Fill(resultDataSet);
    }
    return resultDataSet;        
}

But we do have that transaction value, and that's enough to break the using pattern here. Because of that, you'll want to effectively double the code length, to account for both variants. Yes, the using pattern really is that important, that you would effectively double the code length to keep it where possible:

protected DataSet ExecuteDataSet(string StoredProcName, IEnumerable<TParameter> Params = null) 
{  
    DataSet resultDataSet = new DataSet(); 
    if (transaction == null)
    { 
        using (var cn = new TConnection(connection.ConnectionString))
        using (var cmd = new TCommand(StoredProcName, cn))
        using (var adapter = new TAdapter(cmd))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            if (Params != null) 
            {
                foreach (TParameter param in Params)
                {
                    cmd.Parameters.Add(param);
                }
            }    
            adapter.Fill(resultDataSet);
        }
    }
    else
    {
        using (var cmd = new TCommand(StoredProcName, transaction.Connection))
        using (var adapter = new TAdapter(cmd))
        {
            cmd.Transaction = transaction;
            cmd.CommandType = CommandType.StoredProcedure;

            if (Params != null) 
            {
                foreach (TParameter param in Params)
                {
                    cmd.Parameters.Add(param);
                }
            }    
            adapter.Fill(resultDataSet);
        }
    }
    return resultDataSet; 
}

Finally, none of this will fix your problem. The problem you're seeing is caused by code elsewhere trying too hard to re-use Parameter objects. You'll need to look at other code to fix the issue.