How to use LIKE % for two or more fields with OR in C#

Question

I couldn't find answer on my problem so I had to rise new question. Namely I would like to ask you how to use LIKE +value from textbox+ with % - wildcards (missing letters) but for two or more fields. When I use below code for one field it's working (and I can search in my DB E.G. all FirstNames which starts from "A"):

"SELECT * FROM customer WHERE FirstName LIKE '"+TextBox1.Text + '%'+ "'";

But when I trying to use it for two fields, It's not working,(and I can't search in my DB e.g. any FirstNames or LastNames which starts from "R". I didn't get any errors. Just nothing is happen):

"SELECT * FROM customer WHERE FirstName LIKE '"+TextBox1.Text + '%'+ "' OR LastName LIKE '" +TextBox2.Text + '%'+"'";

Thank you in advance for any advice.

Answer 1

You should not create your queries by string concatenation otherwise you're exposing your application to SQL Injection. Better use command with parameters - that will also help you to not need think much about string escaping which is probably causing your current issue.

using (SqlCommand cmd = new SqlCommand())
using (SqlConnection conn = new SqlConnection("connectionString"))
{
       cmd.CommandText = "SELECT * FROM customer WHERE FirstName LIKE @first OR LastName LIKE @second";
       cmd.Parameters.Add(new SqlParameter("first", SqlDbType.NVarChar, 255).Value = "%" + TextBox1.Text + "%");
       cmd.Parameters.Add(new SqlParameter("second", SqlDbType.NVarChar, 255).Value = "%" + TextBox2.Text + "%");
       // todo: execute
}

You could also verify that query you're currently creating in your app (just set breakpoint before execution or simply log it somewhere to file f.e.) is returning results you'd expect in case you'd execute it directly on server. That could help you examine some more issues.