How to Use SqlDependency

1. Start Service Broker for the DB

USE Master
ALTER DATABASE [DBName] SET ENABLE_BROKER;
//If there are transactions pending in this db, use following command
ALTER DATABASE [DBName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

2. Assign GUEST rights for QueryNotificationService

USE [DBName]
GRANT SEND ON SERVICE::
[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]
TO Guest

Note For SqlDependency class
1. Each time the OnChange event fired, need to re-subscript

Note For SELECT Statement:
1. The column name can't use *
2. with (nolock) is not allowable
3. TableName need to use dbo.TableName

Supported SELECT Statements Reference
http://msdn.microsoft.com/en-us/library/ms181122.aspx