C# 如何在Sql Server Compact Edition中使用LIKE参数

How to use parameter with LIKE in Sql Server Compact Edition

提问by Colin

I'm trying to parameterise a search query that uses the LIKE keyword with a wildcard. The original sql has dynamic sql like this:

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

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

So I've tried this instead, but I get a FormatException:

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

"AND JOB_POSTCODE LIKE @postcode + '%' "

Edit: I guess the FormatException isn't going to be coming from Sql Server CE, so as requested, here is how I set the parameter in my C# code. The parameter is set in code like this:

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

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

I also tried:

我也试过：

"AND JOB_POSTCODE LIKE @postcode"

with

和

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

but that doesn't return any results. Can anyone advise how to use parameters in this search sql?

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

采纳答案by Colin

The short answer is that you should put the wildcard in the Value of the parameter, not in the CommandText. i.e.

简短的回答是您应该将通配符放在参数的值中，而不是放在 CommandText 中。IE

not that: sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode%"

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

this:

这个：

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

Long answer here:

长答案在这里：

I went back and stripped my code down to the essentials so that I could post it here, and while doing that I discovered that the last method I tried in my original question does actually work. Must have been something wrong in my testing. So here's a summary, with full code that's been run:

我回过头来将我的代码精简为基本要素，以便我可以将其发布在这里，同时我发现我在原始问题中尝试的最后一种方法确实有效。在我的测试中一定是出了什么问题。所以这是一个摘要，其中包含已运行的完整代码：

Original dynamic sql, vulnerable to sql injection:

原始动态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");

First attempt to use parameter gives an error:

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

//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");

Second technique does actually work:

第二种技术确实有效：

///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");

Thanks for all the input, and sorry about the original misleading question...

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

回答by OMG Ponies

Using:

使用：

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

...means that the string is constructed before being tacked onto the dynamic SQL. This is so that you don't have to specify the parameter in the parameter list for sp_executesql/EXEC, while this:

...意味着字符串在被添加到动态 SQL 之前被构造。这样您就不必在 sp_executesql/EXEC 的参数列表中指定参数，而这样做：

"AND JOB_POSTCODE LIKE @postcode + '%' "

...does. Please post more of the query.

...做。请发布更多查询。

回答by Cade Roux

Please post your complete example (including the outer client code where you are assembling your call). Both your second and third options should work if you are passing the parameter correctly. Are you calling this in a stored procedure or inline parameterized SQL?

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

I assume no SPs since I just see you are using CE...

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

I think you need to add a length to your .Addcallsince it's an nvarchar.

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

回答by Matt

This returns appropriate results in SQL Server 05 (also works wrapped in a SP), so it seems like the last thing you tried should have worked. But I don't have a test bed for Compact Edition, so maybe that makes a difference (I'd be curious to see if that's so, and why).

这会在 SQL Server 05 中返回适当的结果（也可以在 SP 中运行），因此您尝试的最后一件事似乎应该有效。但是我没有 Compact Edition 的测试台，所以也许这会有所作为（我很想知道是否是这样，以及为什么）。

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

EDIT:

编辑：

What version of SQLCE are you using? Can you tell if your parameter values are actually being passed from your code to the DB? I skimmed through this MSDN tutorialand was able to get the results you're looking for, at least from the Visual Studio Query Designer. (only difference is that I'm using VS 2008 and SQL Server Compact 3.5). See the section titled "Creating a new query"; I mocked up a table with some data and this query worked as you intend.

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

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

Like I said, I didn't write any code to call the query, but it worked from within the designer. In other words, "it works on my machine".

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

回答by Ali Almasian

Your answer is:

你的回答是：

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

and parameter:

和参数：

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