Click here to Skip to main content
Click here to Skip to main content

AutoRefresh Entity Framework Data Using SQL Server Service Broker

, 14 Sep 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
How to use SqlDependency to refresh Entity Framework Object Sets automatically on DB changes

Automatically Update Your Application on DB Changes

Starting with SQL Server 2005, Microsoft introduced a new feature called SQL Server Service Broker (SSB) that allows communications (and notifications!) from SQL Server to other places. There is a class in the .NET Framework called SqlDependency that allows to notify the client about database changes. To get this to work, the SqlDependency constructor needs a SqlCommand - and that's a problem when you're using Entity Framework. The SqlCommand objects are hidden deep inside EF's objects.

Some time ago, a great post from Fritz Onion (previously published at http://www.pluralsight-training.net/community/blogs/fritz/archive/2006/06/15/27694.aspx) revealed a "magic cookie" that allows to connect a SqlDependency object to a hidden SqlCommand. This technique has been used in the open source project LinqToCache that maintains a cache using LINQ to SQL.

In this article, I'll introduce a way to use SQL Server Service Broker notifications in order to automatically update an Entity Framework object set.

The AutoRefresh Extension Method

You'll probably be familiar with the Refresh method that allows you to update an object set (a representation of a table) in your object context with fresh data from your database:

this.MyObjectContext.Refresh(RefreshMode.StoreWins, this.MyObjectContext.MyObjectSet);

Using the ServiceBrokerUtility outlined below, it's easy to refresh your object set not only once, but automatically on each update that occurs in the database. All you need is to replace the Refresh method by the new AutoRefresh extension method defined in ServiceBrokerUtility.

this.MyObjectContext.AutoRefresh
	(RefreshMode.StoreWins, this.MyObjectContext.MyObjectSet);

The ServiceBrokerUtility

Here's the code for all that magic (coming out of the cookie):

public static class ServiceBrokerUtility
{
  private static List<string> connectionStrings = new List<string>();
  private const string sqlDependencyCookie = "MS.SqlDependencyCookie";
  private static ObjectContext ctx;
  private static RefreshMode refreshMode;
  private static readonly Dictionary<string, IEnumerable> collections = new Dictionary<string, IEnumerable>();

  static public void AutoRefresh(this ObjectContext ctx, 
                RefreshMode refreshMode, IEnumerable collection)
  {
    var csInEF = ctx.Connection.ConnectionString;
    var csName = csInEF.Replace("name=", "").Trim();
    var csForEF = 
      System.Configuration.ConfigurationManager.ConnectionStrings
		[csName].ConnectionString;
    var newConnectionString = new 
      System.Data.EntityClient.EntityConnectionStringBuilder
			(csForEF).ProviderConnectionString;
    if (!connectionStrings.Contains(newConnectionString))
    {
      connectionStrings.Add(newConnectionString);
      SqlDependency.Start(newConnectionString);
    }
    ServiceBrokerUtility.ctx = ctx;
    ServiceBrokerUtility.refreshMode = refreshMode;
    AutoRefresh(collection);
  }

  static public void AutoRefresh(IEnumerable collection)
  {
   var oldCookie = CallContext.GetData(sqlDependencyCookie);
   try
   {
    var dependency = new SqlDependency();
    collections.Add(dependency.Id, collection);
    CallContext.SetData(sqlDependencyCookie, dependency.Id);
    dependency.OnChange += dependency_OnChange;
    ctx.Refresh(refreshMode, collection);
   }
   finally
   {
    CallContext.SetData(sqlDependencyCookie, oldCookie);
   }
  }

  static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
  {
    if (e.Info == SqlNotificationInfo.Invalid)
    {
      Debug.Print("SqlNotification:  A statement was provided that cannot be notified.");
      return;
    }
    try{
      var id =((SqlDependency)sender).Id;
      IEnumerable collection;
      if (collections.TryGetValue(id, out collection))
      {
        collections.Remove(id);
        AutoRefresh(collection);
        var notifyRefresh = collection as INotifyRefresh;
        if (notifyRefresh != null)
          System.Windows.Application.Current.Dispatcher.BeginInvoke(
            (Action)(notifyRefresh.OnRefresh));
      }
    }
    catch (Exception ex)
    {
      System.Diagnostics.Debug.Print("Error in OnChange: {0}", ex.Message);
    }
  }
}

Almost Ready to Use

Isn't that fancy? Once I'd developed that code, I instantly bound my (now automatically updated) object set to the ItemsSource property of a DataGrid and pressed F5 in Visual Studio in order to compile and run my program. I displayed Visual Studio's Server Explorer, right-clicked on my table, selected "Show Table Data" and thus changed the value of a field in the database.

Tadaa - the field's value changed in my program's grid accordingly! After I'd enjoyed that game some time I tried to add a new record to the database. Disappointingly my grid didn't honor the new record in any way.

The debugger revealed that new records arrived as soon as they were created in my object set, but the DataGrid didn't care. After some research, I found that (for whatever reason) the object sets are lacking any support of the INotifyCollectionChanged interface.

New problems - ok, we need more code.

The AutoRefreshWrapper

This class takes an object set and spits out a typed enumerable with suitable notification support for the controls and of course with ServiceBrokerUtility's autorefresh power.

Using this wrapper class, we can easily implement a property in a view model that we can use to bind to:

public class MyViewModel
{
    public MyViewModel()
    {
        ..
        MyThings = new AutoRefreshWrapper<thing>(
              MyObjectContext.MyObjectSet, RefreshMode.StoreWins);
        ..
    }
 
    public IEnumerable<thing> MyThings { get; private set; }
 
    ..
}

Here is the wrapper class:

public class AutoRefreshWrapper<T> : IEnumerable<T>, INotifyRefresh
    where T : EntityObject
{
    private IEnumerable<T> objectQuery;
    public AutoRefreshWrapper(ObjectQuery<T> objectQuery, RefreshMode refreshMode)
    {
        this.objectQuery = objectQuery;
        objectQuery.Context.AutoRefresh(refreshMode, this);
    }

    public IEnumerator<T> GetEnumerator()
    {
        return objectQuery.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }

    public void OnRefresh()
    {
        try
        {
            if (this.CollectionChanged != null)
                CollectionChanged(this, 
                  new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Reset));
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.Print("Error in OnRefresh: {0}", ex.Message);
        }
    }

    public event NotifyCollectionChangedEventHandler CollectionChanged;
}

