Concatenate variable number of strings

Question

This is my query:

"select cli.FANTASIA, dbsmp.VEICULO_PLACA, dbsmp.DTINICIOPREV, dbsmp.DTFIMPREV," +
                                                    " dbsmp.DTINICIOREAL, dbsmp.DTFIMREAL,dbsmp.CIDADE_DES,dbsmp.CIDADE_ORI, work.STATUS," +
                                                    " dbsmp.REF1 FROM dbsmp_work work inner join dbsmp "+ 
                                                    " on work.ID_SMP = dbsmp.ID_SMP inner join dbcliente cli "+
                                                    " on dbsmp.ID_CLIENTE = cli.ID_CLIENTE inner join dbSMP_MOTORISTA mot "+
                                                    " on dbsmp.ID_SMP = mot.ID_SMP where dbsmp.ID_CLIENTE = @IDCLIENTE "+
                                                    " and work.STATUS in('F') and work.tipo in ({0})";

In {0} point, I want to insert a list of strings separated by ,.

is there a way to pass this list using some method, or something like that, or i'll have to create another string manually, eg. looping in a list?

How about [using parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html)? — Soner Gönül, Aug 12 '13 at 14:35
[Parameterizing an SQL IN clause](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — Tim Schmelter, Aug 12 '13 at 14:41
Independet from your question you should use StringBuilder or string.Format instead of "+". — Sebi, Aug 12 '13 at 14:46
@Sebi - While that's generally true, in this example, there is no concatenation happening. The compiler will emit only a single string. — Chris Dunaway, Aug 12 '13 at 14:50
@ChrisDunaway: ...and it's also not _generally true_. Even [Jon Skeet prefers](http://stackoverflow.com/a/296985/284240) `+` for few, simple concatenations ;) — Tim Schmelter, Aug 12 '13 at 14:51

score 3 · Accepted Answer · answered Aug 12 '13 at 14:38

3

try this:

string.Format(sql, "'" + string.Join("', '", arrOfStrings) + "'")

answered Aug 12 '13 at 14:38

kpull1

1,623
15
19

1

I would have done, `string.Join(",", arrOfStrings.Select(x => "'" + x + "'"))` , but either way it works. – Ash Burlaczenko Aug 12 '13 at 14:40
@AshBurlaczenko if arrOfStrings is an empty array, the query turns into invalid. – kpull1 Aug 12 '13 at 14:43
True but it really shouldn't be getting to that point if it is empty. – Ash Burlaczenko Aug 12 '13 at 14:45
Thanks @kpull1 and Ash Burlaczenko, i've made a join using your answers and it works fine now. – guisantogui Aug 12 '13 at 19:12

Damith · Answer 2 · 2013-08-12T14:47:01.113

2

var resultQuery = string.Format(query, 
                  string.Join(",", stringList.Select(x => 
                                     string.Format("'{0}'", x))));

edited Aug 12 '13 at 14:47

answered Aug 12 '13 at 14:41

Damith

62,401
13
102
153

Sergey Kalinichenko · Answer 3 · 2013-08-12T15:03:55.073

Unfortunately, .NET DB libraries do not let you bind a single parameter to SQL's IN list.

If the strings that you bind to the IN list always come from inside your program and never from the user input, you can build the list directly, like this:

string query = String.Format(
    @"... AND work.tipo in (null, {0})"
,   string.Join(", ", tipiDiLavoro.Select(t => string.Format("'{0}'", t)))
);

This would produce a string that looks like this:

AND work.tipo in (null, 'a', 'b', 'c')

However, if the strings 'a', 'b', 'c' come from the user, you need to parameterize your query to avoid SQL injection attacks, like this:

string query = String.Format(
    @"... AND work.tipo in (null, {0})"
,   string.Join(", ", tipiDiLavoro.Select((t,i) => string.Format("@param{0}", i)))
);

for the query that looks like this:

AND work.tipo in (null, @param0, @param1, @param2)

and bind the IN list parameters individually in a separate loop:

int pos = 0;
foreach (var code in tipiDiLavoro) {
    cmd.SetParamValue("@param"+pos, code);
    pos++;
}

Note the use of NULL in the queries. They will never match anything, even in case work.tipo contains some NULLs. However, adding a NULL to the list lets you avoid syntax errors when the list of work types is empty: a query like this is valid, and it does not return anything:

... AND work.tipo IN (NULL) -- expanded from an empty list

This query, on the other hand, would trigger a syntax error:

... AND work.tipo IN ()

Concatenate variable number of strings

3 Answers3