C# syntax for tsql "IN" parameter [closed]

The SQL Server 2008 has a feature called Table-Valued Parameters. You create a "special type" in SQL Server and then you can pass a DataTable as a parameter, containing all the values you want.

You can use it this way:

On the DB do this: CREATE TYPE dbo.IntArray AS TABLE (Value INT NOT NULL)

Your IN query must be changed to something like: CustomerID IN (SELECT Value FROM @1)

// Your array of IDs
int[] ids = new[] { 1, 2, 3, 4, 5, 6, 7, 10 };

using (var connection = new SqlConnection("Initial Catalog=AdventureWorksLT2012;Integrated Security=True"))
{
    connection.Open();

    using (var command = new SqlCommand("SELECT CustomerID FROM SalesLT.Customer WHERE CustomerID IN (SELECT Value FROM @1)", connection))
    {
        // An untyped Datatable
        var dt = new DataTable();

        // With a single column
        dt.Columns.Add();

        // Copy your IDs in the DataTable
        foreach (var v in ids)
        {
            dt.Rows.Add(v);
        }

        // Create the Table-Valued Parameter
        var param = command.Parameters.AddWithValue("@1", dt);
        param.SqlDbType = SqlDbType.Structured;
        param.TypeName = "dbo.IntArray";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                int id = (int)reader[0];

                Console.WriteLine(id);
            }
        }
    }
}

Technically you could change your query even in something like

INNER JOIN @1 Par ON CustomerID = Par.Value

This has the advantage that you could create a multi-column DataTable and Table-Valued Parameter and do search on multiple conditions at the same time.

(note that my code is overlong because it's a working example based on the Microsoft's AdventureWorks db)