Quickduck

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!