如何转义Microsoft SQL CE中的特殊字符

NB: I am using Microsoft SQL Compact Edition 3.5

我正在使用微软SQL精简版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

这里的输入是“Abby parker”

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

使用SQL参数转义SQL类语句中的特殊字符

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
• { 0 } = >用户
• {1}=>display_name
• { 1 } = > display_name
• {2}=>pattern
• { 2 } = >模式

Thanks in advance

谢谢提前

1 个解决方案

#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);