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.

Answer 2

I see how @Jaroslav Kadlec is doing it, he is not wrong but he has one problem. He is using the depricated method. To add paramaters there is an new method:

using (SqlConnection conn = new SqlConnection("connectionString")){
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "SELECT * FROM customer WHERE FirstName LIKE @first OR LastName LIKE @second";
    cmd.Parameters.AddWithValue("@first", TextBox1.Text + "%");
    cmd.Parameters.AddWithValue("@second", TextBox2.Text + "%");
}}

Answer 3

If you have a dynamic number of tokens, one convoluted way to do your task is by defining a temporary table and join with it:

using (var conn = new SqlConnection(connectionStr))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        var querySb = new StringBuilder(@"
            CREATE TABLE #temp (Token NVARCHAR(20) NOT NULL)");

        //tokensArray will contain texts from your text boxes
        for (int i = 0; i < tokensArray.Length; i ++)
        {
            querySb.Append($"INSERT INTO #temp VALUES(@p{i})");

            // parameterized query, to protect against SQL injection (input is coming from user)
            cmd.Parameters.AddWithValue($"@p{i}", $"%{token}%");
        }

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = querySb.ToString();

        querySb.Append(@"; 
SELECT C.* 
FROM customer C 
    JOIN #temp T ON C.FirstName LIKE T.Token";

        // execute stuff
    }
}

You can also consider sending tokens in a concatenated string and use a stored procedure to split them and make the filtering in T-SQL.

Credit goes to this answer.

Answer 4

It is better practice to First assign Text box values to string variables then use them as below

String Firsttext = TextBox1.Text;
String SecondText = TextBox2.Text;

Then you can use it as follows

"SELECT * FROM customer WHERE FirstName LIKE '"+ Firsttext + "%" + "' OR LastName LIKE '" +SecondText + "%"+"'";