using where statement with sqldependency?

[jrenae]

Hello,

We have a service that is kicked off by a sqldependency notification. The query associated with the command object is selecting all id's from a table, which is working fine, except I really don't want the service to fire unless a record is inserted that has a certain status. So I would like to add a where statement to the query. Is that allowed? Can I change the query from this:

SELECT LeadDeliveryLog.LeadDeliveryLogID FROM dbo.LeadDeliveryLog

to this?

SELECT LeadDeliveryLog.LeadDeliveryLogID FROM dbo.LeadDeliveryLog Where LeadDeliveryLog.Status = 'rejected'

Thanks in advance

 

[AlexCuse]

Unless I'm missing something, yes you can. It sounds like SQL Injection is not a worry here, but you might still want to consider using parameters to make your query more flexible (there are slight performance gains to be had here as well).

using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SELECT LeadDeliveryLog.LeadDeliveryLogID FROM dbo.LeadDeliveryLog Where LeadDeliveryLog.Status = @status", connection))
{
    command.Parameters.Add("@status", System.Data.SqlDbType.VarChar).Value = "rejected";
    System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        //do some stuff
    }
}

}

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[jrenae]

Thank you...It did work with the where clause I entered above. No worry about sql injection as it's only an internal app using it.