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

I'm trying to delete records from my SQL Server database table by passing multiple values as comma separated string using the below query and ado.net ExecuteNonQuery() method. Here I am passing the table name, column name and comma separated values.

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

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

The deletion process is successful if the full list of values in toDel has no issues. Assume that if there is a foreign key conflict with any of the value, the entire statement will be terminated and no records will be deleted.

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

Since the number of elements in toDel variable is big I cannot use sequential processing and also not able to use a stored procedure as per the requirement.

由于toDel变量中的元素数量很大，所以我不能使用顺序处理，也不能按照需求使用存储过程。

I need to delete based on the successful elements and return the erroneous ones from this method. Any help would be appreciated.

我需要根据成功的元素来删除，并从这个方法中返回错误的元素。如有任何帮助，我们将不胜感激。

Used the below code using Parameters

使用下面的代码使用参数

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
{

}

Got this error - Must declare the table variable "@delTblName".

获得此错误——必须声明表变量“@delTblName”。

3 个解决方案

#1

Regardless of using stored procedures or not, you have two contradicting requirements:

不管是否使用存储过程，您都有两个矛盾的要求:

1. You want to delete rows in batches, not one-by-one. For the sake of speed.
2. 您希望批量删除行，而不是逐个删除。为了速度。
3. You want the delete operation to complete partially ignoring possible constraint violations.
4. 您希望删除操作完成部分忽略可能的约束违反。

I don't know how to make DELETE statement work partially. One statement is the minimal atomic amount of work, so if one row out of 1000 violates the constraint, the whole statement will be rolled back.

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

This leads to the following generic idea. Before attempting to DELETE in one statement that affects many rows, make sure that the whole list of affected rows can be deleted. Explicitly check that there is nothing in the data that can prevent the rows that you are going to delete from being deleted. The actual check depends on your constraints. Identify those rows that can't be deleted and remove them from the main DELETE list.

这就引出了下面的通用概念。在尝试删除一个影响许多行的语句之前，请确保可以删除所有受影响行的列表。显式地检查数据中没有任何内容可以防止删除将要删除的行。实际的检查取决于您的约束。识别那些不能删除的行，并从主删除列表中删除它们。

Example

例子

Two tables - TestMaster and TestDetails with a one-to-many relationship.

两个表——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

Some sample data:

一些示例数据:

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');

Simple attempt to DELETE items (1, 2, 3, 4):

简单尝试删除项目(1、2、3、4):

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

This fails:

这个操作失败:

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.

We can delete only those Master rows that don't have corresponding Details. In this example it is only 3, 4.

我们只能删除那些没有相应细节的主行。在这个例子中只有3 4。

Find MasterID which can't be deleted first:

查找无法删除的MasterID:

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

Which returns:

返回:

MasterID
1
2

Edit your original list of IDs and remove conflicting IDs from it, then you can run the final DELETE:

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

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

Single query

单查询

Instead of running a separate SELECT, retrieving a list of conflicting IDs to the client over the network, adjusting the original list of IDs, running the final DELETE you can make a single query that does it all. For this simple example it can look like this:

与其运行单独的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)
    );

This query will delete only two rows with IDs 3 and 4.

这个查询将只删除id为3和4的两行。

#2

By Default if your sql statment fails then the transaction will be rolled back. And you cannot meet your need if you are doing through single statement where you need to get the erroneous one

默认情况下，如果sql语句失败，事务将回滚。如果你只做了一个错误的陈述，你就不能满足你的需要

May be you can follow the approach given below

也许你可以遵循下面给出的方法

    try
    {
        using (var sc = new SqlConnection(dbConnString))
        using (var cmd = sc.CreateCommand())
        {
            sc.Open();
            var nosConcat = "1,2,3,4,5";
            string failedIds = string.Empty;
            var ids = nosConcat.Split(',');
            string @delTblName = "sometable";
            string @delColumnName = "somecolumn";
            for(int i = 0 ; i < ids.Length ; i++)
            {
                try
                {
                    cmd.CommandText = "DELETE FROM " + @delTblName + " WHERE " + @delColumnName + " = @valConcat ";
                    cmd.Parameters.AddWithValue("@valConcat", ids[i]);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    if (ex.Errors[0].Number == 444) //Use the actual error number that you get on foreign key confilict
                        failedIds += "," + ids[i];
                }
            }
        }
    }
    catch
    {

    }

