title: How to update multiple rows in a table without using a loop?
body: <p>I'm certain there must be a way to do this, as <code>ExecuteNonQuery</code> wouldn't exist if you couldn't alter more than one row at a time, but I can't figure out how. At the moment I'm updating the values in a Customer table with this code:</p>
  
  <pre><code>            for (int i = 0; i &lt; dataGridView1.RowCount; i++)
          {
  
              string connectionString = @"Data Source = A103-17\SQLEXPRESS17; Initial Catalog = CarRental; Integrated Security = True";
  
              string myUpdate = "UPDATE [CarRental].[dbo].[Customer] " +
  //                "set [CustomerID] = @CustomerID, " +
              "set [FirstName] = @FirstName, " +
              "[LastName] = @LastName, " +
              "[StreetNo] = @StreetNo, " +
              "[StreetName]= @StreetName, " +
              "[Suburb] = @Suburb, " +
              "[State] = @State, " +
              "[PostCode] = @PostCode, " +
              "[MobPhone] = @MobPhone, " +
              "[DriverLicNo] = @DriverLicNo, " +
              "[CreditCardType] = @CreditCardType, " +
              "[CreditCardNo] = @CreditCardNo, " +
              "[ExpDate] = @ExpDate, " +
              "[NameOnCreditCard] = @NameOnCreditCard " +
              "WHERE [CustomerID] = @CustomerID" +
              ";";
  
              SqlConnection con = new SqlConnection(connectionString);
              using (SqlCommand ApptUpdate = new SqlCommand(myUpdate, con))
              {
                  ApptUpdate.Parameters.AddWithValue("@CustomerID", dataGridView1.Rows[i].Cells["CustomerID"].Value ?? DBNull.Value);
                  ApptUpdate.Parameters.AddWithValue("@FirstName", dataGridView1.Rows[i].Cells["FirstName"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@LastName", dataGridView1.Rows[i].Cells["LastName"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@StreetNo", dataGridView1.Rows[i].Cells["StreetNo"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@StreetName", dataGridView1.Rows[i].Cells["StreetName"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@Suburb", dataGridView1.Rows[i].Cells["Suburb"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@State", dataGridView1.Rows[i].Cells["State"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@PostCode", dataGridView1.Rows[i].Cells["PostCode"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@MobPhone", dataGridView1.Rows[i].Cells["MobPhone"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@DriverLicNo", dataGridView1.Rows[i].Cells["DriverLicNo"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@CreditCardType", dataGridView1.Rows[i].Cells["CreditCardType"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@CreditCardNo", dataGridView1.Rows[i].Cells["CreditCardNo"].Value ?? "Null");
                  ApptUpdate.Parameters.AddWithValue("@ExpDate", dataGridView1.Rows[i].Cells["ExpDate"].Value ?? DateTime.Now);
                  ApptUpdate.Parameters.AddWithValue("@NameOnCreditCard", dataGridView1.Rows[i].Cells["NameOnCreditCard"].Value ?? "Null");
  
                  con.Open();
                  MessageBox.Show(ApptUpdate.ExecuteNonQuery().ToString());
                  con.Close();
              };
          }
  
      }
  </code></pre>
  
  <p>This works, but the messagebox will pop up showing a value of 1 for each row, rather than once showing a value corresponding to how many rows are changed. I can't move the execution outside of the loop, so I can't figure out how to get any use out of the rows affected return of <code>ExecuteNonQuery</code>. How can I get use that feature correctly?</p>