在分离的线程池中使用SqlDependency。 [英] Using SqlDependency in a separated thread pool.

问题描述

开发此应用程序时我的主要参考是： http：// msdn .microsoft.com / zh-CN / library / a52dhwx7（v = vs.80）.aspx [ ^ ]



我在数据库中有一个表，并且有一个Windows服务在此表中随机插入一些行。然后，我的应用程序从表中获取这些行并在DataGridViewControl中查看它..但我有一个问题。我的问题是UI有一些选项和一些控件，当SqlDependency运行和获取数据时，用户无法使用应用程序UI，除非SqlDependency完成其进程。换句话说，当SqlDependency捕获或接收某些行时，windows应用程序挂起，主线程池变得太忙。



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



我想做两件事：

（1） - 我想运行先前或此处引用的相同代码示例，位于主线程的分离线程池或BackgroundWorker中。 （重要）。

我的一位朋友帮我解决了这段代码：

My main reference when developing this application is : http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx[^]

I have a table in a database, and there is a windows service insert some rows randomly in this table. Then, my application gets these rows from the table and view it in a DataGridViewControl .. but I have a problem. My problem is that the UI has some options and some controls and when SqlDependency running and getting data the user can not use the application UI unless SqlDependency complete its process. In another meaning, when the SqlDependency catches or receives some rows, the windows app hangs up and the main thread pool becomes too busy.

I want to run the same code referenced in the previous link in a separated thread pool by using Thread class or BackgroundWorker control. This means a thread for using UI controls and another one for listening to the database changes and get it into the DataGridView.

I want to do two things:
(1) - I want to run the same code sample referenced previously or here in a separated thread pool or a BackgroundWorker away of the main thread. (Critical).
One of my friends helped me with this code script:

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(new Action(() =>
                {
                dgv.DataSource = dataToWatch;
                dgv.DataMember = tableName;
                dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
                });
        }
    });
}

这段代码使得负责获取数据的方法在一个独立的线程池中运行，但是我有几个错误..我有很多错误的情况，

（第1号）：我在下一个图片链接中显示了此错误。 图像（ http://s7.postimg.org/9dpwvamej/image.png [ ^ ]）

（第2号）：我的网格视图中没有任何内容。 图像（ http://s24.postimg.org/y1ln3hmsl/image.png [ ^ ]）

（第3项）：虽然表中有行，但我只获得列名而没有行。 图片（ http://s9.postimg.org/oh6qz8sn3/image.png [ ^ ]）



所以我想要一个新方案来做这个。



（2） - 我想得到最新的只有行，不要每次绑定表并在DataGridView中显示它的行。

我需要帮助。

This code makes the method that is responsible for getting data to run in a separated thread pool, but I have a several errors .. I have many situations of errors,
(No. 1): I got this error shown in the next image link. Image (http://s7.postimg.org/9dpwvamej/image.png[^])
(No. 2): I got nothing in my grid view. Image (http://s24.postimg.org/y1ln3hmsl/image.png[^])
(No. 3): I got only columns names and no rows, although the table has rows. Image (http://s9.postimg.org/oh6qz8sn3/image.png[^])

so I want a new scenario to do this.

(2) - I want to get the newest rows only, don't bind the table every time and show its rows in the DataGridView.
I need help please.

推荐答案

有你考虑过使用队列吗？ CREATE QUEUE [ ^ ]



或者服务总线？ Service Bus 1.0下载 [ ^ ]



最好的问候

Espen Harlinn

Have you considered using a queue? CREATE QUEUE[^]

Or perhaps the Service Bus? Service Bus 1.0 download[^]

Best regards
Espen Harlinn

感谢Espen给予您的支持，但是让我告诉您我的新代码并解释它的问题。



现在，这是我新的清晰代码方案，问题是当SqlDependency收到一组时，用户无法使用UI控件顺序排。



我想要的是如何让用户单独使用UI远离应用程序的主线程，另一方面， GridView通常没有任何额外的负载接收最新的行。





一般对象：

Thanks Espen for you kindly support, but let me show you my new code and explain its problem.

Now, this is my new clear code scenario and its problem is that the user can't use UI controls when the SqlDependency receives a set of rows sequentially.

What I want exactly is that how to allow user uses the UI separately away of the main thread of the application, and on the other hand, the GridView receives the latest rows normally without any extra loads.


General objects:

string connectionString = "Server=.; Database=SMS_Tank_log; UID=sa; PWD=hana; Pooling=False; MultipleActiveResultSets=True";
const string statusMessage = "{0} changes have occurred.";
int changeCount = 0;
SqlConnection cn = new SqlConnection();

表格构造函数：

Form constructor:

public Form1()
{
    InitializeComponent();
    SqlDependency.Start(connectionString);
}

启动SqlDependency的按钮：

The button whose starts the SqlDependency:

private void btnStart_Click(object sender, EventArgs e)
{
    GetData();
}

此方法根据表中的特定标志从表中获取最新数据，并在以后更新它将在GridView中查看。

This method gets newest data from the table based on specific flag in the table, and it will updated later when it will be viewed in the GridView.

void GetData()
{
        // Create a new SqlCommand object.
        using (SqlCommand command = new SqlCommand("SELECT Serial, MessageID FROM OutgoingLog where monitored = 0", connection))
        {

            // Create a dependency and associate it with the SqlCommand.
            SqlDependency dependency = new SqlDependency(command);
            // Maintain the refence in a class member.

            // Subscribe to the SqlDependency event.
            dependency.OnChange += dependency_OnChange;

            // Execute the command.
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    dgv.Rows.Add(reader[0].ToString(), reader[1].ToString());
                    dgv.Refresh();
                }
            }
        }
}

SqlDependency对象的OnChange事件。

The OnChange event of SqlDependency object.

void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
	ISynchronizeInvoke i = (ISynchronizeInvoke)this;
	if (i.InvokeRequired)
	{
		OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
		object[] args = { sender, e };
		i.BeginInvoke(tempDelegate, args);
		return;
	}

	SqlDependency dependency = (SqlDependency)sender;
	dependency.OnChange -= dependency_OnChange;

	++changeCount;
	lblChanges.Text = String.Format(statusMessage, changeCount);
	lblChanges.Refresh();

	GetData();
}

此void负责更新监控标志以避免GridView中的重复

This void is responsible for updating the monitoring flag to avoid duplication in GridView

private void dgv_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("UPDATE OutgoingLog SET Monitored = 1 WHERE Serial = @id", conn))
        {
            cmd.Parameters.AddWithValue("@id", dgv[0, e.RowIndex].Value.ToString());
            cmd.ExecuteNonQuery();
            lblRowsCount.Text = dgv.Rows.Count.ToString();
            lblRowsCount.Refresh();
        }
    }
}

我可以和蔼可亲这里有帮助??

Can I get kindly help here ??

这篇关于在分离的线程池中使用SqlDependency。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！