[C#] 監聽資料庫更新狀態 (SqlDependency)

如何使用.Net SqlDependency 來監看資料庫是否有變化

更新日期2017-01-12

在開發專案上會遇到要監看資料庫的數值是否有異動，來作為觸發條件等等的需求

這邊重新整理了一下最近使用上的心得，並把程式馬整理得更清楚

再使用.Net SqlDependency 這個函式有幾個步驟和注意事項

1.SQL Server資料庫，必須開啟相關設定

2.在C#、VB程式裡面下的T-SQL字串有相關的限制

這兩點相當重要，接下來在使用上如果發現SQL資料更新後

並沒有觸發任何程式的監聽事件，最有可以是這兩點有問題

其中T-SQL的語法更是要特別注意

下面為程式範例供參考

1.建立一個範例資料庫

這邊建立一個 People的測試資料表

CREATE TABLE [dbo].[People](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [age] [nchar](10) NOT NULL,
 CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2.修改SQL設定

   打開 SQL Management 執行指令，[<dbname>]為你的資料表名稱

alter database [<dbname>] set enable_broker with rollback immediate;

   我這邊資料庫名稱是取名為 TestOnlt 指令就會長像這樣

alter database TestOnly set enable_broker with rollback immediate;

3.接著就可以開始寫程式了

    那麼接下來程式的部分其實很簡單，主要分成下面幾的步驟
    那麼就直接來看成程式碼吧.

    A.建立資料庫連線，宣告並開啟 SqlDependency服務

    B.建立 SqlDependency OnChangeEventHandler
    C.處理去觸發後事件



 
    A.建立資料庫連線

    在你要開始監聽的事件下，加入下面程式碼
    我自己在測試時候是直接寫在 FormLoad 或是 ButtonClick)

string ConnectionString = "Data Source=" + txt_dbSource.Text + ";Initial Catalog=" + txt_dbDatabase.Text + ";User ID=" + txt_dbUser.Text + ";Password=" + txt_dbPassword.Text;

//設定好SQL連接字串，開啟
SqlDependency.Start(ConnectionString);

//建立SqlDependency OnChangeEventHandler
SqlDependencyWatch();

//先刷新一次DatagridView
RefreshTable();

    B.建立 SqlDependency OnChangeEventHandler

    SqlDependencyWatch()這個函式就是SqlDependency 主要監聽的部分
    那麼就一樣看程式碼

 private void SqlDependencyWatch() {
            //這邊用的查詢欄位不能式PK，資料表也必須是完整的像dbo.TableName
            string sSQL = "select name,age from [dbo].[People]";
            using (SqlConnection connection = new SqlConnection(ConnectionString)) {
                using (SqlCommand command = new SqlCommand(sSQL, connection)) {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    //這間加入監聽事件SQLTableOnChange
                    dependency.OnChange += new OnChangeEventHandler(SQLTableOnChange);
                    SqlDataReader sdr = command.ExecuteReader();
                }
            }
        }

    接著在SQLTableOnchange這間Function裡面，讓他再回頭執行監聽動作
    因為SqlDependencyWatch每次監聽到變化後，就會釋放掉

 void SQLTableOnChange(object sender, SqlNotificationEventArgs e) {
        //觸發後再開啟一次監聽事件    
        SqlDependencyWatch();
        //執行我自己要執行的邏輯處理
        RefreshTable();
}

 

    那麼RefreshTable()這個函式就是去做你需要的邏輯處理
    我自己這邊就是刷新DatagridView而已

private void RefreshTable() {
    string sSQL22 = "select * from People";
    DataTable datatable = new DataTable();
    using (SqlConnection connection = new SqlConnection(ConnectionString)) {
        connection.Open();
        using (SqlCommand cmd = new SqlCommand(sSQL22, connection)) {
            using (SqlDataAdapter dr = new SqlDataAdapter(sSQL22, connection)) {
                dr.Fill(datatable);
                //這邊要注意，因為SqlDependency是屬於另外個執行緒
                //所以要使用Invoke來做UI的更新
                this.Invoke((EventHandler)(delegate { 
                    dataGridView1.DataSource = datatable; 
                }));
           }
        }
    }
}

    我相信看到這邊一定很多人會有疑問說，我在Onchange事件又在執行自己一次
    當然會刷新阿，可是這樣不就等於無窮迴圈

    如果做出來的結果DatagridView一直在刷新(畫面一直閃，卷軸根本沒辦法往下拉)
    那其實程式碼可能寫錯了!!!
    而很大的原因是在T-SQL的語法撰寫方式，導致 SqlDependency 沒辦法去正常監聽

參考資料:
http://fanli7.net/a/bianchengyuyan/csharp/2011/1108/140743.html
http://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency


如果有錯或是有更好的方式也請各位大大留言