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
            SqlDependency.Start(ConfigurationManager.AppSettings["Main.ConnectionString"]);
            //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
                    con.Open();

                    //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
                    cmd.ExecuteReader();

                }
            }

            //Stop the Dependency
            SqlDependency.Stop(ConfigurationManager.AppSettings["Main.ConnectionString"]);

            Console.Read();
           
          
        }

        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(
                PermissionState.Unrestricted);
            try
            {
                permission.Demand();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }
        }

   
 

      
    }
}

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