SqlParameter is already contained by another SqlParameterCollection - does () {} use a cheat?

Question

When using using() {} (sic) blocks, as shown below, and assuming cmd1 not outside the scope of the first using() {} block, why should the second block throw an exception with a message

SqlParameter is already contained in another SqlParameterCollection

Does this mean that resources and / or descriptors, including parameters ( SqlParameterCollection ) attached to cmd1 , are not freed when it is destroyed at the end of the block?

 using (var conn = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True")) { var parameters = new SqlParameter[] { new SqlParameter("@ProductId", SqlDbType.Int ) }; using(var cmd1 = new SqlCommand("SELECT ProductName FROM Products WHERE ProductId = @ProductId")) { foreach (var parameter in parameters) { cmd1.Parameters.Add(parameter); } // cmd1.Parameters.Clear(); // uncomment to save your skin! } using (var cmd2 = new SqlCommand("SELECT Review FROM ProductReviews WHERE ProductId = @ProductId")) { foreach (var parameter in parameters) { cmd2.Parameters.Add(parameter); } } } 

NOTE. Doing cmd1.Parameters.Clear () immediately before the last bracket of the first block using () {} will save you from an exception (and possible difficulty).

If you need to reproduce, you can use the following scripts to create objects:

 CREATE TABLE Products ( ProductId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, ProductName nvarchar(32) NOT NULL ) GO CREATE TABLE ProductReviews ( ReviewId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, ProductId int NOT NULL, Review nvarchar(128) NOT NULL ) GO 

Answer 1

I suspect that SqlParameter "knows" which executes it, and that this information is not cleared when the command is located, but cleared when you call command.Parameters.Clear() .

Personally, I think that I avoid reusing objects in the first place, but it is up to you :)

Answer 2

Using blocks does not guarantee that the object is "destroyed"; it simply calls the Dispose() method. What actually happens depends on the particular implementation, in which case it is clearly not an empty collection. The idea is to ensure that unmanaged resources that are not cleared by the garbage collector are disposed of correctly. Since the parameter set is not an unmanaged resource, it is not entirely surprising that the dispose method is not cleared.

Answer 3

Adding cmd.Parameters.Clear (); after execution should be fine.

Answer 4

using determines the scope and makes an automatic call to Dispose() , for which we love it.

A link that falls out of scope will not cause the object to "disappear" if another object has a link to it, which in this case will be the case for parameters that have a link to cmd1 .

Answer 5

I also have the same problem, thanks @Jon, based on the fact that I gave an example.

