DAAB: Not able to add parameters dynamically to the IN clause

Topics: Data Access Application Block
Jul 4, 2013 at 12:55 AM
Edited Jul 4, 2013 at 1:14 AM

I am using DAAB 5.0.
I have written an SQL query and want to execute commandType as Text (and not as a stored proc)
Here, my SQL has IN clause. The IN clause parameters vary dynamically.
Please give me sample code for the same. Its really urgent. Appreciate for the assistance.

I am not able to get below code using DAAB
var connect = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  var sql = "SELECT * FROM Products WHERE CategoryID IN ({0}) ORDER BY CategoryID";
  var values = Request.Form["categories"];
  var valuearray = values.Split(',');

  var parms = valuearray.Select((s, i) => "@p" + i.ToString()).ToArray();
  var inclause = string.Join(",", parms);

  using (var conn = new SqlConnection(connect))
    var cmd = new SqlCommand(string.Format(sql, inclause), conn);
    for (var i = 0; i < valuearray.Length; i++)
      cmd.Parameters.AddWithValue(parms[i], valuearray[i]);
    var reader = cmd.ExecuteReader();
    GridView1.DataSource = reader;
Jul 4, 2013 at 2:42 PM
You will need to try a different approach. See http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable.

If you want to use "IN" then you will have to manipulate the string to embed the values directly in the SQL string. However, this can open you up to SQL injection attacks. To use SQL parameters you can build the SQL string dynamically and add multiple "OR" conditions with a SQL parameter for each one.

Randy Levy
Enterprise Library support engineer
Support How-to