Delete with params in SqlCommand

Question

I use ADO.NET to delete some data from DB like this:

    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();
        }
    }

That code executes without Exception but data wasn't deleted from DB. When I use the same algorithm to insert or update DB everything is OK. Does anybody know what is the problem?

Answer 1

You can't do that in regular TSQL, as the server treats @idList as a single value that happens to contain commas. However, if you use a List<int>, you can use dapper-dot-net, with

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

dapper figures out what you mean, and generates an appropriate parameterisation.

Another option is to send in a string and write a UDF to perform a "split" operation, then use that UDF in your query:

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

Answer 2

According to Marc's Split-UDF, this is one working implementation:

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  

And this is how you could call it:

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

Answer 3

I want to give this discussion a little more context. This seems to fall under the topic of "how do I get multiple rows of data to sql". In @Kate's case she is trying to DELETE-WHERE-IN, but useful strategies for this user case are very similar to strategies for UPDATE-FROM-WHERE-IN or INSERT INTO-SELECT FROM. The way I see it there are a few basic strategies.

String Concatenation

This is the oldest and most basic way. You do a simple "SELECT * FROM MyTable WHERE ID IN (" + someCSVString + ");"

• Super simple
• Easiest way to open yourself to a SQL Injection attack.
• Effort you put into cleansing the string would be better spent on one of the other solutions

Object Mapper

As @MarcGravell suggested you can use something like dapper-dot-net, just as Linq-to-sql or Entity Framework would work. Dapper lets you do connection.Execute("delete from MyTable where ID in @ids", new { ids=listOfIds }); Similarly Linq would let you do something like from t in MyTable where myIntArray.Contains( t.ID )

• Object mappers are great.
• However, if your project is straight ADO this is a pretty serious change to accomplish a simple task.

CSV Split

In this strategy you pass a CSV string to SQL, whether ad-hoc or as a stored procedure parameter. The string is processed by a table valued UDF that returns the values as a single column table.

• This has been a winning strategy since SQL-2000
• @TimSchmelter gave a great example of a csv split function.
• If you google this there are hundreds of articles examining every aspect from the basics to performance analysis across various string lengths.

Table Valued Parameters

In SQL 2008 custom "table types" can be defined. Once the table type is defined it can be constructed in ADO and passed down as a parameter.

• The benefit here is it works for more scenarios than just an integer list -- it can support multiple columns
• strongly typed
• pull string processing back up to a layer/language that is quite good at it.
• This is a fairly large topic, but Table-Valued Parameters in SQL Server 2008 (ADO.NET) is a good starting point.