none
The SqlParameter is already contained by another...

    Question

  •  

    Hi All,

    I have the following code:

    List<SqlParameter> lstParams = new List<SqlParameter>();
    SqlParameter pGuidAccount = new SqlParameter("@guid_account", guid_account);
    lstParams.Add(pGuidAccount);


    ChkValue = AppUtility.DBCore.SqlExecuteScalar("isp_has_transaction", stParams);
               
    ChkValue = AppUtility.DBCore.SqlExecuteScalar("isp_has_totalled_to_account", lstParam2);

    As you can see , the code calls two stored procedures using the same SQLParameter Collection, for both SP need the same value stored in the @guid_account.

    I thought I can do it, but at the second call to the stored procedure isp_has_totalled_to_account, it returns with the following error message:

       "The SqlParameter is already contained by another SQLparameter collection..."

    Of-cource, I haved to fix it by creating another SqlParameter collection with another SqlParameter for the call to the second stored procedure.  But I really hate it.  Why do I have to use two different collections when they are exactly the same.  In my opinion, the SqlParameterCollection that I pass to the ADO.SqlExecute.... should be read only.  Apparently, the collection is being modified by the ADO code, and that causing it cannot be re-used for another ADO call.

    Any comment/suggestion is appreciated.

     

     

     

     

     

    Saturday, April 08, 2006 5:00 AM

Answers

  • Hi!

    AppUtility.DBCore.SqlExecuteScalar() is the problem. It doesn't release your SQLParameter object (I suspect it simply add this param from your list to the SQLCommand.Parameters list and never Dispose() SQLCommand). If it's your's library - fix it, if it's impossible to fix - find another one, because this one don't respect resources.

    Saturday, April 08, 2006 5:21 AM
    Moderator

