c# - 通过传递逗号分隔值 SQL Server 和 C# 删除记录

Question

我正在尝试通过使用以下查询和 ado.net ExecuteNonQuery() 方法将多个值作为逗号分隔的字符串传递来从我的 SQL Server 数据库表中删除记录。在这里，我传递了表名、列名和逗号分隔值。

string delQuery = "DELETE FROM " + delTblName + 
                  " WHERE " + delColumnName + " IN (" + toDel+ ")";

如果 toDel 中的完整值列表没有问题，则删除过程成功。假设如果外键与任何值发生冲突，则整个语句将终止，并且不会删除任何记录。

由于toDel变量中的元素个数很大，无法使用顺序处理，也无法按要求使用存储过程。

我需要根据成功的元素删除并从该方法返回错误的元素。任何帮助，将不胜感激。

使用参数使用下面的代码

try
{
   using (var sc = new SqlConnection(dbConnString))
     using (var cmd = sc.CreateCommand())
     {
         sc.Open();
         cmd.CommandText = "DELETE FROM @delTblName WHERE @delColumnName IN ( @valConcat) ";                                       

         cmd.Parameters.AddWithValue("@delTblName", tblName);


         cmd.Parameters.AddWithValue("@delColumnName", delColumnName);


         cmd.Parameters.AddWithValue("@valConcat", nosConcat);


         cmd.CommandType = CommandType.Text;

         rowsAffected = rowsAffected + cmd.ExecuteNonQuery();
     }
}
catch
{

}

出现此错误 - 必须声明表变量“@delTblName”。

Answer 1

无论是否使用存储过程，您都有两个相互矛盾的要求:

1. 您想批量删除行，而不是逐行删除。为了速度。
2. 您希望删除操作在部分完成时忽略可能的约束违规。

我不知道如何使 DELETE 语句部分起作用。一个语句是最小的原子工作量，因此如果 1000 行中有一行违反约束，则整个语句将被回滚。

这导致了以下通用想法。在一个影响多行的语句中尝试 DELETE 之前，请确保可以删除受影响行的整个列表。明确检查数据中是否没有任何内容可以阻止您要删除的行被删除。实际检查取决于您的限制。识别那些无法删除的行，并将它们从主 DELETE 列表中删除。

示例

两个表 - TestMaster 和 TestDetails 具有一对多关系。

CREATE TABLE [dbo].[TestMaster](
    [ID] [int] NOT NULL,
    [MasterData] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TestMaster] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))

CREATE TABLE [dbo].[TestDetails](
    [ID] [int] NOT NULL,
    [MasterID] [int] NOT NULL,
    [DetailData] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TestDetails] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
GO

ALTER TABLE [dbo].[TestDetails]  WITH CHECK 
ADD  CONSTRAINT [FK_TestDetails_TestMaster] FOREIGN KEY([MasterID])
REFERENCES [dbo].[TestMaster] ([ID])
GO

ALTER TABLE [dbo].[TestDetails] CHECK CONSTRAINT [FK_TestDetails_TestMaster]
GO

一些示例数据:

INSERT INTO [dbo].[TestMaster] ([ID],[MasterData]) VALUES
(1, 'Master1'),
(2, 'Master2'),
(3, 'Master3'),
(4, 'Master4');

INSERT INTO [dbo].[TestDetails] ([ID],[MasterID],[DetailData]) VALUES
(10, 1, 'Detail10'),
(11, 1, 'Detail11'),
(20, 2, 'Detail20');

DELETE 项目 (1, 2, 3, 4) 的简单尝试:

DELETE FROM [dbo].[TestMaster]
WHERE ID IN (1, 2, 3, 4);

这失败了:

Msg 547, Level 16, State 0, Line 13
The DELETE statement conflicted with the REFERENCE constraint "FK_TestDetails_TestMaster". The conflict occurred in database "AdventureWorks2014", table "dbo.TestDetails", column 'MasterID'.
The statement has been terminated.

我们只能删除那些没有相应详细信息的主行。在这个例子中它只是 3, 4。

先找到不能删除的MasterID:

SELECT DISTINCT [dbo].[TestDetails].MasterID
FROM [dbo].[TestDetails]
WHERE [dbo].[TestDetails].MasterID IN (1, 2, 3, 4);

哪个返回:

MasterID
1
2

编辑您的原始 ID 列表并从中删除冲突的 ID，然后您可以运行最后的 DELETE:

DELETE FROM [dbo].[TestMaster]
WHERE ID IN (3, 4);

单一查询

无需运行单独的 SELECT，通过网络向客户端检索冲突 ID 列表，调整原始 ID 列表，运行最后的 DELETE，您可以进行一个查询即可完成所有操作。对于这个简单的例子，它看起来像这样:

DELETE FROM [dbo].[TestMaster]
WHERE 
    [dbo].[TestMaster].ID IN (1, 2, 3, 4)
    AND [dbo].[TestMaster].ID NOT IN
    (
        SELECT [dbo].[TestDetails].MasterID
        FROM [dbo].[TestDetails]
        WHERE [dbo].[TestDetails].MasterID IN (1, 2, 3, 4)
    );

此查询将仅删除 ID 为 3 和 4 的两行。