In the below example,If i pass a single value to mycompany variable say mycompany="100",
the below execquery() method works fine.But when i pass more than one value to parameter then it fails for the below case explained below.
please help me to to make it work.
let us say i have a method that uses the parameterised query like this:
number myid=1000;
var mycompany="100,102,104,105,106";
string sql=select * from employee where
id=@0 and company in (@1);
the call to the below method takes place as below:
var dt=executequery(sql,conn,myid,mycompany)
and the method definition is given below:
public static DataTable Executequery(string sql, string connectionString, params object[] args)
{
using (var connection = new SqlConnection(connectionString))
{ using (var command = new SqlCommand(sql, connection))
{
var parameters = args.Select((value, index) => ToSqlParameter(command, index.ToString(CultureInfo.InvariantCulture), value));
command.Parameters.AddRange(parameters.ToArray());
if (connection.State == ConnectionState.Closed)
connection.open();
var dr = command.ExecuteReader();
var dt = new DataTable();
dt.load(dr);
return dt;
}
}
and ToSqlparamer() method definition is given below:It takes the params from args and creates an sqlparameter and adds to it.
SqlParameter ToSqlParameter(SqlCommand command, string name, object value)
{
var p = command.CreateParameter();
p.ParameterName = name;
p.Value = value;
return p;
}