Bobby Edward wrote:
I have an advanced search box. The user can type in multiple words in
the box. Those words are then used in the WHERE clause against a
Description db field.
So these words: plumber carpenter electrician
Would essentially equate to: "WHERE (Description LIKE '%plumber%') OR
(Description LIKE '%carpenter%') OR (Description LIKE '%electrician%')"
Is there any easy way to dynamically create this WHERE clasue? I know
how to do it manually by code, but I didn't know if I had to manually
parse the tokens and construct the clause or if there was an easier
way...
(I'm using MySQL.)
This may not apply because you're using MySQL, but with SQL Server, you can
use parameterized queries. Parameterized queries allow you to build dynamic
SQL statements that are not susceptible to SQL Injection. You can add
multiple parameters to the command object allowing you to run queries such
as "where x or y or z". The code below is the basic idea ...
SqlCommand cmd = new SqlCommand();
SqlParameter param =
new SqlParameter("@Description1", SqlDbType.VarChar);
param.Value = "%" + "plumber" + "%";
cmd.Parameters.Add(param);
param =
new SqlParameter("@Description2", SqlDbType.VarChar);
param.Value = "%" + "carpenter" + "%";
cmd.Parameters.Add(param);
string Sql =
" SELECT SomeColumns " +
" FROM YourTable " +
" WHERE Description LIKE @Description1 " +
" OR Description LIKE @Description2; ";
SqlConnection conn =
new SqlConnection("your connection string");
cmd.CommandText = Sql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
--
Ben
http://allben.net/