SQL Updating one item at a time in order

Question

I have this C # code that does an SQL update that can do multiple updates at a time. Now the table that I am updating has a SortOrder column, so while I am doing these multiple updates, I would like to make updates in the sortOrder column order ... 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 set from the "cells" of the source object, like other attributes:

"" SortOrder . , , "" , (? ? ? ?)

SortOrder :

, , :

SELECT ScheduleTaskID, SortOrder FROM ScheduleTasks_Copy ORDER BY SortOrder

, ScheduleTaskID. ScheduleTaskID "" , (cells[i].scheduleTaskID == TaskID), THEN .

, # :

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();
 }

Answer 2

, , " "? " List<CellModel> cells /?".

, , cells.Count , , - , - , . , .

cells . , . cells.Count , SqlBulkCopy ( Will) . , Table-Valued Parameter .

, , , , , / temp, (MSDN .) , , cells scheduleTaskID, (, sortOrder, .)

:

create table #temp(scheduleTaskID int, isCompleted bit, userDateOne datetime, actualStart datetime, actualFinish datetime, actualEndDate datetime)

--populate #temp via SqlBulkCopy, etc

update st
set st.ActualStart = t.actualStart
  , st.ActualFinish = t.actualFinish
  , st.ActualEndDate = t.actualEndDate
  , st.UserDate1 = t.userDateOne
  , st.IsCompleted = t.isCompleted
from dbo.ScheduleTasks_Copy st
     inner join #temp t on st.ScheduleTaskID = t.scheduleTaskID

drop table #temp

, , , - , ...

Answer 3

SortOrder, SortOrder = X WHERE:

var dt = new DataTable();

dt : SELECT SortOrder FROM ScheduleTasks_Copy

var conn = new SqlConnection(connectionString);

foreach (DataRow dr in dt.Rows)
{
    cmd = new SqlCommand(
        @"UPDATE ScheduleTasks_Copy  
        SET 
        ActualStart=@actualStart,
        ActualFinish=@actualFinish,
        ActualEndDate=@actualEndDate,
        UserDate1=@userDateOne,
        IsCompleted=@isCompleted
        WHERE ScheduleTaskID = @scheduleTaskID
        AND SortOrder = @sortOrder");

    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.Parameters.Add("@sortOrder", dr["SortOrder"].ToString());
}

( , )

, , , , .

Answer 4

CellModel sortOrder? . :

cells = cells.OrderBy(o => o.sortOrder); // add this -> order your cells first
for (int i = 0; i < cells.Count; i++) { 
...
}

sortOrder, .

ScheduleTaskID, , sortOrder, :

 select ScheduleTaskID from ScheduleTasks_Copy order by SortOrder

, cells ScheduleTaskID, .

Answer 5

, , , . , , , .

var conn = new SqlConnection(connectionString);

conn.Open();
SqlCommand cmd = new SqlCommand("create table ##Tasks(id int, isCompleted bit, userDateOne datetime, actualStart datetime, actualFinish datetime, actualEndDate datetime)", conn);
cmd.ExecuteNonQuery();

DataTable localTempTable = new DataTable("Tasks");
localTempTable.Columns.Add("id", typeof(int));
localTempTable.Columns.Add("isCompleted", typeof(bool));
localTempTable.Columns.Add("userDateOne", typeof(DateTime));
localTempTable.Columns.Add("actualStart", typeof(DateTime));
localTempTable.Columns.Add("actualFinish", typeof(DateTime));
localTempTable.Columns.Add("actualEndDate", typeof(DateTime));

for (int i = 0; i < cells.Count; i++)
{
    var row = localTempTable.NewRow();
    row["id"] = cells[i].scheduleTaskID;
    row["isCompleted"] = (cells[i].selected == true);
    row["userDateOne"] = !string.IsNullOrEmpty(cells[i].scheduledDate) ? cells[i].scheduledDate : (object)DBNull.Value;
    row["actualStart"] = !string.IsNullOrEmpty(cells[i].actualDate) ? cells[i].actualDate : (object)DBNull.Value;
    row["actualFinish"] = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
    row["actualEndDate"] = !string.IsNullOrEmpty(cells[i].finishedDate) ? cells[i].finishedDate : (object)DBNull.Value;
}

localTempTable.AcceptChanges();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = "##Tasks";
    bulkCopy.WriteToServer(localTempTable);
}

