如何在 Sql Server Compact Edition 中将参数与 LIKE 一起使用

Question

我正在尝试参数化使用带有通配符的 LIKE 关键字的搜索查询。原始的sql有这样的动态sql：

"AND JOB_POSTCODE LIKE '" + isPostCode + "%' "

所以​​我尝试了这个，但是我得到了一个FormatException：

"AND JOB_POSTCODE LIKE @postcode + '%' "

编辑：我猜FormatException不会来自Sql Server CE，所以根据要求，这是我设置的方式我的 C# 代码中的参数。该参数在如下代码中设置：

command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode;

我也尝试过：

"AND JOB_POSTCODE LIKE @postcode"

with

command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode + "%";

但不会返回任何结果。谁能建议如何在此搜索sql中使用参数？

Answer 1

简而言之，您应该将通配符放在参数的值中，而不是放在 CommandText 中。即

不是：
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode%"

这个：

sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode + "%";

这里很长的答案：

我回去把我的代码精简到最基本的部分，这样我就可以把它发布在这里，同时这样做我发现我在原来的问题中尝试的最后一种方法确实有效。一定是我的测试出了问题。所以这里是一个总结，其中包含已运行的完整代码：

原始动态sql，容易受到sql注入：

//Dynamic sql works, returns 2 results as expected, 
//but I want to use parameters to protect against sql injection

string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE '" 
                         + postCode + "%'";
return Database.fGetDataSet(sqlCommand, 
                            iiStartRecord, 
                            iiMaxRecords, 
                            "JOBVISIT");

第一次尝试使用参数给出错误：

//This syntax with a parameter gives me an error 
//(note that I've added the NVarChar length as suggested:
//System.FormatException : @postcode : G20 - 
//Input string was not in a correct format.
//at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
//at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor,
// Boolean& isBaseTableCursor)

string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode 
                         + '%'";
sqlCommand.Parameters.Add("@postcode", 
                          SqlDbType.NVarChar, 
                          10).Value = postCode;
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");

第二种技术确实如此实际工作：

///This syntax with a parameter works, returns 2 results as expected
string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode 
                                                                   + "%";
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");

感谢您的所有意见，并对最初的误导性问题表示歉意......

Answer 2

这会在 SQL Server 05 中返回适当的结果（也可以封装在 SP 中），因此您尝试的最后一件事似乎应该有效。但我没有紧凑版的测试台，所以也许这会有所不同（我很想知道是否如此，以及为什么）。

declare @p1 nvarchar(50)
set @p1 = 'f'         -- initial value passed from your app
set @p1 = @p1 + '%'   -- edit value for use with LIKE
select * from JOB
where JOB_POSTCODE like @p1

编辑：

您使用的 SQLCE 版本是什么？您能判断参数值是否实际上从代码传递到数据库吗？我浏览了这个 MSDN 教程并且能够至少从 Visual Studio 查询设计器获得您正在寻找的结果。 （唯一的区别是我使用的是 VS 2008 和 SQL Server Compact 3.5）。请参阅标题为“创建新查询”的部分；我用一些数据模拟了一个表，这个查询按照您的预期工作。

SELECT     JobID, PostCode, OtherValue
FROM         Job
WHERE     (PostCode LIKE @p1 + '%')

就像我说的，我没有编写任何代码来调用查询，但它是在设计器内部工作的。换句话说，“它可以在我的机器上运行”。

Answer 3

使用：

"AND JOB_POSTCODE LIKE '" + isPostCode + "%' "

...意味着字符串是在附加到动态 SQL 之前构建的。这样您就不必在 sp_executesql/EXEC 的参数列表中指定参数，而 this:

"AND JOB_POSTCODE LIKE @postcode + '%' "

...则可以。请发布更多查询。

Answer 4

请发布您的完整示例（包括您正在组装调用的外部客户端代码）。如果您正确传递参数，第二个和第三个选项都应该有效。您是否在存储过程或内联参数化 SQL 中调用此函数？

我假设没有 SP，因为我只是看到您正在使用 CE...

我认为您需要 为您的 .Add 调用添加一个长度，因为它是一个 nvarchar。

Answer 5

select province_address 
from address 
where (@postcode is null or postcode like '%' + @postcode '%')

如果你这样使用，这意味着如果你没有在@postcode中发送任何值，它将带来所有值，否则它将只包含@postcode

Answer 6

你的答案是：

"AND JOB_POSTCODE LIKE '' + @postcode + '%' "

参数：

command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode;