How to update multiple rows / How to perform Operation on multiple records (C#, SQL Server)

Question

I have a database table name Players (ID, Name, Scores). here is my code to binding it with database

    private  void playerList(int team1_ID)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            string query = "SELECT player_name AS [Player Name] , Score AS [Score] FROM Players WHERE Team_id= " + team1_ID;

            SqlCommand cmd = new SqlCommand(query, con);
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds, "Players");
            dGridPlayers.DataSource = ds.Tables["Players"];
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            con.Close();
        }
    }

This code is working fine. It returns a list of 11 players and their Scores.

Now I want to perform some arithmetic operation on Score of each player and want to store back (update ) to the database table. Please tell how to do this.

(I don't want to hit SQL every time to update for each player's score. I want to update every players scores in one go after finishing the operations.)

Answer 1

To update records just apply something similar to what marc_s suggested.

If you want to update all records at once keep primary keys and new values in a structure like this

Dictionary<int, int> values = new Dictionary<int, int>();

Key for the dictionary will hold the primary key for the table and value will be the new score for that record. When you want to update the database just loop through all of these inside of the using SQLCommand block marc_s showed

using (SqlCommand cmd = new SqlCommand(updateQuery, con))
{
  // open connection, execute the UPDATE, close connection        
  conn.Open();
  foreach (KeyValuePair<int, int> row in values)
  {
       cmd.Parameters.Clear();
       cmd.Parameters.Add("@PK", SqlDbType.Int).Value = row.Key;
       cmd.Parameters.Add("@Score", SqlDbType.Int).Value = row.Value; 
       cmd.ExecuteNonQuery();
  } 
  conn.Close();
}