Click here to Skip to main content
11,503,750 members (61,482 online)
Click here to Skip to main content

AutoRefresh Entity Framework Data Using SQL Server Service Broker

, 14 Sep 2012 CPOL 67.9K 2.4K 81
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

 
QuestionCOMPLIMENT! [modified] Pin
Christian Del Bianco20-Apr-15 6:09
memberChristian Del Bianco20-Apr-15 6:09 
GeneralTHANK YOU! (& Entity Framework 6) Pin
qzcreativeit29-Sep-14 23:52
memberqzcreativeit29-Sep-14 23:52 
Questionsort collection Pin
Member 859429620-Mar-14 1:21
memberMember 859429620-Mar-14 1:21 
Questionstored procedure Pin
compliedmonkey14-Dec-13 5:30
membercompliedmonkey14-Dec-13 5:30 
Questionasp.net web form - update cache Pin
makv20091-Oct-13 23:02
membermakv20091-Oct-13 23:02 
QuestionHow to use it in winform datagridview Pin
bilalsaeed23227-Sep-13 4:24
memberbilalsaeed23227-Sep-13 4:24 
QuestionAutoRefresh in MVC Pin
vishal jodh17-Sep-13 3:51
membervishal jodh17-Sep-13 3:51 
QuestionI have a question if someone has time to lend a hand. Pin
dpgil12-Feb-13 12:57
memberdpgil12-Feb-13 12:57 
QuestionThere is no refresh event from DB to application! [modified] Pin
Member 946259327-Sep-12 3:37
memberMember 946259327-Sep-12 3:37 
Problem solved:

SQL was running with local system account, app was running with my domain account, the local systam had no right to authenticate the domain user before sending the service broker event.

modified 27-Sep-12 10:26am.

GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:13
mvpKanasz Robert24-Sep-12 6:13 
Questioncould be used with LINQ to SQL Pin
megcode23-Sep-12 18:33
membermegcode23-Sep-12 18:33 
BugException Pin
Omar Gamil15-Sep-12 1:39
memberOmar Gamil15-Sep-12 1:39 
GeneralRe: Exception Pin
Kishore Jangid16-Sep-12 7:53
memberKishore Jangid16-Sep-12 7:53 
NewsSample code for VS 2012 / Entity Framework 5.0 Code First Pin
Harry von Borstel14-Sep-12 5:59
memberHarry von Borstel14-Sep-12 5:59 
Questionn_tier architecture / WCF Pin
Piedro12346-Sep-12 1:07
memberPiedro12346-Sep-12 1:07 
NewsSample code uploaded [modified] Pin
Harry von Borstel5-Sep-12 5:34
memberHarry von Borstel5-Sep-12 5:34 
QuestionDownload code Pin
Member 79999663-Sep-12 12:56
memberMember 79999663-Sep-12 12:56 
AnswerRe: Download code Pin
Harry von Borstel5-Sep-12 5:23
memberHarry von Borstel5-Sep-12 5:23 
GeneralRe: Download code Pin
Member 79999665-Sep-12 7:35
memberMember 79999665-Sep-12 7:35 
Questionmultiple datasets Pin
Member 831284831-Aug-12 8:03
memberMember 831284831-Aug-12 8:03 
AnswerRe: multiple datasets Pin
Harry von Borstel3-Sep-12 22:38
memberHarry von Borstel3-Sep-12 22:38 
GeneralMy vote of 5 Pin
Christian Amado3-Aug-12 7:57
memberChristian Amado3-Aug-12 7:57 
GeneralInvalid url Pin
GSerjo2-Aug-12 7:38
memberGSerjo2-Aug-12 7:38 
GeneralRe: Invalid url Pin
Harry von Borstel2-Aug-12 20:32
memberHarry von Borstel2-Aug-12 20:32 
GeneralMy vote of 5 Pin
mrmiagi010125-Jul-12 23:12
membermrmiagi010125-Jul-12 23:12 
GeneralOnChange is called endless [modified] Pin
mrmiagi010125-Jul-12 2:03
membermrmiagi010125-Jul-12 2:03 
GeneralMy vote of 4 Pin
chang3d20-Jun-12 12:27
memberchang3d20-Jun-12 12:27 
QuestionJournal of how I used this... and where I failed... [modified] Pin
chang3d18-Jun-12 13:50
memberchang3d18-Jun-12 13:50 
QuestionHow would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge7-May-12 12:48
memberTim Elvidge7-May-12 12:48 
AnswerRe: How would you modify this code for EF 4.3 using DbContext Pin
Harry von Borstel7-May-12 21:57
memberHarry von Borstel7-May-12 21:57 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge8-May-12 18:55
memberTim Elvidge8-May-12 18:55 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Member 831284830-Aug-12 11:53
memberMember 831284830-Aug-12 11:53 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge30-Aug-12 12:29
memberTim Elvidge30-Aug-12 12:29 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Member 831284831-Aug-12 8:17
memberMember 831284831-Aug-12 8:17 
GeneralMy vote of 2 Pin
Gusteru_2023-Apr-12 21:22
memberGusteru_2023-Apr-12 21:22 
GeneralRe: My vote of 2 Pin
Harry von Borstel26-Apr-12 2:21
memberHarry von Borstel26-Apr-12 2:21 
GeneralMy vote of 1 Pin
sangchi.rk26-Mar-12 3:09
membersangchi.rk26-Mar-12 3:09 
QuestionReg: downloading of original Code. Pin
sangchi.rk26-Mar-12 2:41
membersangchi.rk26-Mar-12 2:41 
AnswerRe: Reg: downloading of original Code. Pin
Harry von Borstel1-Apr-12 5:52
memberHarry von Borstel1-Apr-12 5:52 
SuggestionNeed download Pin
gs.meena9-Aug-11 1:35
membergs.meena9-Aug-11 1:35 
GeneralRe: Need download Pin
Harry von Borstel9-Aug-11 21:02
memberHarry von Borstel9-Aug-11 21:02 
GeneralRe: Need download Pin
Michael90002-Nov-11 5:22
memberMichael90002-Nov-11 5:22 
AnswerRe: Need download Pin
Harry von Borstel2-Nov-11 7:36
memberHarry von Borstel2-Nov-11 7:36 
GeneralRe: Need download Pin
smurancsik20-Feb-12 7:12
membersmurancsik20-Feb-12 7:12 
AnswerRe: Need download Pin
Harry von Borstel5-Sep-12 5:36
memberHarry von Borstel5-Sep-12 5:36 
QuestionGreat Pin
hsback3-Aug-11 2:06
memberhsback3-Aug-11 2:06 
AnswerRe: Great Pin
Harry von Borstel3-Aug-11 3:04
memberHarry von Borstel3-Aug-11 3:04 
QuestionMy vote of 5 Pin
Filip D'haene31-Jul-11 5:30
memberFilip D'haene31-Jul-11 5:30 
AnswerRe: My vote of 5 Pin
Harry von Borstel31-Jul-11 8:25
memberHarry von Borstel31-Jul-11 8:25 
GeneralRe: My vote of 5 Pin
Kamran Abdi3-Dec-11 3:01
memberKamran Abdi3-Dec-11 3:01 

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 | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 14 Sep 2012
Article Copyright 2011 by Harry von Borstel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid