SqlDependency提供了这样一种机制,当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的。

首先要对数据库进行配置,配置命令如下:

1.启用Service Broker并查看是否启用成功,is_broker_enabled为1则表明启用成功:

ALTER DATABASE SqlDepTest SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SqlDepTest SET ENABLE_BROKER;
SELECT is_broker_enabled FROM sys.databases WHERE name = 'SqlDepTest'

2.开启帐号订阅权限,由于sa帐号不支持权限设置,所以需要建立单独的帐号。

use master

GRANT CREATE PROCEDURE TO sqldep
GRANT CREATE QUEUE TO sqldep
GRANT CREATE SERVICE TO sqldep

use master
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sqldep
exec sp_helprotect NULL, sqldep

C#示例代码如下:

    1. private const string SQL_CONNECTIONSETTINGS = "";
    2. static void Main(string[] args)
    3. {
    4. //传入连接字符串,启动基于数据库的监听
    5. SqlDependency.Start(SQL_CONNECTIONSETTINGS);
    6. HandleMessage();
    7. Console.Read();
    8. }
    9. /// <summary>
    10. /// 触发处理消息机制
    11. /// </summary>
    12. private static void HandleMessage()
    13. {
    14. using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))
    15. {
    16. //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
    17. using (SqlCommand command = new SqlCommand("SELECT [MessageID],[Messages],[CreateTime] FROM [dbo].[Messages]", connection))
    18. {
    19. int messageID = 0;
    20. command.CommandType = CommandType.Text;
    21. connection.Open();
    22. command.Notification = null;
    23. SqlDependency dependency = new SqlDependency(command);
    24. dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);
    25. SqlDataReader sdr = command.ExecuteReader();
    26. while (sdr.Read())
    27. {
    28. int.TryParse(sdr["MessageID"].ToString(), out messageID);
    29. ProcessMessage(messageID);
    30. Console.WriteLine("MessageID:{0}\tMessages:{1}\tCreateTime:\t{2}", sdr["MessageID"].ToString(), sdr["Messages"].ToString(), sdr["CreateTime"].ToString());
    31. CompleteProcessMessage(messageID, 2);
    32. }
    33. sdr.Close();
    34. }
    35. }
    36. }
    37. /// <summary>
    38. /// SQL消息触发事件
    39. /// </summary>
    40. /// <param name="sender"></param>
    41. /// <param name="e"></param>
    42. private static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    43. {
    44. SqlDependency dependency = sender as SqlDependency;
    45. dependency.OnChange -= Dependency_OnChange;
    46. HandleMessage();
    47. }
    48. /// <summary>
    49. /// 处理消息
    50. /// </summary>
    51. /// <param name="messageID">消息编号</param>
    52. private static void ProcessMessage(int messageID)
    53. {
    54. using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))
    55. {
    56. string sql = string.Format(@"INSERT INTO [dbo].[MessagesComplete]
    57. SELECT [MessageID],[Messages],1,[CreateTime],GETDATE()
    58. FROM [dbo].[Messages] WHERE [MessageID] = {0}
    59. DELETE FROM [dbo].[Messages] WHERE [MessageID] = {0}", messageID);
    60. using (SqlCommand command = new SqlCommand(sql, connection))
    61. {
    62. command.CommandType = CommandType.Text;
    63. connection.Open();
    64. command.ExecuteNonQuery();
    65. command.Dispose();
    66. }
    67. }
    68. }
    69. /// <summary>
    70. /// 消息处理完成,更新消息处理状态
    71. /// </summary>
    72. /// <param name="messageID">消息编号</param>
    73. /// <param name="status">状态:1.处理中,2.处理完成,-1.处理失败</param>
    74. private static void CompleteProcessMessage(int messageID, int status)
    75. {
    76. using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))
    77. {
    78. string sql = string.Format("UPDATE [dbo].[MessagesComplete] SET [Status] = {1} WHERE [MessageID] = {0}", messageID, status);
    79. using (SqlCommand command = new SqlCommand(sql, connection))
    80. {
    81. command.CommandType = CommandType.Text;
    82. connection.Open();
    83. command.ExecuteNonQuery();
    84. command.Dispose();
    85. }
    86. }
    87. }
05-11 19:57
查看更多