DataTable.Select Expression using IN and Like.

Question

Greetings,

I have two questions regarding a DataTable.Select():

1) How to escape potential apostrophes or any other characters that would cause an issue. I'm not worried about SQL Injection. 2) How to use IN with Like, and include results that have a null.

1) Unfortunately, I can't seem to find any advice for the first option since every one says use parametrized (is that a word) queries which I'm pretty sure you can't do with the Datatable.select. The situation is like so, I don't know what the values are going to be ahead of time since the list is retrieved from a textbox. ('US' , 'Engineering' , 'Lumburg's') I know I could parse the string before passing it to the select but I'm sure there is some ANSI built in away of doing it which I'm afraid I don't know. I'm aware that two apostrophes in a row would escape the character if I knew where it was going to be located but I don't know the exact location when I receive it.

2) Ok. Now for the actual Select Expression. What I'm looking to do is allow the user to free form type into a text field. This text field will use 4 different columns to filter by. So the user could type in several items separated by space which would then perform a query per their criteria. (like the iTunes search box which allows you to type free form album song etc or you could be specific and only search by one type of item)

I know how to do an IN or a Like by themselves but I'm not sure how I could put them together (including escaping the apostrophes and including results that match but might have a null in one of the columns) and of course do something that is supported by the .Select method.

WHERE (Country IN ('US' , 'Engineering' , 'Lumburg's')) WHERE (Country LIKE 'LU%')

So the user in the case above provided LU which should return a record for Lumburg. The more information they provide would further refine the results. Any ideas?

Here is the technical specs regarding my application:

Winforms VB.NET .Net 2.0

Answer 1

You'll want to end up with a query that looks like:

Country = 'US' OR Country = 'Engineering' OR Country = 'Lumburg''s' OR Country LIKE 'LU%'

terms = //split apart their search entry
foreach (string searchTerm in terms)
{
  // I don't know how you're telling the difference between terms you want an exact
  // match for, and which use like, but do something like this

  if (exactMatchNeeded)
  {
    query += " OR Country = '" + searchTerm.Replace("'", "''") + "' ";
  }
  else
  {
    query += " OR Country LIKE '" + searchTerm.Replace("'", "''") + "' ";
  }
}

Of course you'll need to tidy that up to make sure of some things like preventing the extra 'OR' on the beginning, but this should get you started.