如何使用参数，就像在SQL Server精简版

问题描述

我试图对其进行参数使用LIKE关键字与通配符搜索查询。原来的SQL具有动态SQL是这样的：

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

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

所以，我已经试过这个代替，但我得到一个出现FormatException：

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

"AND JOB_POSTCODE LIKE @postcode + '%' "

编辑：我猜出现FormatException不会从SQL Server CE的未来，这样的要求，这里是我在我的C＃code设置的参数。该参数设置在code是这样的：

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:

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

我也试过：

"AND JOB_POSTCODE LIKE @postcode"

与

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

，但不返回任何结果。任何人都可以建议如何在此搜索SQL中使用参数？

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

推荐答案

简短的回答是，你应该把通配符参数的值，而不是在CommandText。即

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

不是： sqlCommand.CommandText =SELECT * FROM JOB WHERE JOB_POST code LIKE @交code％

这样的：

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

龙答案在这里：

Long answer here:

我回去，并剥夺我的code到要领，这样我可以在这里发布它，而这样做，我发现，我想在我原来的问题的最后方法不实际工作。一定是错误的东西在我的测试。所以这里有一个总结，以饱满的code，它一直运行：

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:

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

感谢所有的输入，遗憾原误导性的问题...

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

这篇关于如何使用参数，就像在SQL Server精简版的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！