Geek asks and answers

Build the SQL expression "where in" from a list of strings on one line?

I have a List (Of String) that matches the "types" in our database table.

We are using the ADO.NET DB2 provider, and my final query should look something like this:

select * from table where type in (@type1, @type2, @type3, @type4)

In the past, I built a list of query parameters / host variables using a ForEach loop, but I would really like to find out how to create them in one line. Of course, I can join all the lines, but adding "@", and an increasing number gives me headaches.

Anyone have any ideas on how to do this?

+3
source share
4 answers

Wouldn't something like this work?

var inList = "(" + string.Join(", ", typeList.Select(t => "@" + t)) + ")";

Edit

Based on your comment, how about this?

var inList = "(" + 
    string.Join(", ", Enumerable.Range(1, argCount).Select(i +> "@type" + i)) +
    ")";
+7

, string.Join(",", listType.ToArray())

    string types = string.Join(",", listType.ToArray());

    Select * from table where type in (types)
+1

SQL / ADO.NET does not support arrays. So you really need to create SQL manually.

SQL 2008 supports the Table parameter, but there seems to be a lot of overhead. See http://www.sommarskog.se/arrays-in-sql-2008.html for more details .

0
source 
string dbCommand = 
    string.Format("select * from table where type in ({0})", string.Join(",", typeList.Select(p => "@" + p));
0
source

More articles:


All Articles