SQL Update one item at a time in order

Question

I have this c# code that does an SQL Update that could be doing multiple updates at a time. Now the table I am updating has a column called SortOrder, so while I am doing these multiple updates, I would like to do the updates in order of the sortOrder column...is this even possible?

Here is my code:

public void PostScheduledTasks(List<CellModel> cells)
        {
conn = new SqlConnection(connectionString);
                cmd = new SqlCommand(
                    @"UPDATE ScheduleTasks_Copy  
                      SET 
                          ActualStart=@actualStart,
                          ActualFinish=@actualFinish,
                          ActualEndDate=@actualEndDate,
                          UserDate1=@userDateOne,
                          IsCompleted=@isCompleted
                      WHERE ScheduleTaskID = @scheduleTaskID");
                cmd.Parameters.Add("@isCompleted", System.Data.SqlDbType.Bit);
                cmd.Parameters.Add("@userDateOne", System.Data.SqlDbType.DateTime);
                cmd.Parameters.Add("@actualStart", System.Data.SqlDbType.DateTime);
                cmd.Parameters.Add("@actualFinish", System.Data.SqlDbType.DateTime);
                cmd.Parameters.Add("@actualEndDate", System.Data.SqlDbType.DateTime);
                cmd.Parameters.Add("@scheduleTaskID", System.Data.SqlDbType.Int);


                cmd.Connection = conn;

                conn.Open();
                for (int i = 0; i < cells.Count; i++)
                {
                    cmd.Parameters["@isCompleted"].Value = cmd.Parameters["@percentComplete"].Value = (cells[i].selected == true) ? 1 : 0;
                    cmd.Parameters["@userDateOne"].Value = !string.IsNullOrEmpty(cells[i].scheduledDate) ? cells[i].scheduledDate : (object)DBNull.Value;
                    cmd.Parameters["@actualStart"].Value = !string.IsNullOrEmpty(cells[i].actualDate) ? cells[i].actualDate : (object)DBNull.Value;
                    cmd.Parameters["@actualFinish"].Value = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
                    cmd.Parameters["@actualEndDate"].Value = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
                    cmd.Parameters["@scheduleTaskID"].Value = cells[i].scheduleTaskID;
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
}

Answer 1

If "SortOrder" can be ascertained from the source object "cells", like other attributes:

Then the most efficient way would be to sort "cells" by SortOrder prior to iterating. The exact method to do so is beyond the scope of the question, since you have not told us what "cells" is, exactly (a list? an array? a custom object? a set?)

If SortOrder can only be ascertained by querying the database:

Then, not surprisingly, you'll need to query the database:

SELECT ScheduleTaskID, SortOrder FROM ScheduleTasks_Copy ORDER BY SortOrder

You iterate through that rowset, grabbing the ScheduleTaskID each time. For each ScheduleTaskID, iterate through "cells" until you find the matching task (cells[i].scheduleTaskID == TaskID), and THEN do the database update using the matching task from the source table.

Here is very rough code, I haven't written C# in a while:

using (connection)
{
    SqlCommand command = new SqlCommand("SELECT ScheduleTaskID, SortOrder FROM ScheduleTasks_Copy ORDER BY SortOrder;", connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            int taskid = reader.GetInt32(0);
            for (int i = 0; i < cells.Count; i++)
            {
             if (cells[i].scheduleTaskID == taskid) {
                 cmd.Parameters["@isCompleted"].Value = cmd.Parameters["@percentComplete"].Value = (cells[i].selected == true) ? 1 : 0;
                 cmd.Parameters["@userDateOne"].Value = !string.IsNullOrEmpty(cells[i].scheduledDate) ? cells[i].scheduledDate : (object)DBNull.Value;
                 cmd.Parameters["@actualStart"].Value = !string.IsNullOrEmpty(cells[i].actualDate) ? cells[i].actualDate : (object)DBNull.Value;
                 cmd.Parameters["@actualFinish"].Value = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
                 cmd.Parameters["@actualEndDate"].Value = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
                 cmd.Parameters["@scheduleTaskID"].Value = cells[i].scheduleTaskID;
                 cmd.ExecuteNonQuery();
                 }
            }
        }
    }
    reader.Close();
 }