SqlCommand cmd = new SqlCommand(@"
    declare orderedUpdate cursor 
    for 
    select tempTasks.* from ##tasks tempTasks
    inner join ScheduleTasks tasks on tempTasks.id =  tasks.ScheduleTaskID
    order by tasks.sortOrder;

declare @id int, @isCompleted bit, @actualStart datetime, @actualFinish datetime, @actualEndDate datetime, @userDateOne datetime;

open orderedUpdate;
fetch next from orderedUpdate INTO @id, @isCompleted, @userDateOne, @actualStart, @actualFinish, @actualEndDate 
while @@fetch_status = 0
begin 
    UPDATE ScheduleTasks_Copy  
        SET 
            ActualStart=@actualStart,
            ActualFinish=@actualFinish,
            ActualEndDate=@actualEndDate,
            UserDate1=@userDateOne,
            IsCompleted=@isCompleted
        WHERE ScheduleTaskID = @id;
end
close orderedUpdate
deallocate orderedUpdate", conn);

cmd.ExecuteNonQuery();

Answer 6

, ( , , sortOrder ?), , UPDATE . , ( , , ), UPDATE , ( ).

SqlBulkCopy , (TVP), Cells , UPDATE (, [tempdb], ). , SqlBulkCopy Cells `DataTable.

T-SQL # TVP. , - , , , , .

T-SQL

-- First: You need a User-Defined Table Type
CREATE TYPE dbo.ScheduleTasksImport AS TABLE
(
   ScheduleTaskID INT NOT NULL, -- optionally mark this field as PRIMARY KEY
   IsCompleted BIT NOT NULL,
   ActualStart DATETIME NULL,
   ActualFinish DATETIME NULL,
   ActualEndDate DATETIME NULL,
   UserDate1 DATETIME NULL
);
GO

GRANT EXECUTE ON TYPE::[dbo].[ScheduleTasksImport] TO [user_or_role];
GO

-- Second: Use the UDTT as an input param to an import proc.
--         Hence "Tabled-Valued Parameter" (TVP)
CREATE PROCEDURE dbo.ImportData (
   @ImportTable    dbo.ScheduleTasksImport READONLY
)
AS
SET NOCOUNT ON;

UPDATE stc
SET    stc.ActualStart = imp.ActualStart,
       stc.ActualFinish = imp.ActualFinish,
       stc.ActualEndDate = imp.ActualEndDate,
       stc.UserDate1 = imp.UserDate1,
       stc.IsCompleted = imp.IsCompleted
FROM ScheduleTasks_Copy stc
INNER JOIN @ImportTable imp
        ON imp.ScheduleTaskID = stc.ScheduleTaskID
GO

GRANT EXECUTE ON dbo.ImportData TO [user_or_role];

#

1: , IEnumerable<SqlDataRecord>

using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private static IEnumerable<SqlDataRecord> SendRows(List<CellModel> Cells)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("ScheduleTaskID", SqlDbType.Int),
      new SqlMetaData("IsCompleted", SqlDbType.Bit),
      new SqlMetaData("ActualStart", SqlDbType.DateTime),
      new SqlMetaData("ActualFinish", SqlDbType.DateTime),
      new SqlMetaData("ActualEndDate", SqlDbType.DateTime),
      new SqlMetaData("UserDate1", SqlDbType.DateTime)
   };

   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   // read a row, send a row
   for (int _Index = 0; _Index < Cells.Count; _Index++)
   {
      // Unlike BCP and BULK INSERT, you have the option here to create an
      // object, do manipulation(s) / validation(s) on the object, then pass
      // the object to the DB or discard via "continue" if invalid.
      _DataRecord.SetInt32(0, Cells[_Index].scheduleTaskID);
      _DataRecord.SetBoolean(1, Cells[_Index].selected); // IsCompleted
      _DataRecord.SetDatetime(2, Cells[_Index].actualDate); // ActualStart
      _DataRecord.SetDatetime(3, Cells[_Index].finishedDate); // ActualFinish
      _DataRecord.SetDatetime(4, Cells[_Index].finishedDate); // ActualEndDate
      _DataRecord.SetDatetime(5, Cells[_Index].scheduledDate); // UserDate1

      yield return _DataRecord;
   }
}

2: PostScheduledTasks , ImportData. , @ImportTable, .

public static void PostScheduledTasks(List<CellModel> Cells)
{
   SqlConnection _Connection = new SqlConnection(connectionString);
   SqlCommand _Command = new SqlCommand("ImportData", _Connection);
   _Command.CommandType = CommandType.StoredProcedure;

   SqlParameter _TVParam = new SqlParameter();
   _TVParam.ParameterName = "@ImportTable";
   _TVParam.SqlDbType = SqlDbType.Structured;
   _TVParam.Value = SendRows(Cells); // method return value is streamed data
   _Command.Parameters.Add(_TVParam);

   try
   {
      _Connection.Open();

      // Send the data and process the UPDATE
      _Command.ExecuteNonQuery();
   }
   finally
   {
      _Connection.Close();
   }

   return;
}

, # , . .cs, using .

Answer 7

, , : ( SqlBulkCopy), MERGE ORDER BY . ScheduleTasks_Copy temp ScheduleTasks .

MERGE ScheduleTasks AS T
USING (SELECT TOP 99999999 C.* 
       FROM ScheduleTasks_Copy C
       JOIN ScheduleTasks S
         ON C.ID = S.ID
       ORDER BY S.SortOrder ASC) AS S
ON S.ID = T.ID
WHEN MATCHED THEN 
  UPDATE SET T.ActualStart = S.ActualStart,
      T.ActualFinish = S.ActualFinish,
      T.ActualEndDate = S.ActualEndDate,
      T.UserDate1 = S.UserDate1,
      T.IsCompleted = S.IsCompleted,
      T.UpdatedOn =  dbo.GetDateValue();

GetDateValue UpdateOn. , , . , DESC.

http://sqlfiddle.com/#!6/dbc5f/16

, .