SQL逐个更新项目的顺序

Question

我有一段C#代码，可以进行多个SQL更新。现在我正在更新的表格有一个名为SortOrder的列，所以在进行多个更新时，我希望按照sortOrder列的顺序进行更新...这是否可能？

以下是我的代码：

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

- user979331

---

这意味着你必须首先按sortOrder对List<CellModel> cells进行排序... - SmartDev

SortOrder是否可以从源数据（即“cells”数组/对象）中读取，还是仅存在于数据库中？我之所以问这个问题，是因为除了“cells[i]”之外，其他所有数据点都可以在其中找到。 - ExactaBox

这个奇怪而通常不必要的条件的目的是什么？为什么需要逐个进行更新？然后，为什么要按照这个顺序？无论你想做什么，在SQL中可能有更好的方式来实现，而不是使用这种非集合式的条件。 - RBarryYoung

跟进 @RBarryYoung 的评论......在 SQL Server 中更新或插入行的顺序对随后的 SELECT 查询没有任何影响。输入的顺序不会影响输出的顺序。期望它可能会影响，或通常会影响是危险的。 - ExactaBox

我必须赞同@RBarryYoung的观点。即使此处给出的最佳答案，也很容易被基于集合的解决方案超越，在该方案中，您只需将新数据推入临时表并在单个操作中更新目标表。除非您有一些好的（并且奇特的）原因要逐个进行（##），否则采用基于集合的解决方案确实是正确的方法。（##：触发器不是一个好的原因，因为它们也受益于基于集合而不是逐行的方法） - deroby

Answer 1

如果可以从源对象“cells”中确定“SortOrder”，就像其他属性一样：

那么最有效的方法是在迭代之前按SortOrder对“cells”进行排序。具体的方法超出了问题的范围，因为您并没有告诉我们“cells”到底是什么（一个列表？一个数组？一个自定义对象？一个集合？）

如果只能通过查询数据库来确定SortOrder：

那么，毫不奇怪地，您将需要查询数据库：

SELECT ScheduleTaskID, SortOrder FROM ScheduleTasks_Copy ORDER BY SortOrder

你需要遍历该行集，每次获取 ScheduleTaskID。对于每个 ScheduleTaskID，需要遍历“cells”，直到找到匹配的任务（cells[i].scheduleTaskID == TaskID），然后使用源表中的匹配任务进行数据库更新。

以下是非常粗糙的代码，我已经很久没有写 C# 了：

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

- ExactaBox

Answer 2

退一步不谈你实际关心的并发运行更新的问题，假设你的问题不是“我能同时运行一堆单个更新吗？”，而是“如何尽可能快/高效地更新我的List<CellModel> cells中的每个项目？”。

显然，当cells.Count变大时，顺序运行单个更新是行不通的，很可能是因为执行更新的成本被在网络上进行多次往返所产生的开销所淹没。因此，同时运行一堆单个更新可能也不是最有效的解决方案。

相反，让我们一次性移动整个cells列表。这意味着服务器拥有处理我们请求所需的所有内容。如果cells.Count很大，则 SqlBulkCopy（如Will建议的）将难以超越。或者，对于较小的数据集，表值参数也可以正常工作。

一旦数据可供服务器使用，您需要做的就是针对临时表/变量执行单个更新连接，然后完成操作（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

更新操作的连接语法并不像它应该被广泛知晓那样出名。一旦你习惯了它的一些小困难，它就变得非常有用...

- chrisb

Answer 3

CellModel是否有sortOrder属性？如果没有，应该加上这一行：

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

如果您没有“sortOrder”属性，则应找到另一种方法来对此集合进行排序。

另一种解决方案是通过使用“sortOrder”对您期望更新的“ScheduleTaskID”进行排序：

 select ScheduleTaskID from ScheduleTasks_Copy order by SortOrder

那么你应该浏览所有这些条目，如果你在 cells 中找到一个具有相同 ScheduleTaskID 的项，则应进行更新。

- SmartDev

Answer 4

你需要首先获取SortOrder列的值，然后遍历这些值，在WHERE子句中使用SortOrder = X进行更新。

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

我没有测试这段代码的语法，但它能传达一般思路。

虽然我猜这取决于你想要做这个的原因，但在我看来，需要这样做的原因似乎很有限。

- Sam Hood

Answer 5

我认为你可以将结果上传到一个临时表中，然后在服务器端使用游标滚动遍历并按指定顺序更新所有行。如果能够使用SQL用户定义类型和存储过程，这可能会更容易些。

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

- Will

Answer 6

尽管目前还不清楚为什么有必要按特定顺序更新记录集（是否存在触发器导致某些下游效应受益于按sortOrder字段的顺序处理？），但应指出，使用单个UPDATE语句执行所有更新将使排序无关紧要。已经有一些人指出采用基于集合的方法会更快（当然确实如此），但在单个UPDATE中完成意味着在事务上没有顺序（在实际层面上）。

通过SqlBulkCopy进行基于集合的操作已经被提到过了，但我更喜欢使用表值参数（TVPs），因为它们允许直接将Cells集合流式传输到将执行UPDATE的存储过程中（技术上是通过[tempdb]，但足够接近）。相反，使用SqlBulkCopy意味着需要将现有的Cells集合复制成一个数据表。

以下是将现有进程转换为使用TVP的T-SQL和C#代码。需要注意的是，如果仍然有某些原因按特定顺序执行此操作，则唯一需要更改的是如何处理存储过程中的表变量。

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];

C# 代码

第一部分：定义一个方法，该方法将接收集合并将其作为 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;
   }
}

第二部分：使用以下代码替换您当前的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;
}

请注意，这两个 C# 代码清单实际上应该放在同一个文件中，但为了可读性而分开。如果将它们放入不同的 .cs 文件中，则第二个清单需要复制 using 语句。

- Solomon Rutzky

Answer 7

忽略为什么你想这样做的显而易见的问题，你可以尝试这个技巧： 将数据插入到临时表中（使用SqlBulkCopy），然后在源中使用带有ORDER BY子句的MERGE。在此示例中，我使用ScheduleTasks_Copy作为“临时”表，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函数和UpdatedOn列。我只是包含它们，以便您可以看到更新的顺序。如果您想按相反的顺序更新记录，只需将排序顺序更改为DESC即可。

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

我个人建议避免这样的事情，并重新设计解决方案，以完全避免这个问题。

- ilitirit