Post
Ask Question

Record Table Changes Notification From SQL Server To WPF Client

We are going to establish a communication between a WCF service and a WPF client using the wsDualHttpBinding that allows a Server application take the initiative to start an asynchronous communication with connected client, to notify them about an event.
 
I will not explain how to achieve this, because it is a well-known topic and it is also well explained here. I will focus on how to obtain communication between the database and a WCF server application.
 

Receive notification from database to Server application

 
Let's assume that we must implement a stock change service, reporting real time prices. Also, let’s assume that these prices are stored in a database table feed by an eternal provider, such as a broker from stock market exchange. As soon as this table is updated, we want to notify our WPF clients about the price change. We have seen that it is relatively easy to send notifications from a WCF service to connected WPF clients, but how we can achieve this from DB to WCF service?
 
notification  
 
Ideally, we want for our database to instantly notify our server application about record changes, allowing us to execute a periodical SELECT. But how to monitor SQL Server table changes? Some options are:
  • SQL Server Service Broker
  • .NET SqlNotificationRequest
  • .NET SqlDependency
All of these are work based on a notifications infrastructure. The first two options require us to have a good T-SQL and database knowledge to create database objects as service broker queue to monitor every change done on our records table, and notify us about any record table change. Although the last one does not require us to have this low-level knowledge, it presents a limitation: delivered notification does not report any information to us about which record has been changed, forcing us to perform a further SELECT to refresh the stock price.
 
Is it possible to receive record table change notifications containing modified, deleted or inserted records to avoid another SELECT to maintain updates to  our cache?
 

Monitor table changes with SqlTableDependency

 
If we want get an alert about any table changes without paying attention to the underlying SQL Server infrastructure, then SqlTableDependency's record table change notifications will do that for us. Using notifications, an application can detect record changes saving us from having to continuously re-query the database to get new values.
 
SqlTableDependency record change audit provides the low-level implementation to receive database notifications creating SQL Server trigger, queue and service broker that immediately notifies us when any record table changes happen.
 
columns 
 

Push table change notifications using WCF Publish-Subscribe pattern

 
Let's assume that we have a table that is continuously updated with stock value from an external thread. We want our WPF application to be notified every time a stock price is updated without periodically checking the stocks table.
 
stock 
 
To achieve this, we need a service application that will take care of creating an SqlTableDependency object and for every change notification received, forward this new stock price to all interested WPF client applications.
 
For this, we are going to use a WCF service implementing the Publish-Subscribe pattern. This service will receive database notifications on any stock price change and in turn will notify subscribed WCF client applications.
 

Server application

 
For simplicity, we create a console application acting as WCF host. In this application, we create a C# model that will be filled by notification with the new table value.
  1. public class Stock  
  2. {  
  3.     public decimal Price { get; set; }  
  4.     public string Symbol { get; set; }  
  5.     public string Name { get; set; }  
  6. }  
After that, we create the WCF service interfaces.
  1. public interface IPriceChangeCallBack  
  2. {  
  3.     [OperationContract]  
  4.     void PriceChange(string code, string name, decimal price);  
  5. }  
  6.   
  7. [ServiceContract(CallbackContract = typeof(IPriceChangeCallBack))]  
  8. public interface IPriceTicker  
  9. {  
  10.     [OperationContract]  
  11.     void Subscribe();  
  12.   
  13.     [OperationContract]  
  14.     void Unsubscribe();  
  15.   
  16.     [OperationContract]  
  17.     IList<Stock> GetAllStocks();  
  18.   
  19.     [OperationContract]  
  20.     void PublishPriceChange(string item, string name, decimal price);  
  21. }  
Now, we install SqlTableDependency nuget package in our server project.
 
PM> Install-Package SqlTableDependency
 
