8

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?

TaylorOtwell
  • 7,177
  • 7
  • 32
  • 42
  • in .net 4 there is the String.Concat method but getting the index of the item in the iEnumerable is a pain and not a one liner. http://stackoverflow.com/questions/1290603/how-to-get-the-index-of-an-element-in-an-ienumerable I'd be interested in the solution for this. – Mike Miller Jun 07 '11 at 22:21

6 Answers6

15

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)) +
    ")";
StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • I've have had a similar issue in the past and building these up by hand in a pain. I really like the elegence of the linq method! – Brian Dishaw Jun 07 '11 at 22:35
  • Good ol' LINQ-to-String to the rescue. :) – Dan J Jun 07 '11 at 22:48
  • Well, I actually need the parameters to literally be @type1, @type2, etc. With the numbers and all. I'm just trying to create a parameter for each type and I'll fill each parameter with the actual values of the types before executing the query. – TaylorOtwell Jun 08 '11 at 01:22
5

This is how I generally do this

string.Join(",", items.Select(i => $"'{i}'");
Jimmy
  • 575
  • 5
  • 10
2
string dbCommand = 
    string.Format("select * from table where type in ({0})", string.Join(",", typeList.Select(p => "@" + p));
Akhil
  • 7,570
  • 1
  • 24
  • 23
0

A simple way could be to use :

"'" + string.Join("','", myListOfNames) + "'")
F Blanchet
  • 1,430
  • 3
  • 21
  • 32
elhumidio
  • 11
  • 2
0

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

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

    Select * from table where type in (types)
Maxali
  • 1,934
  • 2
  • 16
  • 25
-1

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.

Richard Schneider
  • 34,944
  • 9
  • 57
  • 73