C# &SQL Server - “一次性"删除多行的最佳方法使用存储过程 [英] C# & SQL Server - best way to delete multiple Rows in "one go" using a stored procedure

问题描述

我知道这里有许多相同的问题，我的问题是，如果我想删除大约 1K 行，而给定 RecordID<的 List/code>，我可以避免使用 DataTable，并将列表发送到语句中:

I know there's many of the same subjected question here in SO, my question is if I want to delete say around 1K rows rather few, given a List<int> of RecordID, I could avoid using a DataTable, and send the list translated into a statement:

string ParmRecordsToDelete_CsvWhereIN = "("
for(int CurIdx=0; CurIdx < RecIdsToDelete.Count; CurIdx++)
{
  ParmRecordsToDelete_CsvWhereIN += RecIdsToDelete[CurIdx] + ", ";
  //this method to create passed parameter 
  //logic to remove on last Coma on last Index..

  //or use stringJoin and somehow remove the last coma 
}
ParRecordsToDelete_CsvWhereIN +=")";

这将创建类似 "('1','2','3'......)"

This will create something Like "('1','2','3'......)"

然后创建一个SqlCommand来调用存储过程:

and then create a SqlCommand to call a stored procedure:

Delete * From @TblName WHERE @ColName IN @RecordsToDeleteCsvWhereIN

这是一种有效的方法吗?单个参数的长度有限制吗?我猜是 N/VARCHAR(MAX) 长度.

Is this an efficient approach? Is there a limit to the length of single parameter? I guess it's the N/VARCHAR(MAX) length.

我想如果它不是一个有点hackish的解决方案，它就不会受到长度的限制......

I guess if it's not a kinda hackish solution it would not be limited with length...

最好的快速解决方案是什么，或者我是否在正确的轨道上?

What would be the best fast solution, or am I on the right track as it is?

推荐答案

您可以使用表值参数来解决这个问题.应用层看起来像

You could use table valued parameters to hand this. The application layer would look something like

C#

var tvp = new DataTable();
tvp.Columns.Add("Id", typeof(int));

foreach(var id in RecIdsToDelete)
    tvp.Rows.Add(new {id});

var connection = new SqlConnection("your connection string");

var delete = new SqlCommand("your stored procedure name", connection)
{
  CommandType = CommandType.StoredProcedure
};

delete
  .Parameters
  .AddWithValue("@ids", tvp)
  .SqlDbType = SqlDbType.Structured;

delete.ExecuteNonQuery();

SQL

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'IDList')
BEGIN
    CREATE TYPE IDList AS TABLE(ID INTEGER)
END


CREATE PROCEDURE School.GroupStudentDelete
(                                         
        @IDS IDLIST READONLY      
)                                         
AS

SET NOCOUNT ON;

BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @Results TABLE(id INTEGER)

        DELETE 
        FROM TblName 
        WHERE Id IN (SELECT ID FROM @IDS)        

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();

        ROLLBACK TRANSACTION
        THROW; -- Rethrow exception
END CATCH
GO

与构建字符串相比，这种方法有许多优点

There are a number of advantages to this approach over building strings

• 您避免在应用层创建查询，从而创建关注点分离
• 您可以更轻松地测试执行计划和优化查询
• 您不太容易受到 SQL 注入攻击，因为您给定的方法将无法使用参数化查询来构建 IN 子句
• 代码更具可读性和说明性
• 您最终不会构建过长的字符串

性能

关于 TVP 在大型数据集上的性能有一些考虑.

There are some considerations about the performance of TVPs on large datasets.

因为 TVP 是变量，所以它们不编译统计数据.这意味着查询优化器有时可以伪造执行计划.如果发生这种情况，有几个选项:

Because TVPs are variables, they do not compile statistics. This means the query optimizer can fudge the execution plan sometimes. If this happens there a couple options :

• 在索引存在问题的任何 TVP 语句上设置 OPTION (RECOMPILE)
• 将 TVP 写入本地临时文件并在那里设置索引

这是一篇关于 TVP 的精彩文章，有一个很好的部分关于性能方面的考虑，以及什么时候会发生什么.

Here is a great article on TVP's with a good section on performance considerations, and what to expect when.

因此，如果您担心会达到字符串参数的限制，那么表值参数可能是您要走的路.但最终，如果不了解您正在使用的数据集的更多信息，就很难说.

So if you are worried about hitting limits on string parameters, the table valued parameters might be the way to go. But in the end, it is hard to say without knowing more about the data set you are working with.

这篇关于C# &SQL Server - “一次性"删除多行的最佳方法使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！