DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Receive Notifications when a Table Record Changes with C#

Receive Notifications when a Table Record Changes with C#

Christian Del Bianco user avatar by
Christian Del Bianco
·
Sep. 16, 15 · Tutorial
Like (5)
Save
Tweet
Share
137.13K Views

Join the DZone community and get the full member experience.

Join For Free

The SQLDependency object represents a query notification dependency between an application and an instance of SQL Server. An application can create a SqlDependency object and register to receive notifications via the OnChangeEventHandler event handler every time a record is changed.

However, what it cannot do, is returning the values for the inserted, modified or deleted record.

So, supposing our application has a cache containing record monitored from SqlDependency, for every notification, in order to refresh the cache we have to execute again our select.

SqlTableDependency

SqlTableDependency is a generic C# component used to receive events containing the values for the modified, inserted or delete record. So, supposing our application has a cache filled with data got from a specific table, when its content change, we will receive an event containing a C# object with all property filled with column table values.

How it Works

When instantiated, SqlTableDependency create for us, a series of database objects, used to monitor a predefined table. The main object are:

  1. Queue: containg a message with the modified record values.

  2. Service Broker: insert the message in the queue.

  3. Table Trigger: for each insert, update or delete operation, prepare the an XML message and use the Service Broker to insert it in the queue.

Watch Dog Time Out

SqlTableDependency implement the IDisposable interface, in order to remove the database object created. Infact it is a good practice is to wrap SqlTableDependency within a using statement or, alternatively, call the Stop() method once we do not need any more notifications. So, when the application will not disconnect abruptly, this approach is enough to remove the SqlTableDependency infrastructure (Trigger, Service Broker service, Queue).

However, when the application exits abruptly – that is not calling the Stop() method or not implementing the using statement - we need a way for cleaning up the SqlTableDependency infrastructure. The Start() method, has watchDogTimeOut optional parameter used to remove all the database objects. Its default value is 180 seconds: after this amount of time, if there are no listeners waiting for notifications, the SqlTableDependency infrastructure will be removed.


How to Use

In this example I created a very simple WPF application with a grid, simulating some stocks value.

Let's start assuming the following database table:

CREATE TABLE [dbo].[Stocks](
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[Price] [decimal](18, 0) NULL)
GO
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'MCD', N'McDonald Corp', CAST(333 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'NKE', N'Nike Inc', CAST(240 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'DIS', N'Walt Disney Co', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'UTX', N'United Technologies Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'MSFT', N'Microsoft Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'PFE', N'Pfizer Inc', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'INTC', N'Intel Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'KO', N'Coca Cola Co', CAST(130 AS Decimal(18, 0)))
GO

This table is constantly updated with new stock values. We want keep update our grid without use of any polling system. So, what we need is a notification coming from the database every time a record in the table has been changed. 

We install the SqlTableDependency package from visual studio using the following command:

PM> Install-Package SqlTableDependency

Now we define a C# model mapping the interested table columns.

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

As you can see, model's properties name can have a different table column name. In this case, we need a mapper to correclty bind the values:

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

Finally we create our SqlTableDependency:

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

Below the complete code:

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;
    }
}

As you can see from the code, only an initial select is performed to retrieve and populate the grid. Subsequent updates are done using the SqlTableDependency event handler: its receives a model object containing info about the record changed that we use to update our cache.

Database

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Load Balancing Pattern
  • The 12 Biggest Android App Development Trends in 2023
  • Tech Layoffs [Comic]
  • Top 5 PHP REST API Frameworks

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: