在一个事务中批量执行 INSERT、UPDATE 和 DELETE [已关闭]-解网

问：

已关闭。这个问题需要细节或澄清。它目前不接受答案。

想改进这个问题吗？通过编辑这篇文章添加详细信息并澄清问题。

21天前关闭。

改进此问题

我正在尝试在 C# .NET 中对数据库进行批量“同步”（插入、更新和删除）。我正在使用 Dapper，因为由于其他原因，实体框架不适合该项目。有几个要求使我的问题更加困难：

同样，解决方案不能使用实体框架。
整个“同步”必须是一笔交易。
sql 必须动态生成，因为需要同步的 DTO 是数据库中表的一对一副本。IE：表与具有某些属性的 .NET DTO 匹配，这些属性也作为列存在于数据库中。CourseCourse
该解决方案必须（显然）能够防止 SQL 注入攻击。

解决方案现在的工作原理：

另一个类调用一种方法来“同步”单个客户的所有 DTO。repository.sync()
该客户在数据库中有自己的架构。架构的名称在同步调用时提供给存储库。
同步调用具有 param entitiesToSync 类型，其中Dictionary<Type, Dictionary<Synchronizable, TransactionType>>
- 可同步是要同步的 DTO
- TransactionType 是具有值的枚举{Added, Updated, Deleted}
- Type 是要同步的 DTO 的类型，它也等同于该 DTO 的表的名称。
调用 sync 方法后，entitiesToSync 将根据 TransactionType（添加、更新、删除）拆分为三个新字典。

我正在挣扎的：

对于添加的实体，应生成 INSERT sql 语句。
对于每个更新的实体，应生成单独的 UPDATE sql 语句。
对于已删除的实体，应生成 DELETE sql 语句。
所有这些语句都必须以安全的方式在一个事务中执行，以防止 SQL 注入。

我觉得这很困难的原因是，为了安全地生成 SQL 语句，需要使用参数。使用反射，我可以看到 DTO 具有的属性并生成语句。问题在于，实体的数量可能非常大，并且对每个项目使用唯一的参数可能会导致巨大的 DynamicParameter 对象。我相信SQL Server支持的参数数量是有限制的。解决方案是分批执行语句，但这是不可能的，因为同步应该是一个事务。

此外，为了防止解决方案使用大量资源，我希望生成尽可能少的语句和参数。我想我可以使用临时表来做到这一点，但我不确定如何做，因为我还需要动态生成这些临时表并在其中插入内容，这可能与直接插入实际表完全相同。

顶级域名;如何执行一个事务来批量插入、更新和删除数千条记录，以及如何从 .NET 类型的 PropertyInfo 生成执行此操作的代码？

C# .NET SQL-SERVER dapper

这个问题尚不清楚，因为您可以使用 Dapper 编写任何您想要的 UPDATE 或 DELETE 语句，并且具有特殊含义，仅加载文件，使用最少的日志记录。没有或，操作已完全记录。您可以轻松地使用 Dapper 来执行BULK INSERTBULK UPDATEBULK DELETEDELETE MyTable WHERE Category=@category

1赞 AlwaysLearning 11/1/2023

也许你对自己施加了人为的限制——一笔交易可以跨越任意数量的报表。

0赞 Zenith 11/1/2023

问题不在于我无法使用 Dapper 编写 UPDATE 或 DELETE 语句，而在于在单个事务中可能需要同时插入、更新和删除数千条记录。

0赞 Zenith 11/1/2023

@AlwaysLearning我知道。目的是在一个事务中使用多个语句。问题在于，这笔交易中可能有太多参数，以至于 2100 左右的限制（我认为）可能太低了。

1赞 AlwaysLearning 11/1/2023

参数不适用于事务，它们适用于语句。是的，单个语句的参数限制为 2100 个，但您可以在单个事务中使用任意数量的语句。

答：

1赞 siggemannen 11/1/2023 #1

我处理通用实体同步的方式是这样的：

为您的表创建一个镜像暂存表，即 Course => CourseStaging。该表可以具有与 target 相同的列，但可以为 NULL，以及一个标志列，该列指示如何处理实体 INSERT、UPDATE、DELETE
同步实体时，请确定它们属于哪个临时表，并使用填充临时表的 SQLBulkCopy。您仍然需要能够将属性映射到字段和数据类型，但我猜此代码已经可供您使用。您还可以使用 Dapper Ii guess 来填充暂存表。这要简单得多，因为这里没有 UPDATE / DELETE，只有简单的 INSERT
最后，调用一些 Procedure，该过程会生成一个 MERGE 语句，如下所示：

MERGE {TARGET} t
USING {SOURCE_STAGE} s
 ON s.{ID} = t.{ID}
WHEN MATCHED AND s.flag = 'insert' THEN INSERT (
 {COLUMNS}) VALUES({s.columns})
WHEN MATCHED AND s.flag = 'update' THEN UPDATE
SET {COLUMN_1} = s.{COLUMN_1}
...
WHEN MATCHED AND s.flag = 'delete' THEN DELETE

你需要动态地生成这些东西，但不应该太难，你可以随时在 .sys.columns

合并可以在显式事务中运行，最终您会得到一个完全更新的目标表。

这是完美的。我想我会使用你建议的答案的编辑版本;我将动态编写 SP，以便可以传递 dto 类型。正如您所说，列名可通过 sys.columns 获得，因此根据参数，我可以查找正确的列，并动态执行同步，从而只生成一个 SP。这样，我就不必为每种类型的 Dto （IE SyncCourse）生成一个 SP，而只需构建一个 SP SyncEntity（表 t）

0赞 Zenith 11/2/2023

如何一次插入实体和标志，而不是逐行插入？我已经阅读了有关SqlBulkCopy的信息，但不明白如何在此处实现它

0赞 siggemannen 11/2/2023

也许在这里阅读一些东西：stackoverflow.com/questions/18841000/......

上一个：在 EF Core 中使用计算的 IsDeleted 列进行软删除，无需手动使用 SQL 语句

下一个：批量插入性能问题

在一个事务中批量执行 INSERT、UPDATE 和 DELETE [已关闭]

Bulk INSERT, UPDATE and DELETE in one transaction [closed]

评论

评论