在 SQLLike 子句中使用 SqlParameter 不起作用

我有以下密码:

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);
...
}

这没用，我也试过了:

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 + "%'");
...
}

但是这个不太管用。出了什么问题? 有什么建议吗？

90979 次浏览
小开
最佳答案

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).

In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");

Either approach should work.

小开

You could do LIKE @SEARCH and in your C# code, do 

searchString = "%" + searchString + "%"
小开

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 + '%');";
小开

Just a little careful with a slight difference between Add and AddWithValue methods. I had the problem below, when I used the Add method and put the wrong SqlType parameter.

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.

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 SqlType to NVarChar, the two methods worked fine to me.

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!!!
小开

You can actually directly add the value instead of using parameter. For example this is how I implemented it and it works fine. Cheers!

string name = "test";
string  nameCondition= " and FirstName like '%"+ name +"%' or MiddleName like '%" + name +"%' or LastName like '%"+name+"%'";


提交答案