ADO.NET TableAdapter parameters

Question

I have a query that I wish to run through an ASP.NET TableAdapter that contains an 'IN' clause which is to receive it's values through a parameter.

My question is, how do I specify this parameter? I thought of writing the conditional statement like this:

AND b.group_category_id in (@ParamList)

where the @ParamList is a String of the parameters, e.g. "4,12,45,23" but since the specified id is an Integer, it complains that it cannot convert String to Integer. This makes sense, but is there any way to specify such a list in a SQL statement in an ASP.NET TableAdapter?

Answer 1

You might have a look at http://dotnet.org.za/johanvw/archive/2008/06/13/mssql-split-function.aspx and pass it indeed as a string. Kind of a workaround than a solution. But that would only be usable if you use MSSQL.

Answer 2

One workaround I've seen:

WHERE charindex(',' + cast(b.group_category_id as varchar) + ',', @ParamList) > 0

In this case the @ParamList would be a string in the form ",4,12,45,23,"

It's not "pretty", but preserves the parameter and benefits of a compiled query. Since you are searching for numbers, the sub-string guarantees a unique match.