How to update multiple rows in a table without using a loop?

Question

I'm certain there must be a way to do this, as ExecuteNonQuery 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:

            for (int i = 0; i < 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();
            };
        }

    }

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 ExecuteNonQuery. How can I get use that feature correctly?

Try never to write query statement inside your code like this. There are plenty of ways to avoid this. By the way, if you insist on avoiding any ORM or any cleaner way, at least try using a `DataTable` which let's you do the updates all at once. — Mohsen Kamrani, Sep 13 '16 at 02:52
Take a look at the most up-voted answer of this question: http://stackoverflow.com/questions/15273057/update-database-with-changes-made-to-datatable-confusion — Mohsen Kamrani, Sep 13 '16 at 02:53
I don't suppose you'd be able to explain the differences between that method and mine and why to use it? I think I know what to do with it, but I'm very new to this and want to be sure. — Space Ostrich, Sep 13 '16 at 03:33
Also, what do you mean by "never try to write a query statement inside your code like this"? Where else would I write it and why? — Space Ostrich, Sep 13 '16 at 03:35
1. If you want to change the name of any of you columns you should change your code in all the places that you use it like it; 2. If you want to switch to another DBMS it's quite likely that you have to change many of codes which are specific to the current DBMS. 3. It's not safe. 4. You should recompile the code for changing your connection string ........ — Mohsen Kamrani, Sep 13 '16 at 03:41
1. Why would I want to change the name of my columns? 2. Is there any feasable way to not have to change the code when switching to another DBMS? I figured that was just how things were. 3. What's not safe? 4. What? — Space Ostrich, Sep 13 '16 at 03:46

score 2 · Answer 1 · answered Sep 13 '16 at 02:53

2

Combine all your CustomerID's into a comma separated string first. Then instead of where CustomerID = @CustomerID use where CustomerID in (@CustomerIds)

@CustomerIds is your string

answered Sep 13 '16 at 02:53

rook

2,819
4
25
41

Could you give an example of this? I've tried to implement it myself but there's not enough information for me to get it right. – Space Ostrich Sep 27 '16 at 06:31

How to update multiple rows in a table without using a loop?

1 Answers1