Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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;
                });
        }
    });
}

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.
Posted

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

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

Best regards
Espen Harlinn
 
Share this answer
 
Comments
Ahmad Negm 12-Jun-13 12:18pm    
I am not using Queues, I have just enabled the Broker on the database and using the same code referenced at MSDN >> http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

I want to run the same code in the previous link in a separated thread to allow user to use UI tools while SqlDependency thread listens to database changes.
Espen Harlinn 12-Jun-13 15:38pm    
That seems to imply that you are using the samme SqlConnection on two threads at the same time, which should cause trouble for your program. You write that you only want the newest records and queues are usually a good solution for that
Ahmad Negm 13-Jun-13 3:10am    
Yes, I am using the same SqlConnection within the asynchronous threads in the same time. What should I do to meet the previous requirements, please?

This is a critical problem, sir .. can you produce a code scenario to do that ?
Espen Harlinn 13-Jun-13 5:31am    
Create a separate connecton to the sql server for each thread, like:

[ThreadStatic]
private static SqlConnection connection;
void myWorker(object threadArgument)
{
connection = new SqlConnection();
using(connection)
{
connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
connection.Open();
// Now, do your stuff
}
}

public static SqlConnection Connection
{
get
{
return connection;
}
}
Ahmad Negm 13-Jun-13 6:10am    
Unfortunately, this did not work .. you can see my new code scenario in the next answer (http://www.codeproject.com/Answers/606200/UsingplusSqlDependencyplusinplusaplusseparatedplus#answer3) and help me if it is possible .. Thanks :)
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:
C#
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:
C#
public Form1()
{
    InitializeComponent();
    SqlDependency.Start(connectionString);
}


The button whose starts the SqlDependency:
C#
private void btnStart_Click(object sender, EventArgs e)
{
    GetData();
}


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.
C#
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();
                }
            }
        }
}


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();
}


This void is responsible for updating the monitoring flag to avoid duplication in GridView
C#
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 ??
 
Share this answer
 
Comments
Espen Harlinn 13-Jun-13 6:16am    
Have you heard of the Active Object pattern?
http://www.codeproject.com/Articles/56617/Applied-Long-Running-Active-Object-Pattern
http://www.cs.wustl.edu/~schmidt/PDF/Act-Obj.pdf
IMO you are trying to make this simpler than your requirements allows for.
Ahmad Negm 13-Jun-13 6:24am    
No, I do not know anything about Active Object pattern, did you mean that there is no way to do what I want by using threads or what sir ?
Espen Harlinn 13-Jun-13 6:32am    
No, rather the opposite - it seems you need to get all the interaction with the database into separate threads - and the Active Object pattern is a good recipe for doing that, even if it might be an overkill.

When you want to update the ui, do it in batches - perhaps using BeginInvoke to pass something like 5000 records at the time to the ui thread.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900