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);
}
The button whose starts the SqlDependency:
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.
void GetData()
{
using (SqlCommand command = new SqlCommand("SELECT Serial, MessageID FROM OutgoingLog where monitored = 0", connection))
{
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += dependency_OnChange;
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
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 ??