SqlDependency using BackgroundWorker

我在SQL Server数据库中有一个表，它表示从运行的windows服务插入的一些操作的日志文件。一切正常。

但是，我有一个Windows应用程序，它获得已插入到日志表中的最新行，并在DataGridView中查看它。在开发这个应用程序时，我依赖于MSDN在Windows应用程序中使用SqlDependency。它工作得很好，但是当日志表接收到大量的日志细节时，Windows应用程序挂起，主线程池变得太忙。

我想通过使用Thread类或BackgroundWorker控制在一个分离的线程池中运行前面链接中引用的相同代码。这意味着一个线程用于使用UI控件，另一个线程用于侦听数据库更改并将其放入DataGridView。

你可以从这个链接看到UI截图"UI"

。(1):此群盒代表用户在监控时可以使用的UI工具。

。(2): Start按钮负责开始侦听和接收来自数据库的更新，并重新填充DataGridView。

。(3):此网格表示已插入数据库的新日志。

。(4):这个数字(38次更改)表示侦听sql依赖项对数据库更改的计数。

公共部分类frommain: Form｛SqlConnection康涅狄格州;

    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);
        this.Refresh();
        // Reload the dataset that is bound to the grid.
        GetData();
    }
    private void GetData()
    {
        // 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);
            dgv.DataSource = dataToWatch;
            dgv.DataMember = tableName;
            dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
        }
    }
    private void btnStart_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("<my connection string>");
        SqlDependency.Start("<my connection string>");
        if (connection == null)
        {
            connection = new SqlConnection("<my connection string>");
        }
        if (command == null)
        {
            command = new SqlCommand("select * from OutgoingLog", connection);
        }
        if (dataToWatch == null)
        {
            dataToWatch = new DataSet();
        }
        GetData();
    }
    private void frmMain_Load(object sender, EventArgs e)
    {
        btnStart.Enabled = CanRequestNotifications();
    }
    private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
    {
        SqlDependency.Stop("<my connection string>");
    }
}

我想要的是:当用户单击Start按钮时，应用程序在一个分离的线程池中运行代码。

private void GetData()
{
    // Start the retrieval of data on another thread to let the UI thread free
    ThreadPool.QueueUserWorkItem(o =>
    {
        // 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);
            // Update the UI
            dgv.Invoke(() =>
                {
                    dgv.DataSource = dataToWatch;
                    dgv.DataMember = tableName;
                    dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
                });
        }
    });
}

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);
            running.Set();
            // Update the UI
            dgv.Invoke(() =>
                {
                dgv.DataSource = dataToWatch;
                dgv.DataMember = tableName;
                dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
                });
        }
    });
}