SQL按顺序一次更新一项
本文关键字：一项 更新 一次 顺序 SQL | 更新日期: 2023-09-27 18:27:37
我有一个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();
}
如果可以从源对象"cells"中确定"SortOrder"，就像其他属性一样：
然后，最有效的方法是在迭代之前按SortOrder对"单元格"进行排序。这样做的确切方法超出了问题的范围，因为你没有告诉我们什么是"单元格"，确切地说（列表？数组？自定义对象？集合？）
如果SortOrder只能通过查询数据库来确定：
然后，毫不奇怪，您将需要查询数据库：
SELECT ScheduleTaskID, SortOrder FROM ScheduleTasks_Copy ORDER BY SortOrder
您遍历该行集，每次都获取ScheduleTaskID。对于每个ScheduleTaskID，遍历"单元格"，直到找到匹配的任务（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();
}
退一步，忽略您关于同时运行更新的实际问题，假设您的问题不是"我能同时运行一堆单个更新吗？"，而是"我如何尽可能快速/高效地更新List<CellModel> cells中的每个项？"。
显然，当cells.Count变大时，按顺序运行单个更新是不起作用的，这可能是因为执行更新的成本与在网络上执行多个往返所产生的开销相比相形见绌。因此，同时运行一堆单个更新可能也不是最有效的解决方案。
相反，让我们一次性移动整个cells列表。这意味着服务器拥有处理我们请求所需的一切。如果cells.Count很大，SqlBulkCopy（正如Will所建议的）将很难被击败。或者，表值参数也可以适用于较小的集合。
一旦数据可供服务器使用，您所需要做的就是对临时表/变量执行一次更新连接，就完成了（MSDN有一些很好的例子说明了这一技术。）但是，请注意，如果cells包含重复的scheduleTaskID，您可能还需要执行一点额外的预处理（确保最高排序顺序获胜，等等）
示例：
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
更新的联接语法并不像它应该的那样广为人知——一旦你习惯了这种轻微的尴尬，它就会变得非常有用。。。
您必须首先获取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());
}
（我还没有测试过这段代码的语法，但它贯穿了整个想法）
虽然我想这取决于你想这样做的原因，但在我看来，需要这样做的理由有限。
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，那么你应该进行更新。
我相信你可以做的是将结果上传到临时表中，然后使用光标服务器端滚动并按指定顺序更新所有行。如果您能够使用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();
虽然仍不清楚为什么希望以任何特定的顺序更新记录集（是否有触发器会导致某些下游效应，而这些效应将从按sortOrder字段的顺序处理中受益？），但应该指出的是，在单个UPDATE语句中执行所有更新会使顺序变得无关紧要。其他一些人已经指出，采用基于集合的方法会更快（当然也会更快），但在一个UPDATE中完成意味着事务上没有顺序（在实际层面上）。
已经提到过通过SqlBulkCopy创建此集合，但我更喜欢使用表值参数（TVP），因为它们允许将Cells集合直接流式传输到将执行UPDATE的存储过程（从技术上讲，通过[tempdb]，但足够接近）。相反，使用SqlBulkCopy意味着需要以`DataTable的形式复制现有的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#代码
第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;
}
请注意，这两个C#清单确实在同一个文件中，但为了可读性，在这里被分开了。如果将它们放入单独的.cs文件中，则需要将using语句复制到第二个列表中。
忽略关于为什么要这样做的明显问题，您可以尝试此破解：将数据插入临时表（使用SqlBulkCopy），然后在源中使用带有ORDER BY子句的MERGE。在这个例子中，我使用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函数和UpdatedOn列。我只是把它们包括在内，这样你就可以看到更新的执行顺序。如果您想以相反的顺序更新记录，只需将排序顺序更改为DESC.即可
http://sqlfiddle.com/#！6/dbc5f/16
就我个人而言，我建议避免这样的事情，重新设计你的解决方案，这样就可以完全避免这个问题。