Блог

Я использую ADO.NET чтобы удалить некоторые данные из базы данных следующим образом:

     using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        try
        {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("Delete from Table where ID in (@idList);", conn))
            {
                cmd.Parameters.Add("@idList", System.Data.SqlDbType.VarChar, 100);
                cmd.Parameters["@idList"].Value = stratIds;

                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            //_logger.LogMessage(eLogLevel.ERROR, DateTime.Now, e.ToString());
        }
        finally
        {
            conn.Close();
        }
    }
  

Этот код выполняется без исключений, но данные не были удалены из базы данных.
Когда я использую тот же алгоритм для вставки или обновления базы данных, все в порядке.
Кто-нибудь знает, в чем проблема?

Вы не можете сделать это в обычном TSQL, поскольку сервер обрабатывает @idList как единственное значение, которое содержит запятые. Однако, если вы используете List<int> , вы можете использовать dapper-dot-net, с

 connection.Execute("delete from Table where ID in @ids", new { ids=listOfIds });
  

dapper выясняет, что вы имеете в виду, и генерирует соответствующую параметризацию.

Другой вариант — отправить строку и написать UDF для выполнения операции «разделения», а затем использовать этот UDF в своем запросе:

 delete from Table where ID in (select Item from dbo.Split(@ids))
  

Согласно Split-UDF от Marc, это одна из рабочих реализаций:

 CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  
  

И вот как вы могли бы это назвать:

 DELETE FROM Table WHERE (ID IN (SELECT Item FROM dbo.Split(@idList, ',')));
  

Я хочу придать этому обсуждению немного больше контекста. Похоже, это подпадает под тему «как мне получить несколько строк данных в sql«. В случае @Kate она пытается УДАЛИТЬ-WHERE-IN, но полезные стратегии для этого пользовательского случая очень похожи на стратегии для UPDATE-FROM-WHERE-IN или INSERT-INTO-SELECT FROM. На мой взгляд, существует несколько основных стратегий.

Конкатенация строк

Это самый старый и простой способ. Вы делаете простой «SELECT * FROM MyTable, ГДЕ ИДЕНТИФИКАТОР В (» someCSVString «);»

• Супер просто
• Самый простой способ открыть себя для атаки с использованием SQL-инъекций.
• Усилия, которые вы потратили на очистку строки, было бы лучше потратить на одно из других решений

Средство сопоставления объектов

Как предложил @MarcGravell, вы можете использовать что-то вроде dapper-dot-net, точно так же, как работали бы Linq-to-sql или Entity Framework. Dapper позволяет вам делать connection.Execute("delete from MyTable where ID in @ids", new { ids=listOfIds }); аналогично Linq позволил бы вам сделать что-то вроде from t in MyTable where myIntArray.Contains( t.ID )

• Средства отображения объектов великолепны.
• Однако, если ваш проект является прямым ADO, это довольно серьезное изменение для выполнения простой задачи.

Разделение CSV

В этой стратегии вы передаете строку CSV в SQL, будь то ad-hoc или в качестве параметра хранимой процедуры. Строка обрабатывается табличным значением UDF, которое возвращает значения в виде таблицы с одним столбцом.

• Это была выигрышная стратегия со времен SQL-2000
• @TimSchmelter привел отличный пример функции разделения csv.
• Если вы загуглите это, то найдете сотни статей, в которых рассматриваются все аспекты, от основ до анализа производительности при различных длинах строк.

Параметры, имеющие табличное значение

В SQL 2008 могут быть определены пользовательские «типы таблиц». Как только тип таблицы определен, его можно создать в ADO и передать в качестве параметра.

• Преимущество здесь в том, что это работает для большего количества сценариев, чем просто список целых чисел — он может поддерживать несколько столбцов
• строго типизированный
• перенесите обработку строк обратно на уровень / язык, который в этом довольно хорош.
• Это довольно большая тема, но табличные параметры в SQL Server 2008 (ADO.NET ) является хорошей отправной точкой.