C#和SQL Server – 使用存储过程“一次性”删除多行的最佳方法

发布时间：2020-12-15 19:26:32 所属栏目：百科来源：网络整理

导读：我知道在SO中有很多相同的问题,我的问题是如果我想删除说1K行而不是少数,给定List int对于RecordID,我可以避免使用DataTable,并将列表翻译成语句： string ParmRecordsToDelete_CsvWhereIN = "("for(int CurIdx=0; CurIdx RecIdsToDelete.Count; CurIdx++){

我知道在SO中有很多相同的问题,我的问题是如果我想删除说1K行而不是少数,给定List< int>对于RecordID,我可以避免使用DataTable,并将列表翻译成语句：

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’……)”

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

Delete * From @TblName WHERE @ColName IN @RecordsToDeleteCsvWhereIN

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

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

什么是最好的快速解决方案,还是我在正确的轨道上？

解决方法

您可以使用表值参数来处理此问题.应用程序层看起来像

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

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

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

性能

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

因为TVP是变量,所以它们不编译统计数据.这意味着查询优化器有时可以捏造执行计划.如果发生这种情况,有两种选择：

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

Here is a great article on TVP’s在性能考虑方面有很好的部分,以及什么时候可以期待.

因此,如果您担心对字符串参数进行限制,那么表值参数可能是最佳选择.但最终,如果不了解您正在使用的数据集,很难说.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!