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?

Marc Gravell · Answer 1 · 2011-05-12T13:56:39.207

9

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

edited May 12 '11 at 13:56

answered May 12 '11 at 13:35

Marc Gravell

1,026,079
266
2,566
2,900

You said - "You can't do that in regular TSQL, as the server treats @idList as a single value that happens to contain commas" - In facts I can use code `SqlCommand cmd = new SqlCommand("Delete from Table where ID in ("+stratIds+");", conn)` and everything works right. So I think I can replace string stratIds with param. – Kate May 12 '11 at 13:40
@Kate - I don't understand the comment – Marc Gravell May 12 '11 at 13:42
1

@Kate, SQL parameters don't work like that, your example using string concatenation will work, but parameters are not substituted out and treated like a string concatenation. – Ben Robinson May 12 '11 at 13:50
@Kate - your strategy for string concatenation is a SQL-Injection-attack waiting to happen. – EBarr May 12 '11 at 14:05
That is why I want to use SqlParametr – Kate May 12 '11 at 15:42
@Kate and by using a parameter, you remove the ability to use IN, unless you hack around it – Marc Gravell May 12 '11 at 16:08

Tim Schmelter · Answer 2 · 2011-05-12T13:45:41.897

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

be sure to mention the seminal: http://www.sommarskog.se/arrays-in-sql-2005.html a classic — Sam Saffron, May 14 '11 at 12:50

score 1 · Answer 3 · answered May 12 '11 at 14:32

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.

Delete with params in SqlCommand

3 Answers3

String Concatenation

Object Mapper

CSV Split

Table Valued Parameters

Linked