This class implements a new interface INotifyRefresh:

public interface INotifyRefresh : INotifyCollectionChanged
{
	void OnRefresh();
}

Finally, that whole solution worked fine for me. But be careful - there are some restrictions regarding the queries that are working with Service Broker. And finally: The "Magic Cookie" is an undocumented feature, thus you should take care.

License

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

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralTHANK YOU! (& Entity Framework 6) Pinmemberqzcreativeit29-Sep-14 23:52 
Questionsort collection PinmemberMember 859429620-Mar-14 1:21 
Questionstored procedure Pinmembercompliedmonkey14-Dec-13 5:30 
Questionasp.net web form - update cache Pinmembermakv20091-Oct-13 23:02 
QuestionHow to use it in winform datagridview Pinmemberbilalsaeed23227-Sep-13 4:24 
QuestionAutoRefresh in MVC Pinmembervishal jodh17-Sep-13 3:51 
QuestionI have a question if someone has time to lend a hand. Pinmemberdpgil12-Feb-13 12:57 
QuestionThere is no refresh event from DB to application! [modified] PinmemberMember 946259327-Sep-12 3:37 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 6:13 
Questioncould be used with LINQ to SQL Pinmembermegcode23-Sep-12 18:33 
BugException PinmemberOmar Gamil15-Sep-12 1:39 
GeneralRe: Exception PinmemberKishore Jangid16-Sep-12 7:53 
NewsSample code for VS 2012 / Entity Framework 5.0 Code First PinmemberHarry von Borstel14-Sep-12 5:59 
Questionn_tier architecture / WCF PinmemberPiedro12346-Sep-12 1:07 
NewsSample code uploaded [modified] PinmemberHarry von Borstel5-Sep-12 5:34 
QuestionDownload code PinmemberMember 79999663-Sep-12 12:56 
AnswerRe: Download code PinmemberHarry von Borstel5-Sep-12 5:23 
GeneralRe: Download code PinmemberMember 79999665-Sep-12 7:35 
Questionmultiple datasets PinmemberMember 831284831-Aug-12 8:03 
I dont see how it would work for multiple datasets. Does it?
AnswerRe: multiple datasets PinmemberHarry von Borstel3-Sep-12 22:38 
GeneralMy vote of 5 PinmemberChristian Amado3-Aug-12 7:57 
GeneralInvalid url PinmemberGSerjo2-Aug-12 7:38 
GeneralRe: Invalid url PinmemberHarry von Borstel2-Aug-12 20:32 
GeneralMy vote of 5 Pinmembermrmiagi010125-Jul-12 23:12 
GeneralOnChange is called endless [modified] Pinmembermrmiagi010125-Jul-12 2:03 
GeneralMy vote of 4 Pinmemberchang3d20-Jun-12 12:27 
QuestionJournal of how I used this... and where I failed... [modified] Pinmemberchang3d18-Jun-12 13:50 
QuestionHow would you modify this code for EF 4.3 using DbContext PinmemberTim Elvidge7-May-12 12:48 
AnswerRe: How would you modify this code for EF 4.3 using DbContext PinmemberHarry von Borstel7-May-12 21:57 
GeneralRe: How would you modify this code for EF 4.3 using DbContext PinmemberTim Elvidge8-May-12 18:55 
GeneralRe: How would you modify this code for EF 4.3 using DbContext PinmemberMember 831284830-Aug-12 11:53 
GeneralRe: How would you modify this code for EF 4.3 using DbContext PinmemberTim Elvidge30-Aug-12 12:29 
GeneralRe: How would you modify this code for EF 4.3 using DbContext PinmemberMember 831284831-Aug-12 8:17 
GeneralMy vote of 2 PinmemberGusteru_2023-Apr-12 21:22 
GeneralRe: My vote of 2 PinmemberHarry von Borstel26-Apr-12 2:21 
GeneralMy vote of 1 Pinmembersangchi.rk26-Mar-12 3:09 
QuestionReg: downloading of original Code. Pinmembersangchi.rk26-Mar-12 2:41 
AnswerRe: Reg: downloading of original Code. PinmemberHarry von Borstel1-Apr-12 5:52 
SuggestionNeed download Pinmembergs.meena9-Aug-11 1:35 
GeneralRe: Need download PinmemberHarry von Borstel9-Aug-11 21:02 
GeneralRe: Need download PinmemberMichael90002-Nov-11 5:22 
AnswerRe: Need download PinmemberHarry von Borstel2-Nov-11 7:36 
GeneralRe: Need download Pinmembersmurancsik20-Feb-12 7:12 
AnswerRe: Need download PinmemberHarry von Borstel5-Sep-12 5:36 
QuestionGreat Pinmemberhsback3-Aug-11 2:06 
AnswerRe: Great PinmemberHarry von Borstel3-Aug-11 3:04 
QuestionMy vote of 5 PinmemberFilip D'haene31-Jul-11 5:30 
AnswerRe: My vote of 5 PinmemberHarry von Borstel31-Jul-11 8:25 
GeneralRe: My vote of 5 PinmemberKamran Abdi3-Dec-11 3:01 
GeneralRe: My vote of 5 PinmemberHarry von Borstel7-Dec-11 1:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.1 | Last Updated 14 Sep 2012
Article Copyright 2011 by Harry von Borstel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid