C#_SqlDependency的使用
SqlDependency允许你在数据库中的原始数据更改时接收通知，要接收通知，您需要订阅OnChange事件。
SqlDependency有如下限制：
- SELECT语句中的投影列必须明确声明，并且表名必须由两部分组成。请注意，这意味着该语句中引用的所有表必须位于同一数据库中。
- 该语句不能使用星号（*）或table_name。*语法指定列。
- 该语句不能使用未命名的列或重复的列名。
- 该语句必须引用基表。
- 该语句不能引用具有计算列的表。
- 除非该语句使用GROUP BY表达式，否则SELECT语句中的投影列可能不包含聚合表达式。提供GROUP BY表达式时，选择列表可能包含聚合函数COUNT_BIG（）或SUM（）。但是，不能为可为空的列指定SUM（）。该语句可能未指定HAVING，CUBE或ROLLUP。
- SELECT语句中用作简单表达式的投影列不得出现多次。
- 该声明不能包含PIVOT或UNPIVOT运算符。
- 该语句不能包含UNION，INTERSECT或EXCEPT运算符。
- 该语句不能引用视图。
- 该语句不得包含以下任何内容：DISTINCT，COMPUTE或COMPUTE BY或INTO。
- 该语句不能引用服务器全局变量（@@ variable_name）。
- 该语句不能引用派生表，临时表或表变量。
- 该语句不得引用其他数据库或服务器中的表或视图。
- 该语句不能包含子查询，外部联接或自我联接。
- 该语句不能引用大型对象类型：text，ntext和image。
- 该语句不得使用CONTAINS或FREETEXT全文谓词。
- 该语句不得使用行集函数，包括OPENROWSET和OPENQUERY。
- 该语句不得使用以下任何聚合函数：AVG，COUNT（*），MAX，MIN，STDEV，STDEVP，VAR或VARP。
- 该语句不得使用任何不确定的函数，包括排名和窗口函数。
- 该语句不能包含用户定义的聚合。
- 该语句不得引用系统表或视图，包括目录视图和动态管理视图。
- 该语句不能包含FOR BROWSE信息。
- 该语句不能引用队列。
- 该语句不能包含不能更改且不能返回结果的条件语句（例如，WHERE 1 = 0）。
- 该语句不能指定READPAST锁定提示。
- 该语句不能引用任何Service Broker QUEUE。
- 该语句不能引用同义词。
- 该语句不能具有基于double / real数据类型的比较或表达式。
- 该语句不能使用TOP表达式。
在使用SqlDependency之前，首先需要启用数据库的服务器代理，默认情况下，SQL Server数据库没有启用Service Broker。你可以使用SQL语句启用Service Broker。
ALTER DATABASE DATABASE_NAME SET ENABLE_BROKER
下面是使用SqlDependency的简单示例：
static string connectionString = @"your connect string";
static void Main(string[] args)
{
SqlDependency.Start(connectionString);
getDataWithSqlDependency();
Console.WriteLine("Waiting for data changes");
Console.WriteLine("Press enter to quit");
Console.ReadLine();
SqlDependency.Stop(connectionString);
}
static DataTable getDataWithSqlDependency()
{
using (var connection = new SqlConnection(connectionString))
using (var cmd = new SqlCommand("SELECT productname FROM dbo.product;", connection))
{
var dt = new DataTable();
var dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(onDependencyChange);
connection.Open();
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
return dt;
}
}
static void onDependencyChange(object sender,SqlNotificationEventArgs e)
{
Console.WriteLine($"OnChange Event fired. SqlNotificationEventArgs: Info={e.Info}, Source={e.Source}, Type={e.Type}.");
if ((e.Info != SqlNotificationInfo.Invalid)
&& (e.Type != SqlNotificationType.Subscribe))
{
var dt = getDataWithSqlDependency();
Console.WriteLine($"Data changed. {dt.Rows.Count} rows returned.");
}
else
{
Console.WriteLine("SqlDependency not restarted");
}
Console.ReadLine();
}
参考资料:
https://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency
https://www.c-sharpcorner.com/UploadFile/87b416/working-with-sql-notification/
C#使用SqlDependency实现数据缓存
1、SqlDependency是什么：
SqlDependency对象表示应用程序和 SQL Server 实例间的查询通知依赖关系。应用程序可以创建一个SqlDependency对象并进行注册以通过OnChangeEventHandler事件处理程序接收通知。
它提供了这样一种能力：当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的。
应用：数据实时性较高的场景、新闻、监控数据等。
可以结合SignalR来实现web的实时数据更新。
2、使用方法：
1)sql server设置：ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;语句让相应的数据库启用监听服务，以便支持SqlDependency特性。
2)代码：控制台项目，web项目推荐把start放在Application_start方法中。
3)注意：查询语句中不能使用*，表名要加[dbo].[xx]。sql语句要执行。
conn =ConfigurationManager.ConnectionStrings["default"].ToString();
//Start和Stop方法
SqlDependency.Start(conn);
Update(conn);
private static void Update(string conn)
{
using (
SqlConnection connection =
new SqlConnection(conn))
{
//此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
string sql = "select agent from [dbo].[info]";
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
command.CommandType=CommandType.Text;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
//必须要执行一下command
command.ExecuteNonQuery();
Console.WriteLine(dependency.HasChanges);
}
}
}
//update insert delete都会进入
private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
Console.WriteLine("onchange方法中："+dependency.HasChanges);
Console.WriteLine("数据库数据发生变化"+DateTime.Now);
//这里要再次调用
Update(conn);
}
