The sqlDependency class in the System.Data.SqlClient namespace achieves this function: when monitored data base SqlDependency automatically triggers dependency_OnChange() event to notify the application when the data in SqlDependency changes, so that the system can update the data (or cache) automatically.

The following describes the implementation method and the problems that may be encountered in it.

First, create a database and create a table in it, as shown in the following figure:

The contents of the table are as follows:

Next, a C# console application is built in VS2012 to monitor the contents of tables. When the data of tables changes, the console will output changes in real time. The console application is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;


namespace SqlDependencyDemo
{
    class Program
    {
        private static string _connStr;
        static void Main(string[] args)
        {
            _connStr = "Data Source =(local);Database = DBforStudy; UID = sa; Pwd = sa"; 
            SqlDependency.Start(_connStr);//Input connection string to start database-based listening  
            UpdateGrid();

            Console.Read();
        }

        private static void UpdateGrid()
        {
            using (SqlConnection connection = new SqlConnection(_connStr))
            {
                //Dependency is based on a table, and the query statement can only be a simple query statement, not with top or *, and the owner must be specified, which is similar to [dbo]. []  
                using (SqlCommand command = new SqlCommand("select USERS,PASSWORD From dbo.T_USERS", connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    SqlDataReader sdr = command.ExecuteReader();
                    Console.WriteLine();
                    while (sdr.Read())
                    {
                        Console.WriteLine("USERS:{0}\tPASSWORD:{1}\t", sdr["USERS"].ToString(), sdr["PASSWORD"].ToString());
                    }
                    sdr.Close();
                }
            }
        }

        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            UpdateGrid();
        }  
    }
}

Running the program may throw errors in the process:

"SQL Server Service Broker for the current database is not enabled, so query notifications are not supported. If you want to use notifications, enable Service Broker for this database

Create a new query in the database: Enter the following code:

- The syntax is: SELECT is_broker_enabled FROM sys. databases WHERE name ='DATABSE_NAME'-- DATABASE_NAME is the database name here DB for Study

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBforStudy'

The result of is_broker_enabled de returned at this time is 0,

Don't worry about this error because the database didn't start Service Broker. The solution is simple. The steps are as follows:

In the new query above, simply execute the following sentence:

use DBforStudy
go
ALTER DATABASE DBforStudy SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DBforStudy  SET ENABLE_BROKER;

At this point, run again.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBforStudy'

The result returned becomes 1

Then run the C# console application again. The effect is as follows:

At this point, do not close the console application, write data to the database table, such as I added two rows of data 0000,0000; 1111,1111;

It can be seen that every time data is added, the console outputs the newly added content accordingly.