ADA.NET TableAdapter Options

Question

I have a query that I want to run through ASPAdapter ASP.NET, which contains the "IN" clause, which should take its values ​​through the parameter.

My question is: how to specify this parameter? I was thinking of writing a conditional statement as follows:

AND b.group_category_id in (@ParamList) 

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

Answer 1

Answering my question: this is impossible to do.

Answer 2

You can take a look at http://dotnet.org.za/johanvw/archive/2008/06/13/mssql-split-function.aspx and pass it as a string. Kind of a workaround than a solution. But this can only be used if you are using MSSQL.

Answer 3

One workaround I've seen:

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

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

This is not “beautiful,” but it retains the parameter and benefits of a compiled query. Since you are looking for numbers, the substring guarantees a unique match.

Answer 4

You can pass @ParamList through a comma-delimited string, parse the string and paste the results into #table, and then use IN to search for #table:

 AND b.group_category_id in (select group_category_id from #group_category_id_list) 

In addition, your options are limited if you do not want to use the dynamic SQL expression (exec ()), but I would advise you to avoid this if possible.