Click here to Skip to main content
15,885,366 members
Articles / Web Development / ASP.NET

Query Notification using SqlDependency and SqlCacheDependency

Rate me:
Please Sign up or sign in to vote.
4.84/5 (90 votes)
3 Nov 2012CPOL12 min read 463.4K   17.5K   196  
The article demonstrates how you can use SQL Server Query Notification feature to get notification from database whenever the result of a command gets changed. Sample application demonstrating SqlDependency and SqlCacheDependency is also included.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Collections.ObjectModel;
using System.Windows.Threading;
using System.Data;
using System.Windows.Input;
using System.Data.SqlClient;

namespace NotificationMessages.MVVM
{
    public class MessageModel : INotifyPropertyChanged
    {
        private ObservableCollection<Message> messages = null;
        public ObservableCollection<Message> Messages
        {
            get
            {
                messages = messages ?? new ObservableCollection<Message>();
                return messages;
            }
        }

        private ICommand insertMessage;

        public ICommand InsertMessage
        {
            get
            {
                this.insertMessage = this.insertMessage ?? new DelegateCommand(this.ExecuteInsert,() => !string.IsNullOrEmpty(this.Title) && ! string.IsNullOrEmpty(this.Description));
                return this.insertMessage;
            }
        }
        
        public Dispatcher UIDispatcher { get; set; }

        public SQLNotifier Notifier { get; set; }
        public MessageModel(Dispatcher uidispatcher)
        {
            this.UIDispatcher = uidispatcher;
            this.Notifier = new SQLNotifier();

            this.Notifier.NewMessage += new EventHandler<SqlNotificationEventArgs>(notifier_NewMessage);
            DataTable dt = this.Notifier.RegisterDependency();


            this.LoadMessage(dt);
        }

        private string title;
        public string Title
        {
            get
            {
                return this.title;
            }
            set
            {
                this.title = value;
                this.OnPropertyChanged("Title");
            }
        }
        private string description;
        public string Description
        {
            get
            {
                return this.description;
            }
            set
            {
                this.description = value;
                this.OnPropertyChanged("Description");
            }
        }
        public void ExecuteInsert()
        {
            this.Notifier.Insert(this.Title, this.Description);
            this.Title = string.Empty;
            this.Description = string.Empty;
        }
        private void LoadMessage(DataTable dt)
        {
            
            this.UIDispatcher.BeginInvoke((Action)delegate()
            {
                if (dt != null)
                {
                    this.Messages.Clear();

                    foreach (DataRow drow in dt.Rows)
                    {
                        Message msg = new Message
                        {
                            Id = Convert.ToString(drow["MID"]),
                            Title = drow["MsgString"] as string,
                            Description = drow["MsgDesc"] as string
                        };
                        this.Messages.Add(msg);
                    }
                }
            });
        }
        void notifier_NewMessage(object sender, SqlNotificationEventArgs e)
        {
            this.LoadMessage(this.Notifier.RegisterDependency());
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        private void OnPropertyChanged(string propertyName)
        {
            if (this.PropertyChanged != null)
                this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
President
India India
Did you like his post?

Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.

Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook

Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.

Working as a VP product of APPSeCONNECT, an integration platform of future, he does all sort of innovation around the product.

Have any problem? Write to him in his Forum.

You can also mail him directly to abhi2434@yahoo.com

Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com

Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing

Comments and Discussions