Whilst working with PetaPoco in our development environment, we wrote a Fetch command a little like this:
DB.Fetch<object>("SELECT Col1, Col2 FROM Tablename WHERE Col1 = @0, parameter)
The resultant command looks a lot like this:
SELECT Col1, Col2 FROM Tablename WHERE Col1 = parameter
In most database versions, this is fine, however when the target database was moved to an environment running SQL Server 2014, it becomes apparent that this isn't good enough any more - SQL Server 2014 enforces the requirement to wrap varchar/string based parameters in single quotes, whereas previous versions were able to make a type determination at the point the query is executed and this was never an issue.
I can't reproduce the issue, as I don't have a SQL Server 2014 environment to hand, but it's apparent that SQL is trying to parse what it thinks is an integer to a string and comes out with what looks like a Hex value. Not a particularly helpful indication of what's happening, I grant you
The logical conclusion would be to adapt our Fetch to look something like this:
DB.Fetch<object>("SELECT Col1, Col2 FRMO Tablename WHERE Col1 = '@0', parameter)
Annoyingly, our last command looks like this:
SELECT Col1, Col2 FROM Tablename WHERE Col1 = '@0'
I've tried a number of different ways of writing my fetch command, including using a SqlParameter object as the first argument in the parameter array, defining the type as varchar, but I cannot get the query to work as I'd expect. The closest I have come is to use either string.Format, or an interpolated string, but this poses an obvious security risk and I'd rather avoid it.
I found this question which seems to suggest that the 'correct' thing to do is to remove the single quotes, however as I say, this is a breaking behaviour in MSSQL 2014+...
Is this not a supported feature of PetaPoco, or am I missing something?
