SQL UPDATE Loop Non-Sequential Keys

Question

I am writing a C# application that will update the fields in a SQL Server database. The current algorithm I am testing simply pulls the data from a "State" field, stores each value in an ArrayList, capitalizes it, and then writes it back to the database. I am having a problem with logic.

I pull all of the values into the ArrayList and capitalize them. This is working fine. I now have an array with, for instance, 100 values (i.e., myArray[0] - myArray[99] ). I then use a FOR loop to write the values back to the database:

for (int i = 0; i <= (myArray.Count - 1); i++)
{
   SqlCommand myCommand = 
      new SqlCommand("UPDATE myList SET State = '" + recordArray[i].ToString() + 
                     "' WHERE uniqueID = '" + (i + 1) + "'", dbConnection);
   myCommand.ExecuteNonQuery();
}

I am using "uniqueID" in the above example to place these values according to primary key. However, the problem is that the primary key is only nearly sequential; there are a few missing numbers in the sequence. Thus, even though I have exactly the number of values that I need, and they are in the correct order in the array to be pushed back out to the database, once I reach a lapse in the sequence, the rest of the data is placed in the wrong field. I know this is a lapse in my logic, but I am at a loss as to how I can ensure that every individual value is being placed correctly.

Thanks in advance for the help.

Answer 1

You need to grab the primary key values for the same rows, and keep track of that along with the data, so that you can update the correct row in the end.

Answer 2

Unless you're doing this purely as an exercise, you do know you can just execute the update directly?

UPDATE myList SET State = Upper(State)

Answer 3

So many things wrong here...

1. Never NEVER NEVER use dynamic SQL like that. What if one of your "state" has an apostrophe in it?
2. Unless this is .Net 1.0 or 1.1, you should NOT be using an ArrayList. Use System.Collections.Generic.List<string> instead.
3. Don't create 99 SqlCommand objects. Create 1 SqlCommand object, and update the parameter value on each iteration through the loop.
4. Create your SqlCommand (and even more importantly, SqlConnection) object with a using statement, to make sure the unmanaged resources are released promptly if an exception is thrown.
5. Most of all, all this becomes moot when you realize you can update multiple records in one sql statement, and that sql has a nice easy "UPPER" function.

Since it looks like you could use an example of the correct way to build this kind of query, I'll assume for a moment that #5 is somehow not an option and that you really do need to pull all this data down to the application and then update it back record by record (hint: you don't). Here is how you should build that code:

using (SqlConnection cn1 = new SqlConnection("connection string here")) //inbound data
using (SqlCommand cmd1 = new SqlCommand("SELECT uniqueid, State FROM myList", cn1)) 
using (SqlConnection cn2 = new SqlConnection("connection string here"))
using (SqlCommand cmd2 = new SqlCommand("UPDATE myList SET State= @State WHERE uniqueID= @ID", cn2))
{
    SqlParameter StateParam = cmd2.Parameters.Add("@State", SqlDbType.VarChar, 50);
    SqlParameter IDParam = cmd2.Parameters.Add("@ID", SqlDbType.Int);

    cn1.Open();
    cn2.Open();

    using (SqlDataReader rdr = cmd1.ExecuteReader())
    {
        while (rdr.Read())
        {
            StateParam  = rdr["State"].ToString().ToUpper();
            IDParam = rdr["uniqueID"];
            cmd2.ExecuteNonReader();
        }
    }
}

Note that this is just to demonstrate using blocks and parameterized queries. You should NOT use this code. Instead, take a good look at my point #5. This can and should all be done in a single sql UPDATE statement.