C# 如何在带参数的 SQL 查询中使用通配符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19730941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use wildcards in SQL query with parameters
提问by ovaltein
Say I have a basic query, something like this:
假设我有一个基本查询，如下所示：
SELECT holiday_name
FROM holiday
WHERE holiday_name LIKE %Hallow%
This executes fine in my sql query pane and returns 'Halloween'. My problem occurs when I try to use parameters with with the wildcard '%' characters in my code.
这在我的 sql 查询窗格中执行良好并返回“万圣节”。当我尝试在代码中使用带有通配符 '%' 字符的参数时，会出现我的问题。
SqlConnection Connection = null;
SqlCommand Command = null;
string ConnectionString = ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString;
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE %@name%";
Connection = new SqlConnection(ConnectionString);
try
{
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
Command.Parameters.Add(new SqlParameter("name", HolidayTextBox.Text));
var results = Command.ExecuteScalar();
}
catch (Exception ex)
{
//error stuff here
}
finally
{
Command.Dispose();
Connection.Close();
}
This throws an incorrect syntax error. I've tried moving the '%' to my parameter like so
这会引发不正确的语法错误。我试过像这样将 '%' 移动到我的参数
Command.Parameters.Add(new SqlParameter("%name%", HolidayTextBox.Text));
but then I receive an error saying I haven't declared the scalar variable '@name'. So, how do you properly format wildcard characters to be included with query parameters? Any help is appreciated!
但随后我收到一条错误消息，指出我尚未声明标量变量“@name”。那么，如何正确格式化要包含在查询参数中的通配符？任何帮助表示赞赏！
采纳答案by AllenG
First off, your SqlParametername is @namenot name.
首先，你的SqlParameter名字@name不是name。
Second, I would move your wildcards.
其次，我会移动你的通配符。
So it would look like this:
所以它看起来像这样：
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE @name;"
Connection = new SqlConnection(ConnectionString);
try
{
var escapedForLike = HolidatyTextBox.Text; // see note below how to construct
string searchTerm = string.Format("%{0}%", escapedForLike);
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
Command.Parameters.Add(new SqlParameter("@name", searchTerm));
var results = Command.ExecuteScalar();
}
Note that LIKErequires special care when passing parameters and you need to escape some characters Escaping special characters in a SQL LIKE statement using sql parameters.
请注意，LIKE在传递参数时需要特别小心，并且您需要转义某些字符使用 sql 参数在 SQL LIKE 语句中转义特殊字符。
回答by gordy
whatever you do don't do this:
无论你做什么，都不要这样做：
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE '%'" + HolidayTextBox.Text + "'%'";
as that will open you up to sql injection, instead do this:
因为这会让你接受 sql 注入，而是这样做：
Command.Parameters.Add(new SqlParameter("@name", "%" + HolidayTextBox.Text + "%"));
you may like to know about Command.Parameters.AddWithValue, e.g:
您可能想了解 Command.Parameters.AddWithValue，例如：
Command.Parameters.AddWithValue("@name", "%" + HolidayTextBox.Text + "%");
回答by Tim S.
The %s should be part of the search string, not the query.
在%S的关系是搜索字符串，而不是查询的一部分。
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE @name";
Connection = new SqlConnection(ConnectionString);
try
{
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
string name = "%" + HolidayTextBox.Text + "%";
Command.Parameters.Add(new SqlParameter("@name", name));
