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 r开发者_JS百科eally 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?
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)) +
")";
This is how I generally do this
string.Join(",", items.Select(i => $"'{i}'");
string dbCommand =
string.Format("select * from table where type in ({0})", string.Join(",", typeList.Select(p => "@" + p));
Split the list using as string.Join(",", listType.ToArray())
string types = string.Join(",", listType.ToArray());
Select * from table where type in (types)
A simple way could be to use :
"'" + string.Join("','", myListOfNames) + "'")
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.
精彩评论