How to Escape special characters in Microsoft SQL CE

Question

NB: I am using Microsoft SQL Compact Edition 3.5

I have a table of users.I have the display name as user input and I need to query all the user whose display name matches the input.

select TOP (1) * from users where display_name like 'Abby Parker'

here 'Abby parker' is the input

it is working fine in normal cases .But the problem is the display name can contain special characters

for eg display name can be "Abby Park#er" or simply "%&^%&^%#%" .The above query fails in such cases .I have already tried the solution specified here

Escaping special characters in a SQL LIKE statement using sql parameters

this is how I am building the query here

    var command = ceConnection.CreateCommand();
    command.CommandText = string.Format("select TOP (1) * from {0} where {1} like '[{2}]' ", tableName,fieldName, key);
 }

• {0}=>users
• {1}=>display_name
• {2}=>pattern

Thanks in advance

Answer 1

As posted here, please try the following:

var command = ceConnection.CreateCommand();
command.CommandText = string.Format("select TOP (1) * from {0} where {1} like @key ", tableName,
                    fieldName);
command.Parameters.AddWithValue("@key", key);