Tracking external changes to a database with LINQ-to-SQL

Is there a way to get SQL Server 2005 to call back to a connected application, such that the connected application will know when a record in a table has had a field modified by another application using the same database?

A simple example would be two instances of the same application connecting to the same table in the same database. When one instance of the application makes a change to a table, the other instance would get a notification that something has changed and be able to query the database for the change.

UPDATE

Thanks so much for the help so far. I would have never even known to look for the SqlDependency class. I've followed the instruction on this page http://msdn.microsoft.com/en-us/a52dhwx7.aspx in creating the SqlDependency test demo. However, I wasn't able to get that to work. I never see the OnChange event get called.

I've also attempted to modify my own application using the instructions as a guide with no luck. I've included the code from my own application below. Basically, the Position table has a PositionID field along with a LocationX and LocationY field. I've written another application that allows me to update the LocationX field of a given row.

What am I missing? Why won't the database changes trigger my even handler?开发者_高级运维

UPDATE #2

Also note that I am using a hard coded SQL string for my command. I would prefer not to and use the commented out LINQ statement instead. Is it considered OK to use LINQ in this way to generate the SQL string that will be used to build the command?

UPDATE #3

So I managed to figure out what was wrong with my code below. Apparently you have to execute the command once so there will be a cache of data, or else the server doesn't know when to notify you? I added in a line to do a DataAdapter.Fill() with my SqlCommand and the event now seems to fire when expected.

Which brings me to my next problem. The SqlDependency.OnChange event only lets you know that something has changed. How can I figure out from my old DataSet and the new DataSet what the row-by-row changes are?

I could of course, read the entire query again and update all of my data structures, but that seems excessive.

I can call the DataContext.Refresh() and have it do all the updates to my data structures, but that doesn't seem to trigger any of the DataContext generated OnChanging() events. It seems that Refresh() actually tears down all my structures and creates new ones. So I can never figure out what has changed.

Does anyone have any recommendations?

public partial class MainForm : Form
  {
    private ArpPhase2DbContextDataContext db = null;
    private SqlConnection connection = null;
    private SqlCommand command = null;

    public MainForm()
    {
      InitializeComponent();
    }

    private void MainForm_Load(object sender, EventArgs e)
    {
      this.canRequestNotifications();
      this.db = ArpPhase2DbContextDataContext.Instance;
      this.setupSqlDependency();
    }

    private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
    {
      SqlDependency.Stop(this.db.Connection.ConnectionString);

      if (this.connection != null)
      {
        this.connection.Close();
      }

      this.db.SubmitChanges();
    }

    private bool canRequestNotifications()
    {
      try
      {
        SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
        perm.Demand();

        return true;
      }
      catch
      {
        return false;
      }
    }

    private void setupSqlDependency()
    {
      // Remove any existing dependency connection, then create a new one.
      SqlDependency.Stop(this.db.Connection.ConnectionString);
      SqlDependency.Start(this.db.Connection.ConnectionString);

      if (this.connection == null)
      {
        this.connection = new SqlConnection(this.db.Connection.ConnectionString);
      }

      if (this.command == null)
      {
        var sql = (from position in this.db.Positions
                   select position);

        //string commandString = sql.ToString();
        string commandString = "SELECT * FROM Positions;";
        this.command = new SqlCommand(commandString, connection);
      }

      this.getData();
    }

    private void getData()
    {
      // Make sure the command object does not already have
      // a notification object associated with it.
      this.command.Notification = null;

      // Create and bind the SqlDependency object
      // to the command object.
      SqlDependency dependency = new SqlDependency(this.command);
      dependency.OnChange += new OnChangeEventHandler(this.dependency_OnChange);
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
      // This event will occur on a thread pool thread.
      // Updating the UI from a worker thread is not permitted.
      // The following code checks to see if it is safe to
      // update the UI.
      ISynchronizeInvoke i = (ISynchronizeInvoke)this;

      // If InvokeRequired returns True, the code
      // is executing on a worker thread.
      if (i.InvokeRequired)
      {
        // Create a delegate to perform the thread switch.
        OnChangeEventHandler del = new OnChangeEventHandler(this.dependency_OnChange);

        object[] args = { sender, e };

        // Marshal the data from the worker thread
        // to the UI thread.
        i.BeginInvoke(del, args);

        return;
      }

      // Remove the handler, since it is only good
      // for a single notification.
      SqlDependency dependency = (SqlDependency)sender;

      dependency.OnChange -= this.dependency_OnChange;

      // Add information from the event arguments to the list box
      // for debugging purposes only.
      Console.WriteLine("Info: {0}, Source: {1}, Type: {2}", e.Info.ToString(),
        e.Source.ToString(), e.Type.ToString());

      // Rebind the dependency.
      this.setupSqlDependency();
    }
  }

SQL Server can do this with Query Notifications. There's nothing built into L2S to support this, but also nothing to stop you from using it outside of L2S in the same app.

Query Notifications uses the indexed view technology to detect data changes and notify subscribed queries when the result set has possibly changed. This is the technology that powers the ASP SqlCacheDependency for cache invalidation. You can read more into how it works at The Mysterious Notification.

In .Net Framework the most comonly used component that leverages Query Notifications is SqlDependency. There are various samples on how to integrate linq2sql with SqlDependency, like linqtosqlcache.

You should not use this technology to watch for data that changes frequently but solely for catalog reference data that is worth caching. The cost of setting up and delivering the notification is significant.

Why do you want to do that?

Linq-to-SQL was dead before you began using it.

Now they push EF, WCF-DS etc. (who knows when they will kill them too).

Even query notifications are not a safe bet anymore (as they are so fragile if you have an app that is going to last more than a few years).