Receive Notifications on Record Change with SQLTableDependency

SqlTableDependency is a C# class used to receive notifications containing the modified record values when the content of a specified database table change.

Step1: Create a table name emp in your database.

CREATE TABLE [dbo].[Emp](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [FirstName] [nvarchar](50) NOT NULL,

       [LastName] [nvarchar](50) NOT NULL

) ON [PRIMARY]

------------------------------------------------
Step 2: Create one Customer class.

class Customer

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }

}

Step 3: Add SqlTableDependency page in your project.

<packages>

  <package id="SqlTableDependency" version="5.3.0.0" targetFramework="net46" />

</packages>

Step 4: Copy and pase following code to your program class.

-----------------------------------------------

using System;

using TableDependency;

using TableDependency.SqlClient;

using TableDependency.Enums;

using TableDependency.EventArgs;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections.Generic;

namespace ConsoleSqlTableDepnedency

{

    class Program

    {

        static string  _con = ConfigurationManager.ConnectionStrings["connectionString"].ToString();

        static void Main(string[] args)

        {

            var mapper = new ModelToTableMapper<Customer>();

            mapper.AddMapping(c => c.Id, "Id");

            mapper.AddMapping(c => c.FirstName ,"FirstName");

            using (var dep = new SqlTableDependency<Customer>(_con, "Emp", mapper))

            {

                dep.OnChanged += Changed;

                dep.Start();

             

                Console.ReadKey();

                dep.Stop();

            }

        }

        private static void Changed(object sender, RecordChangedEventArgs<Customer> e)

        {

            if (e.ChangeType != ChangeType.None)

            {

              

                Console.WriteLine("\n================Events=============================\n");              

                var changedEntity = e.Entity;

                Console.WriteLine("DML operation: " + e.ChangeType);

                Console.WriteLine("ID: " + changedEntity.Id);

                Console.WriteLine("Name: " + changedEntity.FirstName);             

                Console.WriteLine("\n===================Result==========================\n");     

                List<Customer> lst = GetAllStocks();

                foreach (var item in lst)

                {

                    Console.WriteLine("----------------------------------------------\n");

                    Console.WriteLine("Id: " + item.Id);

                    Console.WriteLine("First Name: " + item.FirstName);

                    Console.WriteLine("Last Name: " + item.LastName);

                  

                }

                Console.WriteLine("\n Press a key to exit");

            }

        }

        public static List<Customer> GetAllStocks()

        {

            List<Customer> lstCustomer = new List<Customer>();

            var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

            using (var sqlConnection = new SqlConnection(connectionString))

            {

                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())

                {

                    sqlCommand.CommandText = "SELECT * FROM Emp";

                    using (var sqlDataReader = sqlCommand.ExecuteReader())

                    {

                        while (sqlDataReader.Read())

                        {

                            var Id = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("Id"));

                            var name = sqlDataReader.GetString(sqlDataReader.GetOrdinal("FirstName"));

                            var Surname = sqlDataReader.GetString(sqlDataReader.GetOrdinal("LastName"));

                            lstCustomer.Add(new Customer { Id = Id, FirstName = name, LastName = Surname });

                        }

                    }

                }

            }

            return lstCustomer;

        }      

    }

}

---------------------------------------------------------------
Step 5: Insert,Update and Delete emp table by following query and you will see the changes on your output window.

INSERT INTO Emp ([FirstName], [LastName])

VALUES ('Ashok', 'Kumar')

go

DELETE FROM Emp WHERE ID =3 

go

UPDATE Emp SET [FirstName] = 'xyz' WHERE ID = 4

------------------------------------------------