CODEWITHSUNDEEP
c#sqlvb.net
TaylorOtwell
TaylorOtwell

Reputation: 7337

Building SQL "where in" statement from list of strings in one line?

I have a List(Of String) which corresponds to "types" on our database table.

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

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

In the past, I've built the list of query parameters / host variables using a ForEach loop, but I would really like to figure out a way to build them in one line. Of course, I can join all of the strings, but adding the "@" and the incrementing digit is giving me headaches.

Anyone have any ideas on how to do this?

Upvotes: 8

Views: 25223

Answers (6)

Jimmy
Jimmy

Reputation: 587

This is how I generally do this

string.Join(",", items.Select(i => $"'{i}'");

Upvotes: 5

elhumidio
elhumidio

Reputation: 11

A simple way could be to use :

"'" + string.Join("','", myListOfNames) + "'")

Upvotes: 0

Maxali
Maxali

Reputation: 1962

Split the list using as string.Join(",", listType.ToArray())

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

    Select * from table where type in (types)

Upvotes: 0

StriplingWarrior
StriplingWarrior

Reputation: 156554

Won'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)) +
    ")";

Upvotes: 15

Akhil
Akhil

Reputation: 7600

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

Upvotes: 2

Richard Schneider
Richard Schneider

Reputation: 35477

SQL/ADO.NET does not support arrays. So you really have to build the SQL by hand.

SQL 2008 does support a Table parameter, but it seems a lot of overhead. See http://www.sommarskog.se/arrays-in-sql-2008.html for more details.

Upvotes: -1

Related Questions