遇見零壹魔王

前置作業

對資料庫[右鍵]點選[屬性]

點選[選項]>將Broker設定為True

正片開始

建立一個Console專案來測試

Step1

    開啟NuGet 安裝System.Data.SqlClient

Step2

    寫一個Service來備用

Service.cs

using System.Data.SqlClient;

namespace ListenTable
{
    public static class Service
    {
        public static void SqlWatch()
        {
            string connectionString = "Server=.\\;Initial Catalog=TestDb;Integrated Security=True";
            SqlDependency.Start(connectionString);

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Create a new SqlCommand object.
                using (SqlCommand command = new SqlCommand(
                    "SELECT Id, TestName, Phone, CreateDate FROM dbo.Test",
                    connection))
                {

                    // Create a dependency and associate it with the SqlCommand.
                    SqlDependency dependency = new SqlDependency(command);
                    // Maintain the reference in a class member.

                    // Subscribe to the SqlDependency event.
                    dependency.OnChange += new
                       OnChangeEventHandler(OnDependencyChange);

                    // Execute the command.
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        // Process the DataReader.
                    }
                }
            }
        }
        public static void OnDependencyChange(object sender,
            SqlNotificationEventArgs e)
        {
            // Handle the event (for example, invalidate this cache entry).
            Console.WriteLine("Changed!");

            SqlWatch();
        }
    }
} 

Step3

在Program.cs中執行程式

using ListenTable;

Console.WriteLine("Hello, World!");

Service.SqlWatch();

Console.ReadKey();

完成!

參考資料: https://docs.microsoft.com/zh-tw/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency