SqlParameter Exception: The SqlParameter is already contained by another SqlParameterCollection

Question

I am getting an exception

The SqlParameter is already contained by another SqlParameterCollection

I have moved my SqlParameter calls out of the foreach loop as I thought that was the issue but I am still getting the same exception.

private void SendToSQLServer_FactionStandings(Faction faction)
{
    string sqlCnnString = "Server=tcp:horde.database.windows.net,1433;Initial Catalog=horde_db;Persist Security Info=False;User ID=USERNAME@horde;Password=PASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

    SqlParameter sqlParaFaction_Faction_ID = new SqlParameter("@Faction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_ToFactionID = new SqlParameter("@ToFaction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_Standing = new SqlParameter("@Standing", SqlDbType.SmallInt);

    using (SqlConnection sqlCnn = new SqlConnection(sqlCnnString))
    {
        sqlCnn.Open();

        using (SqlTransaction sqlTrans = sqlCnn.BeginTransaction())
        {
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.Parameters.Clear();

                foreach (FactionStanding factionStanding in faction.FactionStandings)
                {
                    //
                    // - Insert Faction Standing Table Data
                    //

                    sqlParaFaction_Faction_ID.Value = factionStanding.Faction_ID; 
                    sqlCmd.Parameters.Add(sqlParaFaction_Faction_ID); // <---- Getting exception here

                    sqlParaFaction_ToFactionID.Value = factionStanding.ToFaction_ID;
                    sqlCmd.Parameters.Add(sqlParaFaction_ToFactionID);

                    sqlParaFaction_Standing.Value = factionStanding.DefaultStanding;
                    sqlCmd.Parameters.Add(sqlParaFaction_Standing);

                    string sqlCmdFactionStandings = "INSERT INTO FactionStandings (Faction_ID, ToFaction_ID, Standing) VALUES (@Faction_ID, @ToFaction_ID, @Standing)";

                    sqlCmd.Connection = sqlCnn;
                    sqlCmd.CommandText = sqlCmdFactionStandings;
                    sqlCmd.Transaction = sqlTrans;

                    sqlCmd.ExecuteNonQuery();
                }

                sqlTrans.Commit();
                sqlCmd.Parameters.Clear();
            }
        }
    }
}

Answer 1

You are adding the same SqlParameter multiple times to the same SqlCommand. The problem is that Parameters.Clear() removes the parameters from the command, but the parameters still think that the command 'owns' them. There's no way to remove this ownership. It's unintuitive and it's a common mistake :)

2 ways to fix this:

1. Create new SqlParameter objects for each loop iteration

2. Or use a single SqlCommand (like what you have) and pull the sqlCmd.Parameters.Add to outside the loop. e.g.

       SqlParameter sqlParaFaction_Faction_ID = new SqlParameter("@Faction_ID", SqlDbType.Int);
       SqlParameter sqlParaFaction_ToFactionID = new SqlParameter("@ToFaction_ID", SqlDbType.Int);
       SqlParameter sqlParaFaction_Standing = new SqlParameter("@Standing", SqlDbType.SmallInt);
   
       using (SqlConnection sqlCnn = new SqlConnection(sqlCnnString))
       {
           sqlCnn.Open();
           using (SqlTransaction sqlTrans = sqlCnn.BeginTransaction())
           {
               using (SqlCommand sqlCmd = new SqlCommand())
               {
                   sqlCmd.Connection = sqlCnn;
                   string sqlCmdFactionStandings = "INSERT INTO FactionStandings (Faction_ID, ToFaction_ID, Standing) VALUES (@Faction_ID, @ToFaction_ID, @Standing)";
                   sqlCmd.CommandText = sqlCmdFactionStandings;
                   sqlCmd.Transaction = sqlTrans;
                   sqlCmd.Parameters.Add(sqlParaFaction_Faction_ID);
                   sqlCmd.Parameters.Add(sqlParaFaction_ToFactionID);
                   sqlCmd.Parameters.Add(sqlParaFaction_Standing);
   
                   foreach (FactionStanding factionStanding in faction.FactionStandings)
                   {
                       //
                       // - Insert Faction Standing Table Data
                       //
                       sqlParaFaction_Faction_ID.Value = factionStanding.Faction_ID; 
                       sqlParaFaction_ToFactionID.Value = factionStanding.ToFaction_ID;
                       sqlParaFaction_Standing.Value = factionStanding.DefaultStanding;
   
                       sqlCmd.ExecuteNonQuery();
                   }
   
                   sqlTrans.Commit();
               }
           }
       }
   }