A Little Bit OT: My Dataaccess statement doesn't work!

Jav posted on Friday, April 08, 2011

In my DataPortal_Fetch, he following statement works:
cm.CommandText = "SELECT MyKey, MyFruit FROM MyFruitTable WHERE (FruitCategory IN ('Red Fruit', 'Green Fruit'))";
                using (var dr = new SafeDataReader(cm.ExecuteReader()))
                      {
                          while (dr.Read())
                          {
                              Add(Fruits.GetFruit(dr));
                          }
                      }

But the folowing statement does not:
Parameters = "'Red Fruit', 'Green Fruit'";
cm.CommandType = CommandType.Text;
cm.Parameters.AddWithValue("@FruitCategories", Parameters );
cm.CommandText = "SELECT MyKey, MyFruit FROM MyFruitTable WHERE (FruitCategory IN (@FruitCategories))";
                using (var dr = new SafeDataReader(cm.ExecuteReader()))
                      {
                          while (dr.Read())
                          {
                              Add(Fruits.GetFruit(dr));
                          }
                      }

NOR this one:
Parameters = "'Red Fruit', 'Green Fruit'";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@FruitCategories", Parameters);
cm.CommandText = "GetFruitByCategory";
using (var dr = new SafeDataReader(cm.ExecuteReader()))
     {
        while (dr.Read())
        {
            Add(Fruits.GetFruit(dr));
        }
     }

tmg4340 replied on Friday, April 08, 2011

SQL Server doesn't support parameterized IN queries (and I don't think Oracle does either).  If you want to do it via stored procedure, then your SP has to build SQL to execute.  If you're using SS 2008, you could create a table parameter and do it via JOIN (I think Oracle has a similar construct, but I'm not sure about that.)  But otherwise, dynamic SQL is the only way I know of to make it work.

HTH

- Scott

Jav replied on Friday, April 08, 2011

Thanks Scott.  I didn't know that.

Jav