When I called the function below, in which 2 times the same sqlparameter passed. The first time the database was called, it was called correctly, but the second time it threw the above error.

  public Claim GetClaim(long ClaimId) { string command = "SELECT * FROM tblClaim " + " WHERE RecordStatus = 1 and ClaimId = @ClaimId and ClientId =@ClientId"; List<SqlParameter> objLSP_Proc = new List<SqlParameter>(){ new SqlParameter("@ClientId", SessionModel.ClientId), new SqlParameter("@ClaimId", ClaimId) }; DataTable dt = GetDataTable(command, objLSP_Proc); if (dt.Rows.Count == 0) { return null; } List<Claim> list = TableToList(dt); command = "SELECT * FROM tblClaimAttachment WHERE RecordStatus = 1 and ClaimId = @ClaimId and ClientId =@ClientId"; DataTable dt = GetDataTable(command, objLSP_Proc); //gives error here, after add 'sqlComm.Parameters.Clear();' in GetDataTable (below) function, the error resolved. retClaim.Attachments = new ClaimAttachs().SelectMany(command, objLSP_Proc); return retClaim; } 

This is a common DAL function.

  public DataTable GetDataTable(string strSql, List<SqlParameter> parameters) { DataTable dt = new DataTable(); try { using (SqlConnection connection = this.GetConnection()) { SqlCommand sqlComm = new SqlCommand(strSql, connection); if (parameters != null && parameters.Count > 0) { sqlComm.Parameters.AddRange(parameters.ToArray()); } using (SqlDataAdapter da = new SqlDataAdapter()) { da.SelectCommand = sqlComm; da.Fill(dt); } sqlComm.Parameters.Clear(); //this added and error resolved } } catch (Exception ex) { throw; } return dt; } 

Answer 6

I ran into this specific error because I used the same SqlParameter objects as part of the SqlParameter collection to call the procedure multiple times. The reason for this IMHO error is that SqlParameter objects are associated with a specific SqlParameter collection, and you cannot use the same SqlParameter objects to create a new SqlParameter collection.

So instead of -

var param1 = new SqlParameter {DbType = DbType.String, ParameterName = param1, Direction = ParameterDirection.Input, Value = ""};

var param2 = new SqlParameter {DbType = DbType.Int64, ParameterName = param2, Direction = ParameterDirection.Input, Value = 100};

SqlParameter [] sqlParameter1 = new [] {param1, param2};

ExecuteProc (sp_name, sqlParameter1);

/*MISTAKE:

SqlParameter [] sqlParameter2 = new [] {param1, param2};

ExecuteProc (sp_name, sqlParameter2);

* /

Do this-

var param3 = new SqlParameter {DbType = DbType.String, ParameterName = param1, Direction = ParameterDirection.Input, Value = param1.Value};

var param4 = new SqlParameter {DbType = DbType.Int64, ParameterName = param2, Direction = ParameterDirection.Input, Value = param2.Value};

SqlParameter [] sqlParameter3 = new [] {param3, param4}; ExecuteProc (sp_name, sqlParameter3);

Answer 7

I came across this exception because I was unable to instantiate the parameter object. I thought he complained about two procedures with parameters with the same name. He complained that the same parameter was added twice.

  Dim aParm As New SqlParameter() aParm.ParameterName = "NAR_ID" : aParm.Value = hfCurrentNAR_ID.Value m_daNetworkAccess.UpdateCommand.Parameters.Add(aParm) aParm = New SqlParameter Dim tbxDriveFile As TextBox = gvNetworkFileAccess.Rows(index).FindControl("tbxDriveFolderFile") aParm.ParameterName = "DriveFolderFile" : aParm.Value = tbxDriveFile.Text m_daNetworkAccess.UpdateCommand.Parameters.Add(aParm) **aParm = New SqlParameter()** <--This line was missing. Dim aDDL As DropDownList = gvNetworkFileAccess.Rows(index).FindControl("ddlFileAccess") aParm.ParameterName = "AccessGranted" : aParm.Value = aDDL.Text **m_daNetworkAccess.UpdateCommand.Parameters.Add(aParm)** <-- The error occurred here. 

Answer 8

question
I was executing a SQL Server stored procedure from C # when I ran into this problem:

Exception Message [SqlParameter is already contained in another SqlParameterCollection.]

cause
I passed 3 parameters to my stored procedure. I added

 param = command.CreateParameter(); 

just once. I had to add this line for each parameter, which means a total of 3 times.

 DbCommand command = CreateCommand(ct.SourceServer, ct.SourceInstance, ct.SourceDatabase); command.CommandType = CommandType.StoredProcedure; command.CommandText = "[ETL].[pGenerateScriptToCreateIndex]"; DbParameter param = command.CreateParameter(); param.ParameterName = "@IndexTypeID"; param.DbType = DbType.Int16; param.Value = 1; command.Parameters.Add(param); param = command.CreateParameter(); --This is the line I was missing param.ParameterName = "@SchemaName"; param.DbType = DbType.String; param.Value = ct.SourceSchema; command.Parameters.Add(param); param = command.CreateParameter(); --This is the line I was missing param.ParameterName = "@TableName"; param.DbType = DbType.String; param.Value = ct.SourceDataObjectName; command.Parameters.Add(param); dt = ExecuteSelectCommand(command); 

Decision
Adding the following line of code for each parameter

 param = command.CreateParameter();