I ran into a problem today where I was trying to set up a SqlDependency against a table, with what I thought was a plain vanilla select statement:
private const string SqlCommandText =
"Select ImportDate From dbo.Imports Where ImportDate = '{0:yyyyMMdd}'";
I was formatting this string with a date/time value at runtime, then passing that into my SqlCommand object, then attempting to register the dependency. Well guess what – it didn’t work! The SqlNotificationEventArgs class kept telling me that my statement was invalid. Checking the special considerations using query notifications, I couldn’t see anything wrong with my query, so I was a bit lost.
Anyway, I decided to axe the “Where” condition from my query, and sure enough, it worked! So, by process of elimination, I figured it was something to do with converting a date from a character string that was throwing it. Not sure why; this works fine when run as T-Sql, or within a normal query…
To get around this problem, I used the “best practice” approach of using a parameter in the SqlCommand object:
private const string SqlCommandText =
"Select ImportDate From dbo.Imports Where ImportDate = @ImportDate";
protected SqlCommand CreateSqlCommand() {
var command = new SqlCommand(SqlCommandText);
command.Parameters.Add(new SqlParameter("ImportDate", SqlDbType.DateTime) {
Value = PositionDate
});
return command;
}
To my surprise, this worked a treat! I guess in the end, it wasn’t all that surprising, however I for some reason thought that using a parameter would surely not work; hence the reason I was pre-formatting the date/time in the first place!
