不稳定的应用程序使用 SqlDependency.几种状态和错误

问题描述

我有一个使用 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.几种状态和错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！