How to refresh a table every few minutes

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
ashlar64
Posts: 75
Joined: Thu 04 May 2006 18:56

How to refresh a table every few minutes

Post by ashlar64 » Mon 09 Jun 2008 15:32

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?)

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Tue 10 Jun 2008 08:31

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

ashlar64
Posts: 75
Joined: Thu 04 May 2006 18:56

Post by ashlar64 » Tue 10 Jun 2008 12:59

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.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 11 Jun 2008 14:19

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.
Last edited by Alexey.mdr on Thu 12 Jun 2008 07:59, edited 1 time in total.

ashlar64
Posts: 75
Joined: Thu 04 May 2006 18:56

Post by ashlar64 » Wed 11 Jun 2008 18:02

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

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 12 Jun 2008 08:36

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.

ashlar64
Posts: 75
Joined: Thu 04 May 2006 18:56

Post by ashlar64 » Thu 12 Jun 2008 13:26

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.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 13 Jun 2008 07:13

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.

ashlar64
Posts: 75
Joined: Thu 04 May 2006 18:56

Post by ashlar64 » Fri 13 Jun 2008 14:29

Ahh that makes sense. Thanks for clarifying that up.
:D

Post Reply