不稳定的应用程序使用 SqlDependency.几种状态和错误 [英] Unstable application uses SqlDependency. Several states and errors
问题描述
我有一个使用 SqlDependency 在分离的线程池中运行的 Windows 应用程序，该应用程序代表一个日志监视器 UI，获取添加到数据库特定表中的最新行并在 DataGridView 中查看它.您可以从此LINK查看应用程序源代码，或按照此脚本操作.
I have a windows application using SqlDependency running at separated thread pool, this application represents a log monitor UI get the latest rows added in a specific table in the database and view it in a DataGridView. You can see the application source code from this LINK, or follow this script.
const string tableName = "OutgoingLog";
const string statusMessage = "{0} changes have occurred.";
int changeCount = 0;
private static DataSet dataToWatch = null;
private static SqlConnection connection = null;
private static SqlCommand command = null;
public frmMain()
{
InitializeComponent();
}
private bool CanRequestNotifications()
{
// In order to use the callback feature of the
// SqlDependency, the application must have
// the SqlClientPermission permission.
try
{
SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
perm.Demand();
return true;
}
catch
{
return false;
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// 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(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
lblChanges.Text = String.Format(statusMessage, changeCount);
// Reload the dataset that is bound to the grid.
GetData();
}
AutoResetEvent running = new AutoResetEvent(true);
private void GetData()
{
// Start the retrieval of data on another thread to let the UI thread free
ThreadPool.QueueUserWorkItem(o =>
{
running.WaitOne();
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataToWatch, tableName);
try
{
running.Set();
}
finally
{
// Update the UI
dgv.Invoke(new Action(() =>
{
dgv.DataSource = dataToWatch;
dgv.DataMember = tableName;
//dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
}));
}
}
});
}
private void btnAction_Click(object sender, EventArgs e)
{
changeCount = 0;
lblChanges.Text = String.Format(statusMessage, changeCount);
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop("Server=.; Database=SMS_Tank_Log;UID=sa;PWD=hana;MultipleActiveResultSets=True");
SqlDependency.Start("Server=.; Database=SMS_Tank_Log;UID=sa;PWD=hana;MultipleActiveResultSets=True");
if (connection == null)
{
connection = new SqlConnection("Server=.; Database=SMS_Tank_Log;UID=sa;PWD=hana;MultipleActiveResultSets=True");
}
if (command == null)
{
command = new SqlCommand("select * from OutgoingLog", connection);
//SqlParameter prm =
// new SqlParameter("@Quantity", SqlDbType.Int);
//prm.Direction = ParameterDirection.Input;
//prm.DbType = DbType.Int32;
//prm.Value = 100;
//command.Parameters.Add(prm);
}
if (dataToWatch == null)
{
dataToWatch = new DataSet();
}
GetData();
}
private void frmMain_Load(object sender, EventArgs e)
{
btnAction.Enabled = CanRequestNotifications();
}
private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
{
SqlDependency.Stop("Server=.; Database=SMS_Tank_Log;UID=sa;PWD=hana;MultipleActiveResultSets=True");
}
问题:我有很多错误情况，(第一条评论中的图片)
(第 1 名):我收到了这个错误对话框，我不知道它的原因.
(No. 1): I got this error dialog, and I don't know its reason.
(第 2 名):我的网格视图中什么也没有(没有错误，也没有数据).
(No. 2): I got nothing in my grid view (No errors, and no data).
(第 3 名):虽然表有行，但我只有列名，没有行.
(No. 3): I got only columns names and no rows, although the table has rows.
我需要帮助.
推荐答案
我可能错了，DataSet 似乎没有通知能力，所以 如果您在背后更改 DataGridView，可能会感到惊讶.
I may be wrong but a DataSet does not seem to have notification capability so the DataGridView may be surprised if you change it behind its back.
您可以尝试通过首先将其设置为 null 来明确显示您正在更改数据源:
You could try to explicitly show your're changing the data source by first setting it to null:
dgv.DataSource = null;
dgv.DataSource = dataToWatch;
dgv.DataMember = tableName;
值得一试...
这篇关于不稳定的应用程序使用 SqlDependency.几种状态和错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
