开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