Getting notification from Sql Server

Question

I'd like to use SqlDependency class in my winforms application :

    public partial class Form1 : Form
    {
        private int changeCount = 0; 
        private const string statusMessage = "{0} changes have occurred."; 

        private static SqlConnection connection = null;
        private static SqlCommand command = null;
        private static SqlDependency dependency;

        private static SqlCommand command1 = null;
        private static SqlDependency dependency1;

        public Form1()
        {
            InitializeComponent();
            button1.Enabled = CanRequestNotifications();
            this.FormClosed += Form1_FormClosed;
            if (connection == null)
            {
                connection = new SqlConnection(GetConnectionString());
            }

            if (command == null)
            {
                command = new SqlCommand("procCreationUser", connection);
                command.CommandType = CommandType.StoredProcedure;
            }
            dependency = new SqlDependency(command);

            if (connection == null)
            {
                connection = new SqlConnection(GetConnectionString());
            }

            if (command1 == null)
            {
                command1 = new SqlCommand("procSelectionUser", connection);
                command1.CommandType = CommandType.StoredProcedure;
            }
            dependency1 = new SqlDependency(command1);
            GetData();
        }

        private void GetData()
        {

            SqlDependency.Start(GetConnectionString());
            if (connection.State != ConnectionState.Open) connection.Open();
            using (var dr = command.ExecuteReader())
            {
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
            }

            using (var dr = command1.ExecuteReader())
            {
                dependency1.OnChange += new OnChangeEventHandler(dependency_OnChange);
            }

        }
        private string GetConnectionString()
        {
            return @"Data Source=PRT-12\SQLEXPRESS; Initial Catalog=TestNotification;Integrated Security=True";
        }


        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            MessageBox.Show("Notification");      
        }

    } 

I created two stored procedures : procCreationUser for creation and procSelectionUser to select users.

When I launched the application, and I clicked into the button and insert a new row in the database I haven't no notification !!!!!

1. How can I fix my code?
2. Is it the best way to get notification from Sql Server?

Answer 1

I recommend you read these:

• Using SQL Trace to Troubleshoot Query Notifications
• Troubleshooting Query Notifications: Checking a Subscription
• Troubleshooting Query Notifications: Subscription Registered, Event Message Not Produced, Subscription Removed
• Troubleshooting Dialogs
• The Mysterious Notification

You need to establish that the following event all occur:

• your query creates a notification subscription. Check the Profiler events mentioned in above articles, check in sys.dm_qn_subscriptions
• check that modifying the data invalidates the subscription, use the Profiler events mentioned in above articles
• check that notification messages get delivered, specially check transmission_status in sys.transmission_queue
• check that your code has succesfully set up the notification listener. Your app must call SqlDependency.Start once per appdomain and ideally should call SqlDependency.Stop when the appdomain shuts down.

Once you get the notification flowing make sure you check and honor the Source, Info and Type in your SqlNotificationEventArgs arg. Not all combinations indicate success, the notification may also fire immediately indicating a problem with your subscription.

Also, a small tidbits with your code:

• you need to hook up the SqlDependency before executing the query:

  dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); using (var dr = command.ExecuteReader()) { while (dr.Read()) { ... } }

• you need to call SqlDependency.Start once per appdomain. Try calling it in Main, before displaying the Form

Answer 2

Normally you should subscribe to event first dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); and only after that you will receive updates from command.ExecuteReader().
For two separate commands it is probably better to have two separate SqlDependency.