C# 数据库更新重叠

我有 2 个列表，都包含代表各自球队中球员评分的整数。

Ex:
team1ListOfRatings: 9,1,4
team2ListOfRatings: 6,2,4

我希望将这些输入到我拥有的访问数据库中。但是，当我尝试输入它们时，如果与列表中的数字有任何相似之处(在上面显示的示例中，这是"4"(，那么第一个列表中的重复评级将分配给数据库中的团队 2。

它将产生以下结果：

Players in team 1: Ben (rating 9), Will(rating 1) 
Players in team 2: Philip(rating 6), Sam(rating 2), Bert(rating 4), Gary(rating 4)

从上面的例子中，很明显 Gary 需要在第一个团队中，但是因为我使用 for 循环，它会将任何重复项重新分配给最近 for 循环中使用的团队。这是我为此使用的代码：

// this adds the players to the database temporarily without a team
for (int i = 0; i < numberOfPlayers; i++)
{
    OleDbConnection connection = new OleDbConnection(CONNECTION STRING HERE);
    OleDbCommand command = new OleDbCommand();
    connection.Open();
    command.Parameters.Add("@name", OleDbType.VarWChar).Value = textBox[i].Text;
    command.Parameters.Add("@rating", OleDbType.Integer).Value = Convert.ToInt32(ratingBox[i].Text);
    command.CommandText = "INSERT INTO TotalPlayerName ([PlayerName], [Team], [Rating],  [Substitute], [Complete], [SportID]) VALUES (@name, null , @rating,  false, false, " + ID + ")";
    command.CommandType = CommandType.Text;
    command.Connection = connection;
    command.ExecuteNonQuery();
    connection.Close();
}

            //list of all ratings:
            List<int> ratingList = new List<int>();
            for (int i = 0; i < numberOfPlayers; i++)
            {
                ratingList.Add(Convert.ToInt32(ratingBox[i].Text));
            }
            ratingList.Sort();
            var count = ratingList.Count();
            // if even number of players, keep aside the one in the middle (as rating)
            int? middle = null;
            if (count % 2 != 0)
            {
                middle = ratingList[count / 2];
                ratingList.RemoveAt(count / 2);
            }
            var ratingListDesc = ratingList.OrderByDescending(i => i).ToList();
            var half = count / 2;
            var take = half % 2 != 0 ? half - 1 : half;
            var team1List = ratingList.Take(take).Where((r, i) => i % 2 == 0).ToList();
            team1List.AddRange(ratingListDesc.Take(take).Where((r, i) => i % 2 == 0));
            var team2List = ratingList.Take(take).Where((r, i) => i % 2 != 0).ToList();
            team2List.AddRange(ratingListDesc.Take(take).Where((r, i) => i % 2 != 0));
            // we just have to redistribute the remaining pair between each team
            if (half % 2 != 0)
            {
                team1List.Add(ratingList[half - 1]);
                team2List.Add(ratingListDesc[half - 1]);
            }
            // this part will add the team numbers to the database
            for (int i = 0; i < (count/2); i++)
            {
                OleDbConnection connection = new OleDbConnection(CONNECTION STRING HERE);
                OleDbCommand command = new OleDbCommand();
                connection.Open();
                command.CommandText = "update TotalPlayerName set Team = 1, Complete = true WHERE Team = null AND Complete = false AND Rating = " + team1List[i];
                command.CommandType = CommandType.Text;
                command.Connection = connection;
                command.ExecuteScalar();
                command.CommandText = "update TotalPlayerName set Team = 2, Complete = true WHERE Team = null AND Complete = false AND Rating = " + team2List[i];
                command.ExecuteScalar();
                connection.Close();

            }

有没有办法避免这种情况并添加评级而不会重叠？

注意：这里的代码只是作为我正在做的事情的一个例子，你不必阅读它来了解我想要做什么，但它可能会也可能不会帮助你理解。

List<int> ratingsList = new List<int>(); //Presume this is already populated.
List<int> team1 = new List<int>();
List<int> team2 = new List<int>();
ratingsList = ratingsList.Sort();
if(ratingsList.Count() % 2 != 0)
{
    ratingsList.Remove((ratingsList.Count()/2)+2));  //Integer divide intentional
}
for(int i = 0; i < ratingsList.Count() ; i++)
{
    if(i%2 != 0)
    {
        team1.Add(ratingsList[i]);
    }
    else
    {
        team2.Add(ratingsList[i]);
    }
}
foreach(var player in team1)
{
    //Add SQL Insert here
}
foreach(var player in team2)
{
    //Add SQL Insert here
}