Example with WPF

Simple WPF application with grid auto updated whenever a record change.

Assuming a table containing stock as follow:

CREATE TABLE [Stocks] (
	[Code] [nvarchar](50) NULL,
	[Name] [nvarchar](50) NULL,
	[Price] [decimal](18, 0) NULL
)

This table is continuously update with stock's value from an external thread.

We what our application be notified every time a new value is updated, without polling periodically the Stocks table.

First we create a C# model that will be filled with the interested table value:

public class Stock
{
    public decimal Price { get; set; }
    public string Symbol { get; set; }
    public string Name { get; set; }
}

Our application execute an initial read from Stocks table, just to fill its grid. After that, the application subscribe its self as listener for data changes. In this way, every time a stock is updated, a notification containing fresh value is received without need of executing a new query on Stocks table:

<Window x:Class="DataGridSample.Window1"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="DataGrid Sample" Height="350" Width="776">
    <Grid>
        <DataGrid Height="302" Width="750" HorizontalAlignment="Left" Margin="10,10,0,0" 
          Name="McDataGrid" VerticalAlignment="Top" RowHeight="30" ColumnWidth="auto" 
                  ColumnHeaderHeight="30" HeadersVisibility="Column" AlternatingRowBackground="Silver"
                  BorderBrush="Gray" BorderThickness="1" AutoGenerateColumns="True">
        </DataGrid>
    </Grid>
</Window>

public partial class Window1 : Window
{
    private IList<Stock> _stocks;
    private readonly string _connectionString = 
        "data source=.;initial catalog=TableDependencyDB;integrated security=True";
    private readonly SqlTableDependency<Stock> _dependency;

    public Window1()
    {
        this.InitializeComponent();
        this.McDataGrid.ItemsSource = LoadCollectionData();
        this.Closing += Window1_Closing;

        var mapper = new ModelToTableMapper<Stock>();
        mapper.AddMapping(model => model.Symbol, "Code");

        _dependency = new SqlTableDependency<Stock>(_connectionString, "Stocks", mapper);
        _dependency.OnChanged += _dependency_OnChanged;
        _dependency.OnError += _dependency_OnError;
        _dependency.Start();
    }

    private void Window1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
    {
        _dependency.Stop();
    }

    private void _dependency_OnError(object sender, TableDependency.EventArgs.ErrorEventArgs e)
    {
        throw e.Error;
    }

    private void _dependency_OnChanged(
        object sender, 
        TableDependency.EventArgs.RecordChangedEventArgs<Stock> e)
    {
        if (_stocks != null)
        {
            if (e.ChangeType != ChangeType.None)
            {
                switch (e.ChangeType)
                {
                    case ChangeType.Delete:
                        _stocks.Remove(_stocks.FirstOrDefault(c => c.Symbol == e.Entity.Symbol));
                        break;
                    case ChangeType.Insert:
                        _stocks.Add(e.Entity);
                        break;
                    case ChangeType.Update:
                        var customerIndex = _stocks.IndexOf(
                                _stocks.FirstOrDefault(c => c.Symbol == e.Entity.Symbol));
                        if (customerIndex >= 0) _stocks[customerIndex] = e.Entity;
                        break;
                }

                this.McDataGrid.Dispatcher.Invoke(DispatcherPriority.Background, new Action(() =>
                {
                    this.McDataGrid.Items.Refresh();
                }));
            }
        }
    }

    private IEnumerable<Stock> LoadCollectionData()
    {
        _stocks = new List<Stock>();

        using (var sqlConnection = new SqlConnection(_connectionString))
        {
            sqlConnection.Open();
            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = "SELECT * FROM [Stocks]";

                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        var code = sqlDataReader
                                .GetString(sqlDataReader.GetOrdinal("Code"));
                        var name = sqlDataReader
                                .GetString(sqlDataReader.GetOrdinal("Name"));
                        var price = sqlDataReader
                                .GetDecimal(sqlDataReader.GetOrdinal("Price"));

                        _stocks.Add(new Stock { Symbol = code, Name = name, Price = price });
                    }
                }
            }
        }

        return _stocks;
    }

To test Download it and then follow these steps to run it:
  • Execute the .sql file included in the solution: it creates a database table and insert some records.
  • Run the application.
  • Modify any data in the table to have an immediate grid refresh.

Last edited Sep 29, 2015 at 12:27 PM by delbianco, version 5