0

In TSQL I can write:

Select * from mytablename  M where M.field in (1, 5, 8, 56)

If I want to do the same thing in parameterized c#, what is the syntax?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
DeveloperM
  • 1,129
  • 7
  • 17
  • 30
  • 1
    Do you mean `IN (@p1,@p2,@p3,@p4)`? – Joachim Isaksson Aug 16 '13 at 14:02
  • Just my brain overflowed.. – Soner Gönül Aug 16 '13 at 14:02
  • It helps when downvotes are accompanied by a comment of what's lacking. – hatchet - done with SOverflow Aug 16 '13 at 14:02
  • You are speaking of ADO.NET? – xanatos Aug 16 '13 at 14:03
  • @Joachim: Yes, but I will have hundreds of parameters. Is this feasible? – DeveloperM Aug 16 '13 at 14:04
  • 1
    @DeveloperM Look at http://stackoverflow.com/a/5407095/613130 – xanatos Aug 16 '13 at 14:05
  • @Hatchet: That downvote thing: it's a mystery to me. I haven't been on this site in months and the question had a -6 almost instantly. – DeveloperM Aug 16 '13 at 14:05
  • 3
    @DeveloperM Normally, when you have hundreds of parameters in an `IN` query, it's a sign that you fetched something from the database to generate the query parameters (very seldom do users type in hundreds of parameters in an UI). Try using a `JOIN` or similar if the data is already in the database instead of fetching it and passing it back again. But yes, it definitely works for hundreds of parameters too. – Joachim Isaksson Aug 16 '13 at 14:06
  • 1
    @JoachimIsaksson There is a top limit to the number of parameters that can be passed. I don't remember it, but it isn't "infinite". At least on SQL Server 2008. – xanatos Aug 16 '13 at 14:07
  • @xanatos It's 2100 parameters, over hundreds into the thousands, but yes, may be a concern. – Joachim Isaksson Aug 16 '13 at 14:08
  • @JoachimIsaksson That is for Stored Procedures... For SQL commands it isn't clear. But lets say 2100. – xanatos Aug 16 '13 at 14:09
  • @Joachim: yes, the parameters values are fetched from the db but I can't use a join instead of the "IN" syntax because the parameters values will be dynamic. That is, I won't know until execution time what I'll need to retrieve. Thanks for responding. – DeveloperM Aug 16 '13 at 14:14

1 Answers1

1

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)

xanatos
  • 109,618
  • 12
  • 197
  • 280