We now implement the WCF service. It creates an instance of SqlTableDependency pointing to the Stocks table: every record change on this table triggers a database notification that is cached from OnChanged event handler. Here, we simply push the new stock price to all connected clients.
  1. [ServiceBehavior(  
  2.         InstanceContextMode = InstanceContextMode.Single,   
  3.         ConcurrencyMode = ConcurrencyMode.Single)]  
  4. public class PriceTicker : IPriceTicker, IDisposable  
  5. {  
  6.     #region Instance variables  
  7.   
  8.     private readonly List<IPriceChangeCallBack> _callbackList =   
  9.             new List<IPriceChangeCallBack>();  
  10.     private readonly string _connectionString;  
  11.     private readonly SqlTableDependency<Stock> _sqlTableDependency;  
  12.  
  13.     #endregion  
  14.  
  15.     #region Constructors  
  16.   
  17.     public PriceTicker()  
  18.     {  
  19.         _connectionString = ConfigurationManager  
  20.                     .ConnectionStrings["connectionString"]  
  21.                     .ConnectionString;  
  22.   
  23.         _sqlTableDependency = new SqlTableDependency<Stock>(  
  24.                     _connectionString,   
  25.                     "Stocks");  
  26.   
  27.         _sqlTableDependency.OnChanged += TableDependency_Changed;  
  28.         _sqlTableDependency.OnError += (sender, args) =>   
  29.                     Console.WriteLine($"Error: {args.Message}");  
  30.         _sqlTableDependency.Start();  
  31.   
  32.         Console.WriteLine(@"Waiting for receiving notifications...");  
  33.     }  
  34.  
  35.     #endregion  
  36.  
  37.     #region SqlTableDependency  
  38.   
  39.     private void TableDependency_Changed(  
  40.         object sender,   
  41.         RecordChangedEventArgs<Stock> e)  
  42.     {  
  43.         Console.WriteLine(Environment.NewLine);  
  44.         Console.WriteLine($"DML: {e.ChangeType}");  
  45.         Console.WriteLine($"Code: {e.Entity.Code}");  
  46.         Console.WriteLine($"Name: {e.Entity.Name}");  
  47.         Console.WriteLine($"Price: {e.Entity.Price}");  
  48.   
  49.         this.PublishPriceChange(e.Entity.Code, e.Entity.Name, e.Entity.Price);  
  50.     }  
  51.  
  52.     #endregion  
  53.  
  54.     #region Publish-Subscribe design pattern  
  55.   
  56.     public IList<Stock> GetAllStocks()  
  57.     {  
  58.         var stocks = new List<Stock>();  
  59.   
  60.         using (var sqlConnection = new SqlConnection(_connectionString))  
  61.         {  
  62.             sqlConnection.Open();  
  63.             using (var sqlCommand = sqlConnection.CreateCommand())  
  64.             {  
  65.                 sqlCommand.CommandText = "SELECT * FROM [Stocks]";  
  66.   
  67.                 using (var sqlDataReader = sqlCommand.ExecuteReader())  
  68.                 {  
  69.                     while (sqlDataReader.Read())  
  70.                     {  
  71.                         var code = sqlDataReader  
  72.                                 .GetString(sqlDataReader  
  73.                                 .GetOrdinal("Code"));  
  74.                         var name = sqlDataReader  
  75.                                 .GetString(sqlDataReader  
  76.                                 .GetOrdinal("Name"));  
  77.                         var price = sqlDataReader  
  78.                                 .GetDecimal(sqlDataReader  
  79.                                 .GetOrdinal("Price"));  
  80.   
  81.                         stocks.Add(new Stock {   
  82.                                 Code = code,   
  83.                                 Name = name,   
  84.                                 Price = price });  
  85.                     }  
  86.                 }  
  87.             }  
  88.         }  
  89.   
  90.         return stocks;  
  91.     }  
  92.   
  93.     public void Subscribe()  
  94.     {  
  95.         var registeredUser = OperationContext.  
  96.                     Current  
  97.                     .GetCallbackChannel<IPriceChangeCallBack>();  
  98.         if (!_callbackList.Contains(registeredUser))  
  99.         {  
  100.             _callbackList.Add(registeredUser);  
  101.         }  
  102.     }  
  103.   
  104.     public void Unsubscribe()  
  105.     {  
  106.         var registeredUser = OperationContext  
  107.                     .Current  
  108.                     .GetCallbackChannel<IPriceChangeCallBack>();  
  109.         if (_callbackList.Contains(registeredUser))  
  110.         {  
  111.             _callbackList.Remove(registeredUser);  
  112.         }  
  113.     }  
  114.   
  115.     public void PublishPriceChange(string code, string name, decimal price)  
  116.     {  
  117.         _callbackList.ForEach(delegate (IPriceChangeCallBack callback) {   
  118.                     callback.PriceChange(code, name, price);   
  119.         });  
  120.     }  
  121.  
  122.     #endregion  
  123.  
  124.     #region IDisposable  
  125.   
  126.     public void Dispose()  
  127.     {  
  128.         _sqlTableDependency.Stop();  
  129.     }  
  130.  
  131.     #endregion  
  132. }  
