asp.net mvc - Inner join using ado.net is not working properly -


inner join using ado.net not working properly. please see attached code , let me know. receiving string array values in "values". think have problem in code itself.

public jsonresult searchdata(string[] values,string[] values1,string[] values2)         {             //string str;             //for (int = 0; <= values.length; i++)             //{             //    str = values[i];             //    console.writeline(str);             //}              using (sqlconnection connection = new sqlconnection("data source=.; database=srivatsava; integrated security=sspi"))             {                 dataset ds = new dataset();                 connection.open();                 sqlcommand cmd = connection.createcommand();                 string str= "select accntname,bu,salesop,isdormant fourth_page fg"+                                   " inner join linked ld on ld.productid=fg.productid"+                                    "inner join isdormant on it.productid=ld.productid"+                                    "where fg.accountname in" + values;                 cmd.commandtext = str;                 sqldataadapter mysqldataadapter = new sqldataadapter();                 mysqldataadapter.selectcommand = cmd;                 mysqldataadapter.fill(ds);                 foreach (datarow dr in ds.tables[0].rows)                 {                     console.writeline(dr[0].tostring() + "" + dr[1].tostring() + "" + convert.toint32(dr[2]) + "" + convert.toboolean(dr[3]));                 }                                 connection.close();             }             return json(values);          } 

values array, cannot inject query. need read each item array , use in in clause. below code should work fine.

i replaced sqldataadapter sqldatareader query data.

public jsonresult searchdata(string[] values) {     var result = new list<string>();     using (var con = new sqlconnection("put conn string here"))     {         using (var cmd = new sqlcommand())         {            var str= "select accntname,bu,salesop,isdormant fourth_page fg"+                             " inner join linked ld on ld.productid=fg.productid"+                             " inner join isdormant on it.productid=ld.productid ";               if (values != null && values.any())             {              var items = values.select((x, counter) => "@v" + counter);              str +=string.format(" fg.accountname in ({0})",                                                               string.join(",", items));                  var paramcounter = 0;                 foreach (var value in values)                 {                     cmd.parameters.addwithvalue("@v" + paramcounter, value);                     paramcounter++;                 }             }             cmd.commandtext = str;             cmd.connection = con;             con.open();             var reader = cmd.executereader();             if (reader.hasrows)             {                 while (reader.read())                 {                     var c1 = reader.getstring(reader.getordinal("accntname"));                     result.add(c1);                 }             }                          }     }     return json(result, jsonrequestbehavior.allowget); } 

Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -