C# – How to use List as SQL parameter in C#


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

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

Best Solution

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