How to use List as SQL parameter in C#

Question

I am trying to use List< int> as SQL parameter using this code:

var listID= new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

        using (var sqlConnection = new SqlConnection(_connectionstring))
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = sqlConnection;
                cmd.CommandText = "delete from MyTable where TableID in ( @tableID)";

                string param = String.Join(",", listID.ToArray());
                cmd.Parameters.Add("@tableID", param);
                sqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
            sqlConnection.Close();
        }

The problem is, that this code will generate:

exec sp_executesql N'delete from MyTable where TableID in ( @tableID)',N'@tableID nvarchar(17)',@tableID =N'1,2,3,4,5,6,7,8,9'

This will fail because:

Conversion failed when converting the nvarchar value '1,2,3,4,5,6,7,8,9' to data type int.

Any idea how to solve this? Thank you.

EDIT: I'm using MS SQL 2012

There is a logical difference between multiple parameters, separated by commas, and a *single* string parameter that happens to *contain* commas. T-SQL (like every other sane language) doesn't attempt to rip apart that single parameter. — Damien_The_Unbeliever, May 14 '13 at 14:54
are you sure that `IN` will work with *one* parameter? (i mean: one to rule them all, not one for one ...) — , May 14 '13 at 14:54
check this questions: stackoverflow.com/questions/182060/where-in-array-of-ids — VeNoMiS, May 14 '13 at 14:56

score 8 · Accepted Answer · answered May 14 '13 at 14:54

8

You should use a TVP, than you can keep the query exactly as specified. They were introduced in SQL 2008.

Table Valued Parameter, with example

answered May 14 '13 at 14:54

Matthew

10,244
5
49
104

2

given that the OP is talking about SQL2012, this is the way to go. +1 – Jodrell May 14 '13 at 15:06
+1 Yep. Agreed. When I wrote my answer, we didn't know which version of SQL he was talking about. – Tom Chantler May 14 '13 at 15:11
1

Good info! Link-only answers have a few problems: . Could you consider copy/paste the info into your answer? – JumpingJezza Apr 08 '15 at 02:00

score 2 · Answer 2 · edited May 23 '17 at 12:22

In SQL Server 2005 I wrote a CLR function that converts a comma delimited list into a set of nvarchars.

[SqlFunction(DataAccess = DataAccessKind.None,
                IsDeterministic = true,
                SystemDataAccess = SystemDataAccesskind.None,
                IsPrecise = true,
                FillRowMethodName = "SplitFillRow",
                TableDefinition = "s NVARCHAR(MAX)")]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
    {
        return new string[0];
    }

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

Which I think is an effective use .Net CLR and superior to the non CLR alternative using a CTE function.

In SQL Server 2008+, you can use Table Valued Parameters.

Sunny · Answer 3 · 2013-05-14T15:04:26.357

0

In SQL Server 2008 and above, you can use table value parameters. Since they are just comman seperated integers, I would also try to keep delete statement in stored procedure and pass xml string or pass as is to get parsed into into table variable. It is doable in many ways, as mentioned in other answers, table value parameters seems clean.

edited May 14 '13 at 15:04

answered May 14 '13 at 14:50

Sunny

4,765
5
37
72

Don't use XML for this. This is exactly what TVP's are for. The TVP can be sent as a parameter to the sproc, too – Matthew May 14 '13 at 15:00
You're right, tvp seems clean. But having only comma separated integers made me provide additional options. In < SQL Server 2005, I would prefer using XML rather than CLR function. – Sunny May 14 '13 at 15:06

score 0 · Answer 4 · answered May 14 '13 at 14:54

0

I would generally use some kind of ORM, but if you want to carry on with something similar to what you've got, you could always try this:

    var listID= new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
    using (var sqlConnection = new SqlConnection(_connectionstring))
    {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = sqlConnection;
            cmd.CommandText = "delete from MyTable where TableID in (" + String.Join(",",listID) + ")";
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
        }
        sqlConnection.Close();
    }

answered May 14 '13 at 14:54

Tom Chantler

14,753
4
48
53

2

Why was this downvoted? Seems reasonable given the problem. – Michael Todd May 14 '13 at 14:57
1

If you are doing dynamic SQL its suprisingly effective. – Jodrell May 14 '13 at 15:03
3

@MichaelTodd I'm guessing because even though it's safe in its current form, trivial modifications (`List`) make it dangerously unsafe. Personally, I'm not necessarily opposed to dynamic SQL, but if it needs to be used, a probably generally better alternative would be to build a `where TableID in (@p0, @p1, @p2, ...)` query and store the list values in the parameters. – May 14 '13 at 15:04
1

@hvd I'm aware of SQL injection, but I'm unclear how this particular use of dynamic SQL could be unsafe. What trivial modification would allow a list of ints to alter the SQL sufficiently to become a problem? (If you mean that, at some later time, the list is turned into strings, then YES, of course it would be a problem.) – Michael Todd May 14 '13 at 15:46
@MichaelTodd Right, exactly that: changing `List` to `List` in order to use it on a different table where the primary key is a string instead of an integer is, IMO, a trivial modification. I think we're mostly in agreement: SQL injection is not possible if you're only dealing with `int`s and you're not preceding the `int`s by anything that could combine with the sign of negative values. – May 14 '13 at 15:57
@hvd Just wanted to make sure I wasn't missing something. There's always a new hack or exploit; I'll never catch up without asking. – Michael Todd May 14 '13 at 16:27

How to use List as SQL parameter in C#

4 Answers4