#3

If you needed to just delete, it would have been an easy answer - batch the delete statements and wrap them in a transaction. But since you want the keys that caused an issue, and you cannot use stored procedures, that makes the problem tricky.

如果只需要删除语句，这将是一个简单的答案——对delete语句进行批处理，并将它们封装到事务中。但是，由于您想要引起问题的键，并且不能使用存储过程，这就使问题变得棘手。

Here is a thought experiment that might get you closer to what you want. The idea is this:

这里有一个思想实验，可以让你更接近你想要的。我们的想法是这样的:

1. Process the delete in batches. I see no way around that without using stored procedures.
2. 分批处理删除。我认为如果不使用存储过程就无法解决这个问题。
3. In each batch:
   • Try to delete all the records (maximum "window" size)
   • 尝试删除所有记录(最大“窗口”大小)
   • If the delete fails, try to delete the records in a smaller "window".
   • 如果删除失败，请尝试在较小的“窗口”中删除记录。
   • Keep trying and decreasing the "window" until a problem record is found.
   • 继续尝试并减少“窗口”，直到找到问题记录。
   • On a single error add that value to the list of errors.
   • 在单个错误中，将该值添加到错误列表中。
   • Continue checking each record.
   • 继续检查每个记录。
   • if the next delete succeeds, confidently increase the "window" size.
   • 如果下一个删除成功，自信地增加“窗口”大小。
4. 在每批处理中:如果删除失败，尝试删除所有记录(最大“窗口”大小)，尝试删除较小的“窗口”中的记录。继续尝试并减少“窗口”，直到找到问题记录。在单个错误中，将该值添加到错误列表中。继续检查每个记录。如果下一个删除成功，自信地增加“窗口”大小。

The "window" size adjusts the number of records in the batch to process.

“窗口”大小调整批处理中的记录数量。

---

At the end you would have deleted what you can, and will have a list of values that failed to delete.

最后，您将删除所能删除的内容，并将拥有一个无法删除的值列表。

Unfortunately you cannot run a delete and get the list of values that gave problems (database transactions don't work like that) but the "binary search-like" method above will at least be faster than checking each value in the 15k list... only if there are a a small number of conflicts. Otherwise it won't be any faster.

不幸的是，您无法运行delete并获取导致问题的值列表(数据库事务不能这样工作)，但是上面的“类似于二进制搜索”的方法至少比检查15k列表中的每个值要快……只有当有少量的冲突时。否则就不会更快了。

---

Here is the code that details the steps:

以下是具体步骤的代码:

int batchSize = 1024; // define this elsewhere
// allValuesToDel is your list of values. I've just defined it as an empty list
var allValuesToDel = new List<int>();
var valueErrors = new List<int>();

for (int i = 0; i < allValuesToDel.Count; i += batchSize)
{
    using (var sc = new SqlConnection(dbConnString))
    {
        sc.Open();
        var valueBatch = allValuesToDel.Skip(i)
            .Take(batchSize)
            // annotating each value with a 'processed' flag allows us to track them
            .Select(k => new { Value = k, Processed = false }).ToList();

        // the starting window size
        int windowSize = valueBatch.Count;
        while (valueBatch.Count > 0 && valueBatch.Any(o => !o.Processed))
        {
            // get the unprocessed values within the window
            var valuesToDel = valueBatch.Where(k => !k.Processed).Take(windowSize).ToList();
            string nosConcat = string.Join(",", valuesToDel.Select(k => k.Value));
            try
            {
                using (var cmd = sc.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", tblName, delColumnName, nosConcat); 
                    cmd.ExecuteNonQuery();
                }

                // on success we can mark them as processed
                valuesToDel.ForEach(k => k.Processed = true);

                // Since delete worked, let's try on more records
                windowSize = windowSize * 2;
            }
            catch (SqlException ex)
            {
                if (windowSize == 1)
                {
                    // we found a value that failed - add that to the list of failures
                    valueErrors.Add(valuesToDel[0].Value);
                    valuesToDel.ForEach(k => k.Processed = true); // mark it as processed
                }

                // decrease the window size (until 1) to try and catch the problematic record
                windowSize = (windowSize / 2) + 1;
            }
        }
    }
}