How to refresh a table every few minutes
How to refresh a table every few minutes
Hello,
I am having alot of issues trying to refresh a table every minute or so. I would like to refresh the table with a timer event. But is the only way to refresh a table is to use the Adapter and the Fill method? (Won't the Fill method reload all the data and not the data that is different? Is there a better way?)
I am having alot of issues trying to refresh a table every minute or so. I would like to refresh the table with a timer event. But is the only way to refresh a table is to use the Adapter and the Fill method? (Won't the Fill method reload all the data and not the data that is different? Is there a better way?)
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
There is an alternative way, you can find an example in MySqlDependency class topic (help reference).
Additionaly, take a look at this post:
http://crlab.com/forums/viewtopic.php?p=34243#34243
Additionaly, take a look at this post:
http://crlab.com/forums/viewtopic.php?p=34243#34243
Looking at the help for this class I see this example:
string connectionString = "User Id=root;Password=root;Host=localhost;Database=test;";
void Start()
{
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand commandDeptEmp = new MySqlCommand("select * from dept, emp", connection);
MySqlCommand commandPict = new MySqlCommand("select * from mysqlnet_pictures", connection);
MySqlDependency dependency = new MySqlDependency(commandDeptEmp, 100);
dependency.AddCommandDependency(commandPict);
dependency.OnChange += new CoreLab.Common.DbDependency.OnChangeEventHandler(dependency_OnChange);
MySqlDependency.Start(connectionString);
}
void Stop()
{
MySqlDependency.Stop(connectionString);
}
void dependency_OnChange(object sender, CoreLab.Common.DbTableChangeEventArgs e) {
// process changes
}
//---------------------------------------------------------
I do have a few questions...
1)In the help file it mentions you need a TimeStamp column. If you have multiple Timestamps in the column how does it know which one to use?
2) Putting code in this method void dependency_OnChange(object sender, CoreLab.Common.DbTableChangeEventArgs e)
Would executing the Update command just upload the changes that were made in the database and upload them to the app? Or will I need to reload the whole table? Or is there another way?
On a side note I am using WPF and this will be a multi-user app.
string connectionString = "User Id=root;Password=root;Host=localhost;Database=test;";
void Start()
{
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand commandDeptEmp = new MySqlCommand("select * from dept, emp", connection);
MySqlCommand commandPict = new MySqlCommand("select * from mysqlnet_pictures", connection);
MySqlDependency dependency = new MySqlDependency(commandDeptEmp, 100);
dependency.AddCommandDependency(commandPict);
dependency.OnChange += new CoreLab.Common.DbDependency.OnChangeEventHandler(dependency_OnChange);
MySqlDependency.Start(connectionString);
}
void Stop()
{
MySqlDependency.Stop(connectionString);
}
void dependency_OnChange(object sender, CoreLab.Common.DbTableChangeEventArgs e) {
// process changes
}
//---------------------------------------------------------
I do have a few questions...
1)In the help file it mentions you need a TimeStamp column. If you have multiple Timestamps in the column how does it know which one to use?
2) Putting code in this method void dependency_OnChange(object sender, CoreLab.Common.DbTableChangeEventArgs e)
Would executing the Update command just upload the changes that were made in the database and upload them to the app? Or will I need to reload the whole table? Or is there another way?
On a side note I am using WPF and this will be a multi-user app.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Hello,
1) MySqlDependency will use only the first column if a table has several timestamps columns. In this case a hash will be calculated on the first timestamp column and this hash will be compared to the previous value.
2) I'm afraid, you need to load the whole table, as MySqlDependency doesn't track the updated data itself. It can just notify that the table has been changed.
Regards,
Alexey.
1) MySqlDependency will use only the first column if a table has several timestamps columns. In this case a hash will be calculated on the first timestamp column and this hash will be compared to the previous value.
2) I'm afraid, you need to load the whole table, as MySqlDependency doesn't track the updated data itself. It can just notify that the table has been changed.
Regards,
Alexey.
Last edited by Alexey.mdr on Thu 12 Jun 2008 07:59, edited 1 time in total.
Hi Alexey,
Thanks for the info....but it does bring up a few more questions.
1) Should I assume that dependency_OnChange event won't be fired if the person who is using the app (and no one else is changing any data) is the one to make a change. (So if the user makes a change it won't cause this event to fire) Is this correct?
2) So if I go this route to update a table that has been changed I should use the Fill method of the corresponding Adapter correct? Just want to make sure there might be a faster way or something.
---Dave
Thanks for the info....but it does bring up a few more questions.
1) Should I assume that dependency_OnChange event won't be fired if the person who is using the app (and no one else is changing any data) is the one to make a change. (So if the user makes a change it won't cause this event to fire) Is this correct?
2) So if I go this route to update a table that has been changed I should use the Fill method of the corresponding Adapter correct? Just want to make sure there might be a faster way or something.
---Dave
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Hello Dave,
1) No, this is not correct.
On the first internal query MySqlDependency calculates a small unique hash value for the table.
With the next queries MySqlDependency recalculates the table's hash and compares the newer value with the older one.
If they do not match, the table has been changed.
So it doesn't matter who has updated the table. MySqlDependency is responsible for tracking any change in the table.
2) Basically, yes.
But usually you don't want to show all data from the table to the user.
Instead you would rather sort it, limit the rows amount, say to the first hundred rows or perform asynchronous queries.
You can find some samples in the “Asynchronous Query Execution” topic in the product help.
Regards,
Alexey.
1) No, this is not correct.
On the first internal query MySqlDependency calculates a small unique hash value for the table.
With the next queries MySqlDependency recalculates the table's hash and compares the newer value with the older one.
If they do not match, the table has been changed.
So it doesn't matter who has updated the table. MySqlDependency is responsible for tracking any change in the table.
2) Basically, yes.
But usually you don't want to show all data from the table to the user.
Instead you would rather sort it, limit the rows amount, say to the first hundred rows or perform asynchronous queries.
You can find some samples in the “Asynchronous Query Execution” topic in the product help.
Regards,
Alexey.
Hello again,
Ok so just to make sure I understand this. It sounds like you make a unique hash value for the table on the database and also for the same table in memory and it compares these two values to determine if it fires off that event. So if in my app the user changes the data in memory and the app also saves this change to the database...MySqlDependency won't fire off that event because the two hash values are the same correct?
As to your answer to my #2 question. I am using the Xceed WPF DataGrid (excellent datagrid btw) and I am using the .DefaultView property of the table to do some sorts and filters already....so I am not sure if this changes anything. But I will check out “Asynchronous Query Execution” and play around with that a bit and see if I can optimize this in some way.
Ok so just to make sure I understand this. It sounds like you make a unique hash value for the table on the database and also for the same table in memory and it compares these two values to determine if it fires off that event. So if in my app the user changes the data in memory and the app also saves this change to the database...MySqlDependency won't fire off that event because the two hash values are the same correct?
As to your answer to my #2 question. I am using the Xceed WPF DataGrid (excellent datagrid btw) and I am using the .DefaultView property of the table to do some sorts and filters already....so I am not sure if this changes anything. But I will check out “Asynchronous Query Execution” and play around with that a bit and see if I can optimize this in some way.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Hello,
Well, MySqlDependency will fire an event anyway.
It doesn't matter what data you have got in the local client memory.
MySqlDependency always compares hashes for the table on server side.
Say, I have a table tbl1 with rows:
1)AAA AAA
2)BBB BBB
3)CCC CCC
This is a table on the server.
Also I have a user-client U1.
U1 loads tbl1 to the his machine's managed heap.
Every 5 seconds MySqlDependency from U1 application calculates a hash for the tbl1 (that is stored on the server). Say the hash value is:
_currentHash = “ABC”
U1 locally changes the tbl1:
1) AAA BBB
2) BBB CCC
3) CCC DDD
MySqlDependency compares _currentHash (which is “ABC” now) to the new hash, which is also “ABC”, as tbl1 on the server has not changed. The event is not fired.
Then U1 updates tbl1 and overrides the data on the server side.
Next time MySqlDependency calculates a hash for the tbl1 and gets a new hash value, say:
_newHash = “AAA”. Finally, MySqlDependency compares “AAA” (_newHash) and “ABC” (_currentHash) and fires an event.
Regards,
Alexey.
Well, MySqlDependency will fire an event anyway.
It doesn't matter what data you have got in the local client memory.
MySqlDependency always compares hashes for the table on server side.
Say, I have a table tbl1 with rows:
1)AAA AAA
2)BBB BBB
3)CCC CCC
This is a table on the server.
Also I have a user-client U1.
U1 loads tbl1 to the his machine's managed heap.
Every 5 seconds MySqlDependency from U1 application calculates a hash for the tbl1 (that is stored on the server). Say the hash value is:
_currentHash = “ABC”
U1 locally changes the tbl1:
1) AAA BBB
2) BBB CCC
3) CCC DDD
MySqlDependency compares _currentHash (which is “ABC” now) to the new hash, which is also “ABC”, as tbl1 on the server has not changed. The event is not fired.
Then U1 updates tbl1 and overrides the data on the server side.
Next time MySqlDependency calculates a hash for the tbl1 and gets a new hash value, say:
_newHash = “AAA”. Finally, MySqlDependency compares “AAA” (_newHash) and “ABC” (_currentHash) and fires an event.
Regards,
Alexey.