Tuesday, April 16, 2013

Notifications : Service Broker, SqlDependency and NotifyIcon

Hi Guys,

This time I made a small application to show notifications using Service Broker (Query Notifications), SqlDependency and NotifyIcon.

Before you Start,

1) Sql Server (Any Version)
2) Visual Studio (Any Version)

Step One : Enable Service Broker for the Database you work on.

Step Two : Add the Following Code in a Console/Windows Application
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Security.Permissions;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace TestApplication
    class Program
        static void Main(string[] args)
            //Start the Dependency
            //Create the Sql Connection
            SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["Main.ConnectionString"]);
            using (con)
                //Check whether Notifications can be requested
                if (CanRequestNotifications())
                    //Open the Connection
                    //Create SqlCommand and add parameters
                    SqlCommand cmd = new SqlCommand("SELECT [Message] FROM Message WHERE UserId = @UserId", con);
                    cmd.Parameters.AddWithValue("@UserId", 2303);
                    //Clear notifications
                    cmd.Notification = null;
                    //Create SqlDependency Passing SqlCommand
                    SqlDependency dependency = new SqlDependency(cmd, null, 5);
                    //Add Event Handler to OnChange
                    dependency.OnChange += dependency_OnChange;
                    //Execute the SqlCommand to Bond the Dependency
            //Stop the Dependency
        static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            //Make the instance of NotifyIcon
            NotifyIcon notifyIcon = new NotifyIcon();
            notifyIcon.Icon = SystemIcons.Information;
            notifyIcon.Visible = true;
            notifyIcon.ShowBalloonTip(5000, "New Message", "You have Received a New Message", ToolTipIcon.Info);
        //Method to check whether Notifications can be requested
        public static bool CanRequestNotifications()
            SqlClientPermission permission =
                new SqlClientPermission(
                return true;
            catch (System.Exception)
                return false;

Step Three: Run the Program and Make Some changes to the Dependent Data