SQL依赖于C＃ [英] SQL Dependency in C#
问题描述
我想弄清楚如何使用SQL依赖（C＃4.0），以'听'更改到数据库。我已经看到了网络上不少的东西，但他们似乎是量身定制的（自然）使用的依赖拉动了SQL依赖是依赖于相同的数据。例如，这篇文章。
我想要做的就是创建一种依赖，在触发时，将产生很多不同的SQL选择查询（我可以在其他方法等存储）。例如：我想设置手表表中的行数的依赖性。当行数增加，然后做X，Y，Z（即我的程序并不关心的行数为，只是它的增加，当它做了一堆东西）。
任何什么想法将最好的方式做到这一点？
编辑：我已经把它贴我的代码，因为我有它目前。我试图找出如何分离从的GetData（）过程中建立的SqlDependency。目前，虽然，我觉得我进入了一点一个无限循环，因为之后我删除了事件处理程序，然后重新运行SetupSqlDependency（），它会右后卫到事件处理程序
私人无效SetupSQLDependency（）
{
//教程这个发现在：
// http://www.dreamincode.net/forums /主题/ 156991-使用-的SqlDependency到显示器-SQL数据库更改/
SqlDependency.Stop（的connectionString）;
SqlDependency.Start（的connectionString）;
sqlCmd.Notification = NULL;
//创建的SqlCommand
的SqlDependency sqlDep =新的SqlDependency（SQLCMD）新的依赖;
sqlDep.OnChange + =新OnChangeEventHandler（sqlDep_OnChange）;
SqlDataReader的读卡器= sqlCmd.ExecuteReader（）;
}
私人无效sqlDep_OnChange（对象发件人，SqlNotificationEventArgs E）
{
// FROM：http://msdn.microsoft.com/en-us/a52dhwx7.aspx
＃区域
//将发生在一个线程池线程此事件。
//更新从工作线程UI是不允许的。
//下面的代码检查，看是否是安全的
//更新UI。
/ * ISynchronizeInvoke I =（ISynchronizeInvoke）这一点;
//如果InvokeRequired返回true，则代码
//是在一个工作线程执行。
如果（i.InvokeRequired）
{
//创建一个委托执行线程切换。
OnChangeEventHandler tempDelegate =新OnChangeEventHandler（sqlDep_OnChange）;
对象[] ARGS = {发送，E};
//从元帅工作者线程
//到UI线程的数据。
i.BeginInvoke（tempDelegate，参数）;
的回报;
} * /
#endregion
//必须删除它，因为它只是工作的一次
的SqlDependency sqlDep =发件人为的SqlDependency;
sqlDep.OnChange - = sqlDep_OnChange;
//此时，代码是
// UI线程上执行，因此，它是安全的更新UI ..
// 1 ）Resetup Dependecy
SetupSQLDependency（）;
}
可以挂钩的SqlDependency.Change事件，做任何你在此事件处理程序一样。这是，事实上，只有这样，才能做你想做的，并没有什么不妥的地方。
在伪代码看起来是这样的：
VAR DEP =新的SqlDependency（GetSqlQueryForMonitoring（））;
dep.Change + =（）=> {
VAR数据= ExecSql（GetDataQuerySql（））;
UpdateCache（数据）;
};
很简单。只要使用两个不同的查询
编辑：在您的示例代码中有一个评论说你是在UI线程上运行。为什么是这样的话？我对此表示怀疑。无论如何，你应该怎么resetup的依赖之前，否则，你将有并发失效发生的潜在运行查询。
我建议你从数据库中获取的新的数据和然后将消息发送到用户界面进行更新（调用）。
I'm trying to figure out how to use SQL Dependency (C# 4.0) to 'listen' for changes to a database. I've seen quite a few things on the web, but they seem to be tailored (naturally) for using the dependency to pull the same data that the SQL Dependency is dependent on. For example, this article.
What I'm trying to do is create a dependency that, when triggered, results in a number of different SQL 'Select' queries (which I can store in other methods etc). For example: I'm trying to set a dependency that watches the number of rows in table. When the number of rows increases, then do x, y, z (ie my program doesn't care what the number of rows is, just that it's increased, and when it does do a bunch of things).
Any thoughts on what would the best way to do this?
EDIT: I've attached my code as I have it currently. I'm trying to figure out how to separate setting up the SqlDependency from the GetData() process. Currently though, I think I go into a bit of an endless loop as after I remove the event handler and rerun "SetupSqlDependency()", it goes right back into the event handler
private void SetupSQLDependency()
{
// Tutorial for this found at:
// http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
sqlCmd.Notification = null;
// create new dependency for SqlCommand
SqlDependency sqlDep = new SqlDependency(sqlCmd);
sqlDep.OnChange += new OnChangeEventHandler(sqlDep_OnChange);
SqlDataReader reader = sqlCmd.ExecuteReader();
}
private void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
{
// FROM: http://msdn.microsoft.com/en-us/a52dhwx7.aspx
#region
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
/* ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(sqlDep_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}*/
#endregion
// Have to remove this as it only work's once
SqlDependency sqlDep = sender as SqlDependency;
sqlDep.OnChange -= sqlDep_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI..
// 1) Resetup Dependecy
SetupSQLDependency();
}
You can hook up the SqlDependency.Change event and do whatever you like in this event handler. This is, in fact, the only way to do what you want, and there is nothing wrong with it.
In pseudo-code it looks like this:
var dep = new SqlDependency(GetSqlQueryForMonitoring());
dep.Change += () => {
var data = ExecSql(GetDataQuerySql());
UpdateCache(data);
};
Very simple. Just use two different queries.
Edit: In your sample code there is a comment saying you are running on the UI thread. Why should that be the case? I doubt it. Anyway, you should run your query before you resetup the dependency because otherwise you will have the potential for concurrent invalidations happening.
I suggest you get your fresh data from the database and then send a message to the ui to update it (Invoke).
这篇关于SQL依赖于C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
