SQL Dependency using Entity Framework in WPF

• Download source code (RAR)

Introduction

This project will help you understand how to fetch data in WPF without refreshing the UI content and notify as well.

Using the Code

The first step is to create person class:

CREATE TABLE Persons
(
    Id int NOT NULL PRIMARY KEY,
    FirstName varchar(255) NOT NULL,
    LastName varchar(255) NOT NULL
); 

Next, enable Service Broker for the database using the following command:

ALTER DATABASE DatabaseName SET ENABLE_BROKER

Then, prepare a class which represents the table from the database:

public class Person
{
        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }
}

After that, create an object instance of SqlTableDependency class passing table name and connection string as parameters. Afterwards, subscribe a method to the change event and invoke Start method to begin receiving table change notifications:

public void WatchTable()
    {
         var connectionString = ConfigurationManager.ConnectionStrings
                                     ["DefaultConnString"].ConnectionString;
         var tableName = "[dbo].[Persons]";
         var tableDependency = new SqlTableDependency<Person>(connectionString, tableName);

         tableDependency.OnChanged += OnNotificationReceived;
         tableDependency.Start();
    }

private void OnNotificationReceived(object sender, 
                  TableDependency.EventArgs.RecordChangedEventArgs<Person> e)
    {
        switch (e.ChangeType)
        {
           case ChangeType.Delete:
                Console.WriteLine("{0} {1} has been deleted", e.Entity.FirstName, e.Entity.LastName);
                break;

           case ChangeType.Update:
                Console.WriteLine("{0} {1} has been updated", e.Entity.FirstName, e.Entity.LastName);
                break;

           case ChangeType.Insert:
                Console.WriteLine("{0} {1} has been inserted", e.Entity.FirstName, e.Entity.LastName);
                break;
        }
    }