c# - Pass Array Parameter in SqlCommand -

c# - Pass Array Parameter in SqlCommand -

i trying pass array parameter sql commnd in c# below, not work. meet before?

string sqlcommand = "select * tablea age in (@age)"; sqlconnection sqlcon = new sqlconnection(connectstring); sqlcommand sqlcomm = new sqlcommand(); sqlcomm.connection = sqlcon; sqlcomm.commandtype = system.data.commandtype.text; sqlcomm.commandtext = sqlcommand; sqlcomm.commandtimeout = 300; sqlcomm.parameters.add("@age", sqldbtype.nvarchar); stringbuilder sb = new stringbuilder(); foreach (listitem item in ddlage.items) { if (item.selected) { sb.append(item.text + ","); } } sqlcomm.parameters["@age"].value = sb.tostring().trimend(',');

you need add together values in array 1 @ time.

var parameters = new string[items.length]; var cmd = new sqlcommand(); (int = 0; < items.length; i++) { parameters[i] = string.format("@age{0}", i); cmd.parameters.addwithvalue(parameters[i], items[i]); } cmd.commandtext = string.format("select * tablea age in ({0})", string.join(", ", parameters)); cmd.connection = new sqlconnection(connstr);

update: here extended , reusable solution uses adam's reply along suggested edit. improved bit , made extension method create easier call.

public static class sqlcommandext { /// <summary> /// add together array of parameters sqlcommand. used in statement. /// utilize returned value in part of sql call. (i.e. select * table field in ({paramnameroot})) /// </summary> /// <param name="cmd">the sqlcommand object add together parameters to.</param> /// <param name="values">the array of strings need added parameters.</param> /// <param name="paramnameroot">what parameter should named followed unique value each value. value surrounded {} in commandtext replaced.</param> /// <param name="start">the origin number append end of paramnameroot each value.</param> /// <param name="separator">the string separates parameter names in sql command.</param> public static sqlparameter[] addarrayparameters<t>(this sqlcommand cmd, ienumerable<t> values, string paramnameroot, int start = 1, string separator = ", ") { /* array cannot added parameter sqlcommand need loop through things , add together manually. * each item in array end beingness it's own sqlparameter homecoming value must used part of * in statement in commandtext. */ var parameters = new list<sqlparameter>(); var parameternames = new list<string>(); var paramnbr = start; foreach(var value in values) { var paramname = string.format("@{0}{1}", paramnameroot, paramnbr++); parameternames.add(paramname); parameters.add(cmd.parameters.addwithvalue(paramname, value)); } cmd.commandtext = cmd.commandtext.replace("{" + paramnameroot + "}", string.join(separator, parameternames)); homecoming parameters.toarray(); } }

it called this...

var cmd = new sqlcommand("select * tablea age in ({age})"); cmd.addarrayparameters(new int[] { 1, 2, 3 }, "age");

notice "{age}" in sql statement same parameter name sending addarrayparameters. addarrayparameters replace value right parameters.

c# tsql