sql server - Best practice for dynamically building variable length IN SQL clause in C# -

sql server - Best practice for dynamically building variable length IN SQL clause in C# -

recently found myself in c#, doing sql. not area of expertise. wrote code looks pretty ugly, , failing find improve solution anyplace. many of answers here on pose sql injection risks, , doing same thing im doing in 1 way or another. situation is, have form user provides list of store id's in form. when click button, csv export generated using stores provide exclusion criteria query. way accomplished setting sql string constant, using string builder dynamically append @x in in clause. code looks bad heres quick snippet improve explain. example, sql query this

private readonly string _select_unexpected_totes = "select * table table.store in ";

i next (stores array of strings, sb string builder):

//get plenty room stores var sqlparams = new sqlparameter[stores.length]; conn.open(); //append our query sb.append(_select_unexported_totes); //open in list sb.append("("); //build in list int = 0; foreach (string s in stores) { sb.append("@"); sb.append(i); sb.append(","); i++; } //trim trailing , sb.length -= 1; sb.append(")"); //make actual parameters = 0; foreach (string s in stores) { sqlparams[i] = new sqlparameter("@" + i, sqldbtype.varchar); sqlparams[i].value = s; i++; }

later on in code utilize these sql params in sqlstatement object. .net provide improve way accomplish this? dont know lot .nets sql objects, , know solution bad simple string replace... advice welcome.

i don't believe there easier way of doing through ado.net. other users had mentioned entity framework create considerably cleaner , not hard setup, on little app. turn above code into:

var info = context.table.where(t => stores.contains(t.store)).tolist();

c# sql-server in-clause sqlparameters