Как использовать параметр с LIKE в Sql Server Compact Edition

Я пытаюсь параметризовать поисковый запрос, который использует ключевое слово LIKE с подстановочным знаком. Оригинальный sql имеет динамический sql, как это:

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

Итак, я пробовал это вместо этого, но получаю исключение FormatException:

"AND JOB_POSTCODE LIKE @postcode + '%' "

Изменить: я думаю, что FormatException не будет поступать с Sql Server CE, так как запрошено, вот как я установил параметр в свой код на С#. Параметр задается в коде следующим образом:

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

Я также пробовал:

"AND JOB_POSTCODE LIKE @postcode"

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

но это не возвращает никаких результатов. Может ли кто-нибудь посоветовать, как использовать параметры в этом поисковом sql?

Ответ 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");

Спасибо за все входные данные и извините за оригинальный вводящий в заблуждение вопрос...

Ответ 2

Это возвращает соответствующие результаты в 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:

Какую версию SQLCE вы используете? Можете ли вы сказать, действительно ли ваши значения параметров передаются из вашего кода в БД? Я просмотрел этот учебник по MSDN и смог получить результаты, которые вы ищете, по крайней мере, из конструктора запросов Visual Studio. (только разница в том, что я использую VS 2008 и SQL Server Compact 3.5). См. Раздел "Создание нового запроса"; Я издевался над таблицей с некоторыми данными, и этот запрос работал так, как вы планируете.

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

Как я уже сказал, я не писал код для вызова запроса, но он работал в дизайнере. Другими словами, "это работает на моей машине".

Ответ 3

Использование:

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

... означает, что строка строится до привязки к динамическому SQL. Это значит, что вам не нужно указывать параметр в списке параметров для sp_executesql/EXEC, а это:

"AND JOB_POSTCODE LIKE @postcode + '%' "

... делает. Пожалуйста, разместите больше запросов.

Ответ 4

Пожалуйста, разместите свой полный пример (включая внешний код клиента, в котором вы собираете свой вызов). Оба ваших второго и третьего варианта должны работать, если вы правильно передаете параметр. Вы вызываете это в хранимой процедуре или встроенном параметризованном SQL?

Я не предполагаю никаких SP, поскольку я просто вижу, что вы используете CE...

Я думаю, вам нужно добавить длину к вашему вызову .Add, так как это nvarchar.

Ответ 5

Ваш ответ:

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

и параметр:

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