Colin Colin - 2 months ago 7
C# Question

How to use parameter with LIKE in Sql Server Compact Edition

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 + "%' "


So I've tried this instead, but I get a 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:

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?

Answer

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

not that: 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:

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