All replies

  • Hi!

    AppUtility.DBCore.SqlExecuteScalar() is the problem. It doesn't release your SQLParameter object (I suspect it simply add this param from your list to the SQLCommand.Parameters list and never Dispose() SQLCommand). If it's your's library - fix it, if it's impossible to fix - find another one, because this one don't respect resources.

    Saturday, April 08, 2006 5:21 AM
    Moderator
  • Hi David and S.G.,

    First, I feel really grateful to S.G. for having pointed out the problem coming from your method: AppUtility.DBCore.SqlExecuteScalar().

    There could be the same exception thrown out when the method: SqlParaMeterCollection.Add() called.

    Let's check the source code of SqlParameterCollection.Add() in .net framework with "Reflector" tool

    public
    SqlParameter Add(SqlParameter value)
    {
    this
    .OnSchemaChanging();
    this
    .AddWithoutEvents(value);
    return
    value;

    }


    Go on with source code of "AddWithoutEvents"

    private void
    AddWithoutEvents(SqlParameter value)
    {
    this.Validate(-1
    , value);
    value.Parent
    = this
    ;
    this
    .ArrayList().Add(value);

    }


    Here, value.Parent = this which should grab our attention and enables tge relationship between "
    SqlParameter value" and "SqlParameterCollection". "SqlParameter value" can only belong to one SqlParameterCollection at the same time.?Let check "Validate(-1, value)":

    internal void Validate(int
    index, SqlParameter value)
    {
    if (value == null
    )
    {
    throw ADP.ParameterNull("value", this, this
    .ItemType);

    }

    if (value.Parent != null)
    {
    if (this !=
    value.Parent)
    {
    throw ADP.ParametersIsNotParent(this.ItemType, value.ParameterName, this
    );

    }

    if (index != this.IndexOf(value))
    {
    throw ADP.ParametersIsParent(this.ItemType, value.ParameterName, this
    );

    }


    }

    string text1 = value.ParameterName;
    if (!
    ADP.IsEmpty(text1))
    {
    return
    ;

    }

    index
    = 1;
    do

    {
    text1
    = string.Concat("Parameter"
    , index.ToString());
    index
    = (index + 1
    );

    }

    while ((-1 != this.IndexOf(text1)));
    value.ParameterName
    =
    text1;

    }

    From source code above, we can find out how the exception goes out. If "value" is judged by another SqlParameterCollection instance within the condition "this != value.Parent, exception will be thrown out.


          In other scenario using SqlParameterColletion, there might be static member functions without setting some parameters every time called or multithreading to cause the problem. I hope this can answer your question with SqlParameterCollection.


          Based on my understanding, in your first scenario, the source could be AppUtility.DBCore.SqlExecuteScalar() which seems like a static member function. If you can provide the source code or assembly of AppUtility.DBCore, I can check more details for you, or if you are interested in the mechanism of AppUtility.DBCore.SqlExecuteScalar(), you can check that with Reflactor yourself.

        Finally, two suggestions for you to improve development:

    1.  Cautious not to use static member functions

    2. Use SqlParameter safely: new an instance every time you'd like to use it.

       I sincerely hope it can be of help to you. Please feel free to ask if you have any questions.

    Best regards,

    Cleo

     

    Monday, April 10, 2006 2:05 PM
  • Hi Cleo!

    I think you right, if we have no access to DBCore class, best solution is to use new parameter every time we call it's methods. But this is really not good - waste of memory and CPU.

    If we have access to DBCore (or we create it) - we must change behavior, because it's not good to take external parameter and leave it useless after work.

    One thing I don't catch - why you suggest be cautious with static methods? They aren't so bad and in this case I don't think instance method do less harm.

    BTW, cool idea to use Reflector to get to the bottom of the problem. Somehow I don't use it yet  But caution must be taken here - client code must stay with public library contract to survive next time new library version comes. We must not make assumptions about internal implementation details that may change in future.

    Monday, April 10, 2006 2:40 PM
    Moderator
  • Hi S.G,

    So nice to receive creative ideas from you . I should apologize to have made a literal mistake: be cautious not to use static method where I leaked "not". You are totally right that static methods are so bad as what I'm thinking about. 'coz a friend who once used static methods rightly got the same exception reproduced, which hints me to give a better practice to use SqlParameter without static methods.

    Thanks and best regards,

    Cleo

    Monday, April 10, 2006 5:16 PM
  • I think people who develop static methods must keep in mind - they must not leave side effects after job done
    Monday, April 10, 2006 5:50 PM
    Moderator
  •  

    Thanks all very much for replying and pointing out possible problems in the code.  The AppUtility.DBCode.SQLExec...() actually is not a static function (as you can see in the code below) at all.  Basically, the AppUtility.DBCore is a static function that returns the application object pointer of a class that handles all database management code, and especially, handles SQL server connection pool - works out very well since it isolate all DB code in a class, uses the same connection string for the connection pool, which makes my client/server application performs reasonably fast. 

    Any way, I added the SqlCommand.Parameters.Clear() to the code and the problem is gone.  As you can see in my code, the SqlCommand dbCommand is created in the function and should be destroyed at the end, which I think it is, as C# is a managed language that handles garbage collections.  It was my mistake to think that it also destroys all of its children objects (Parameters). 

    Thanks all again for your expertise advices/recommendations.

    public object SqlExecuteScalar(string szQueryName, CommandType QueryType, List<SqlParameter> lstParams)

    {         

            object FirstColumnData;

            SqlConnection dbConnection = new SqlConnection(this.ConnectionString);

            try

            {

                 this.CheckConnectionPool();

                 dbConnection.Open();

                 SqlCommand dbCommand = new SqlCommand(szQueryName, dbConnection);

                 dbCommand.CommandType = QueryType;

                 if (lstParams != null)

                 {

                     IEnumerator<SqlParameter> eParm = lstParams.GetEnumerator();

                    while (eParm.MoveNext())

                    {

                           dbCommand.Parameters.Add(eParm.Current);

                    }

                 }

                 FirstColumnData = dbCommand.ExecuteScalar();

                 dbCommand.Parameters.Clear();  // I just added this code to clear the parameters, and it works

                 dbConnection.Close();

           }

           catch (SqlException SqlError)

          {

               FirstColumnData = DBNull.Value;

               if (dbConnection.State == ConnectionState.Open)

               {

                   dbConnection.Close();

               }

               if (this.Parent != null && this.Parent is IDBManager)

               {

                    ((IDBManager)this.Parent).IMsgBox(SqlError);

              }

              else

             {

                     throw SqlError;

              }

     }

    Thursday, April 13, 2006 7:42 PM
  • Hi David!

    Close() do not destroy object, just make it in closed state and release unmanaged resources. Memory will be free eventually after GC (Garbage Collector) do his job.

    I suggest you to read about "using" statement in C#. It will make your life easier - you will not need to Close() your connections or Dispose() other objects. Here is sample how to safely work with database:

    using(SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        // do something here
    }

    // connection.Close() automatically called even if exception happens.
    Friday, April 14, 2006 4:41 AM
    Moderator
  • After
    cmd.ExecuteReader or similar method call
    cmd.Parameters.Clear();
    May work.
    Monday, December 25, 2006 9:17 AM
  • I end up changed the SqlHelper and it works for me.

     

    Inside ALL method that contains

    Code Snippet
    using (SqlCommand cmd = new SqlCommand())

     

     

    Add a "try ... catch" block inside the "using"

    Code Snippet

            try
            {
                // Original Code
            }
            catch
            {
                cmd.Parameters.Clear();
                throw;
            }

     


    Example

    Code Snippet

    public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
      {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch
            {
                cmd.Parameters.Clear();
                throw;
            }

        }
      }

     

     

    Saturday, May 17, 2008 3:31 AM
  • Is it have any solution , if i want to reuse the same parameter to another stored procedure
    Friday, July 29, 2011 8:52 AM