How to use List<int> as SQL parameter in C#

<>

This question already has an answer here:

Answers


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


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.


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

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.


Need Your Help

TCP Echo on different adapter

c# sockets

I'm trying to write a program for a computer with 2 network adapters (Private and Public) that echoes data received on Private to Public and vice versa. I only need it to listen on two specific por...

What's the control stucture “if true, then return” called?

if-statement

I remember a fellow programmer had a name for the kind if statement that returns out of the method it's in if it evaluates to true.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.