SqlParameter异常: SqlParameter已经包含在另一个SqlParameterCollection中

Question

我得到一个例外

SqlParameter已经包含在另一个SqlParameterCollection中

我已经将我的SqlParameter调用从foreach循环中移出，因为我认为这是问题所在，但我仍然得到了相同的例外。

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

您要多次向同一个SqlParameter添加相同的SqlCommand。问题是Parameters.Clear()从命令中删除了参数，但是参数仍然认为命令“拥有”它们。没有办法取消这个所有权。这是不直观的，也是一个常见的错误:)

解决这一问题的2种方法：

1. 为每个循环迭代创建新的SqlParameter对象
2. 或者使用单个SqlCommand (就像您所拥有的那样)并将sqlCmd.Parameters.Add拉到循环之外。例如： SqlParameter sqlParaFaction_Faction_ID =新SqlParameter("@Faction_ID"，SqlDbType.Int)；SqlParameter sqlParaFaction_ToFactionID =新SqlParameter("@ToFaction_ID"，SqlDbType.Int)；SqlParameter sqlParaFaction_Standing = new SqlParameter(“@ sqlCnn”，SqlDbType.SmallInt)；new (SqlConnection sqlCnn= new SqlConnection(SqlConnection)){SqlConnection()；使用(SqlTransaction sqlTrans = sqlCnn.BeginTransaction()) { new (SqlCommand sqlCmd =新SqlCommand()) { sqlCmd.Connection = sqlCnn；string sqlCmdFactionStandings = "INSERT INTO FactionStandings (Faction_ID，ToFaction_ID，string)值(@Faction_ID，@ToFaction_ID，@sqlCmd)“；sqlCmd.CommandText = sqlCmdFactionStandings；sqlCmd.Transaction =sqlCmdFactionStandings；sqlCmd.Parameters.Add(sqlParaFaction_Faction_ID)；sqlCmd.Parameters.Add(sqlParaFaction_ToFactionID)；sqlCmd.Parameters.Add(sqlParaFaction_Standing)；foreach (FactionStanding factionStanding in faction.FactionStandings) {/-插入派系站立表数据// sqlParaFaction_Faction_ID.Value = factionStanding.Faction_ID；sqlParaFaction_ToFactionID.Value = factionStanding.ToFaction_ID；sqlParaFaction_Standing.Value = factionStanding.DefaultStanding；sqlCmd.ExecuteNonQuery()；}sqlTrans.Commit()}