Let's set the endpoint binding.
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <configuration>  
  3.   
  4.   <connectionStrings>  
  5.     <add name="connectionString" connectionString="data source=.;initial catalog=TableDependencyDB;integrated security=False; User ID=Test_User;Password=Casadolcecasa1" providerName="System.Data.SqlClient"/>  
  6.   </connectionStrings>  
  7.   
  8.   <startup>  
  9.     <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6"/>  
  10.   </startup>  
  11.   
  12.   <system.serviceModel>  
  13.     <behaviors>  
  14.       <serviceBehaviors>  
  15.         <behavior>  
  16.           <serviceMetadata httpGetEnabled="true"/>  
  17.           <serviceDebug includeExceptionDetailInFaults="true"/>  
  18.         </behavior>  
  19.       </serviceBehaviors>  
  20.     </behaviors>  
  21.   
  22.     <services>  
  23.       <service name="ConsoleApplicationServer.PriceChangeService.PriceTicker">  
  24.         <endpoint address="get" binding="wsDualHttpBinding" contract="ConsoleApplicationServer.PriceChangeContracts.IPriceTicker">  
  25.           <identity>  
  26.             <dns value="localhost" />  
  27.           </identity>  
  28.         </endpoint>  
  29.         <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />  
  30.         <host>  
  31.           <baseAddresses>  
  32.             <add baseAddress="http://localhost:8090/PriceTickerService/" />  
  33.           </baseAddresses>  
  34.         </host>  
  35.       </service>  
  36.     </services>  
  37.   </system.serviceModel>  
  38.   
  39. </configuration>  
And to conclude, we code the hosting part.
  1. class Program  
  2. {  
  3.     static void Main(string[] args)  
  4.     {  
  5.         var host = new ServiceHost(typeof(PriceTicker));  
  6.         host.Open();  
  7.         Console.WriteLine($"Service started at {host.Description.Endpoints[0].Address}");  
  8.         Console.WriteLine("Press key to stop the service.");  
  9.         Console.ReadLine();  
  10.         host.Close();  
  11.     }  
  12. }  

WPF Client application

 
As a first step, we need to create a WCF proxy to execute the price change subscription, and of course, to read the initial list of stocks. This operation can be done from Visual Studio - Run the server application and then, by adding a service reference typing the WCF URL endpoint.
 
 
 
We prepare the WCF client as follows.
  1. <Window x:Class="DataGridSample.Window1"  
  2.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4.         Title="DataGrid Sample" Height="350" Width="776">  
  5.     <Grid>  
  6.         <DataGrid Height="302" Width="750" HorizontalAlignment="Left" Margin="10,10,0,0"   
  7.           Name="McDataGrid" VerticalAlignment="Top" RowHeight="30" ColumnWidth="auto"   
  8.                   ColumnHeaderHeight="30" HeadersVisibility="Column" AlternatingRowBackground="Silver"  
  9.                   BorderBrush="Gray" BorderThickness="1" AutoGenerateColumns="False">  
  10.   
  11.             <DataGrid.Columns>  
  12.                 <DataGridTextColumn Header="Code" Binding="{Binding Code}" />  
  13.                 <DataGridTextColumn Header="Name" Binding="{Binding Name}" />  
  14.                 <DataGridTextColumn Header="Price" Binding="{Binding Price}" />  
  15.             </DataGrid.Columns>  
  16.   
  17.         </DataGrid>  
  18.     </Grid>  
  19. </Window>  
Our client application executes an initial request to WCF, just to fill its Grid. After that, the application subscribes itself as a listener for price change notifications. In this way, every time a stock is updated, a notification containing fresh values is received.
  1. public partial class Window1: Window, IPriceTickerCallback  
  2. {  
  3.     private readonly IList<Stock> _stocks;  
  4.     private readonly PriceTickerClient _proxy;  
  5.   
  6.     public Window1()  
  7.     {  
  8.         this.InitializeComponent();  
  9.          
  10.         var instanceContext = new InstanceContext(this);  
  11.         _proxy = new PriceTickerClient(instanceContext);  
  12.         _proxy.Subscribe();  
  13.   
  14.         _stocks = _proxy.GetAllStocks();  
  15.         this.McDataGrid.ItemsSource = _stocks;  
  16.   
  17.         this.Closing += (sender, args) =>  
  18.         {  
  19.             try  
  20.             {  
  21.                 _proxy?.Unsubscribe();  
  22.             }  
  23.             catch  
  24.             {  
  25.                 // ignored  
  26.             }  
  27.         };  
  28.     }  
  29.   
  30.     public void PriceChange(string code, string name, decimal price)  
  31.     {  
  32.         if (_stocks != null)  
  33.         {  
  34.             var customerIndex = _stocks.IndexOf(_stocks.FirstOrDefault(c => c.Code == code));  
  35.             if (customerIndex >= 0)  
  36.             {  
  37.                 _stocks[customerIndex] = new Stock {Code = code, Name = name, Price = price };  
  38.   
  39.                 this.McDataGrid.Dispatcher.Invoke(DispatcherPriority.Background, new Action(() =>  
  40.                 {  
  41.                     this.McDataGrid.Items.Refresh();  
  42.                 }));  
  43.             }  
  44.         }  
  45.     }  
  46. }  
As the final result, instead of executing a request from client to the web application, and then the web application to the database; we are doing the reverse, i.e., sending a request from database to web application, and in turn from web application to clients.  


Similar Articles