C# 在 SQL LIKE 子句中使用 SqlParameter 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/665129/
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
Use of SqlParameter in SQL LIKE clause not working
提问by nmdr
I have the following code:
我有以下代码：
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";
using (var command = new SqlCommand(Sql, Connection))
{
command.Parameters.AddWithValue("@SEARCH", searchString);
...
}
This does not work, I tried this as well:
这不起作用，我也试过这个：
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";
using (var command = new SqlCommand(Sql, Connection))
{
command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");
...
}
but this does not work as well. What is going wrong? Any suggestions?
但这也行不通。出了什么问题？有什么建议？
采纳答案by Marc Gravell
What you want is:
你想要的是：
tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'
(or edit the parameter value to include the % in the first place).
（或编辑参数值以首先包含 %）。
Otherwise, you are either (first sample) searching for the literal"@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).
否则，您要么（第一个示例）搜索文字“@SEARCH”（不是 arg 值），要么在查询中嵌入一些额外的引号（第二个示例）。
In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:
在某些方面，让 TSQL 只使用LIKE @SEARCH并在调用者处处理它可能更容易：
command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");
Either approach should work.
任何一种方法都应该有效。
回答by Charles Graham
You could do LIKE @SEARCHand in your C# code, do
你可以做LIKE @SEARCH，在你的 C# 代码中，做
searchString = "%" + searchString + "%"
回答by Ali Almasian
Instead of using:
而不是使用：
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";
Use this code:
使用此代码：
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE '%' + @SEARCH + '%' OR tblCustomerInfo.Info LIKE '%' + @SEARCH + '%');";
回答by LeoFraietta
Just a little careful with a slight difference between Addand AddWithValuemethods. I had the problem below, when I used the Addmethod and put the wrong SqlTypeparameter.
稍微注意一下Add和AddWithValue方法之间的细微差别。当我使用Add方法并输入错误的SqlType参数时，我遇到了下面的问题。
ncharandnvarcharcan store Unicodecharacters.charandvarcharcannot store Unicodecharacters.
nchar并且nvarchar可以存储Unicode字符。char并且不能存储 Unicode字符。varchar
For example:
例如：
string query = " ... WHERE stLogin LIKE @LOGIN ";
SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.Char, 255)
{
Value = "%" + login + "%"
};
command.Parameters.AddWithValue(p.ParameterName, p.Value); //works fine!!!
command.Parameters.Add(p); // won't work
When I changed the SqlTypeto NVarChar, the two methods worked fine to me.
当我将SqlType更改为NVarChar 时，这两种方法对我来说都很好。
SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.NVarChar, 255)
{
Value = "%" + login + "%"
};
command.Parameters.AddWithValue(p.ParameterName, p.Value); //worked fine!!!
command.Parameters.Add(p); //worked fine!!!
