Into

While building out push notifications for Twit-A-Dex I wanted to build out a server application that took users twitter information and pushed notifications to their phones. At first I was considering polling the SQL database at a regular interval, however after reading all this stuff about push notifications in both Twitter and GCM I wanted to see if i could use a similar solution locally to set up my services for a new user when a new user account was committed to the database.

Problem

After a quick search I found SqlDependency was probably my solution however I could not find a good way of using it without getting a single notifications, or a million instant notifications etc..

Tutorial

So this tutorial is meant to be a cut and paste. 1. Create a new database called ‘NotificationDependency’ and create this table:

  
CREATE TABLE [dbo].[Note](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nchar](10) NOT NULL,
 CONSTRAINT [PK_Note] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2. Create a sql user the application will be able to connect with and give it Read/Write/Create Stored Procs/Execute Stores Procs

3. Run this statement to enable the service broker:

  
ALTER DATABASE NotificationDependency SET ENABLE_BROKER with rollback immediate;

4. Create a new Console Application and create an app config with the connection string NotificationDependency 5. Create the following class:

  
    public class DatabaseChangeListener
    {
        private readonly string connectionString;
        private readonly SqlConnection connection;

        public delegate void NewMessage();
        public event NewMessage OnChange;

        public DatabaseChangeListener(string connectionString)
        {
            this.connectionString = connectionString;
            SqlDependency.Stop(connectionString);
            SqlDependency.Start(connectionString);
            connection = new SqlConnection(connectionString);
        }

        public DataTable Start(string changeQuery)
        {
            
            var cmd = new SqlCommand(changeQuery, connection) { Notification = null };
            var dependency = new SqlDependency(cmd);
            dependency.OnChange += NotifyOnChange;
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            var dt = new DataTable();
            dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));

            return dt;
        }

        void NotifyOnChange(object sender, SqlNotificationEventArgs e)
        {
            var dependency = sender as SqlDependency;
            if (dependency != null) dependency.OnChange -= NotifyOnChange;
            if (OnChange != null) { OnChange(); }
        }

        ~DatabaseChangeListener()
        {
            SqlDependency.Stop(connectionString);
        }
    }

7. Create a new EF model or have some way to query the database.

8. In the Main Add the following Code:

  
            var db = new NotificationDependencyEntities();
            var maxId = db.Notes.Max(x => x.Id);

            var con = System.Configuration.ConfigurationManager.ConnectionStrings["NotificationDependency"];
            var changeListener = new DatabaseChangeListener(con.ConnectionString);
            changeListener.OnChange += () =>
                                           {
                                               Console.WriteLine("There was a change");
                                               maxId = db.Notes.Max(x => x.Id);
                                               changeListener.Start(@"SELECT [Id] ,[Name] FROM [dbo].[Note] WHERE [Id] > " + maxId);
                                           };
            changeListener.Start(@"SELECT [Id] ,[Name] FROM [dbo].[Note] WHERE [Id] > " + maxId);

            Console.ReadLine();

9. Finally you can run the application, then run the following insert in some other application like SSMS to see the result

  
INSERT INTO [dbo].[Note]
           ([Name])
     VALUES
           ('hello')

Conclusion

Sql Dependency is not so bad, and in this example I used a query that allows me to prevent the whole table from being downloaded each time an event happens.
The OnChange event only gets called once each time so you will need to make sure the Start method is called again.