MySqlDependency Class

In This Topic

Tracks changes on the server.

Syntax

Visual Basic
C#

'Declaration
 
Public Class MySqlDependency 
   Implements System.IDisposable

public class MySqlDependency : System.IDisposable

Remarks

Represents a process that periodically polls the server and fires an event if it detects changes in monitored tables.

MySqlDependency provides several ways to determine if there are changes on the server:

Using table checksums.
This method uses the CHECKSUM TABLE statement to check whether the table data was changed. Note that this statement is best used against MyISAM tables, created with the CHECKSUM = 1 clause, because for other storage engines, and for MyISAM tables, created without this clause, this statement reads all the table data to calculate checksums. This method also doesn't work with views.
Using a TIMESTAMP column.
This method offers higher performance for non-MyISAM tables, but requires a timestamp column to be present in the table. It checks table for changes by performing the Works with any table engine, but requires presence of a TIMESTAMP column in the table. MySqlDependency does not modify the column data, it only calculates some kind of hash and stores this hash to compare with later. The hash is calculated using the following statement:

SELECT md5(group_concat(md5(timestamp_column))) FROM table_name

Note that this statement uses MySQL's group_concat function, which concatenates hashes of a timestamp values of every row. This function has a limit of the result string length, set in the group_concat_max_len variable. By default, this limit is vely low, and everything beyond this limit is ignored, and thus, changes in some rows may be ignored. To avoid this, increase the group_concat_max_len value either in the server settings or for current session. You can do it in the following way for current session:

SET SESSION group_concat_max_len=4294967295

The value above is the max possible value for 32-bit MySQL server and it is enough for tables with 4294967295 / 33 - approximately 130150524 rows. If table contains more rows, MySqlDependency may not detect changes in some of the rows. For 64-bit MySQL, this value can be much larger, up to 18446744073709551615.
Using your own callback function.
This is the most flexible way to determine state of a table or any subquery.

By default, MySqlDependency selects the method to use in the following way: if a table has at least one TIMESTAMP column, the dependency uses it to check for changes; otherwise it uses the CHECKSUM TABLE statement. It also allows you to explicitely specify, which method to use for a command by using the MySqlDependency Constructor(MySqlCommand,Int32,MySqlDependencyCheckType) or AddCommandDependency(MySqlCommand,MySqlDependencyCheckType) overloads with the checkType argument. The latter one determines the method to use for the command: default behavior, or only CHECKSUM TABLE or only timestamp columns. See MySqlDependencyCheckType for more details.

If you want to use your own callback function, use the OnCheck event handler for this.

When specifying the method to use, you need to take the following considerations into account:

The CHECKSUM TABLE statement is only efficient against MyISAM tables, created with the CHECKSUM=1 clause.
While using timestamp columns is more efficient, in most cases it requires increasing the group_concat_max_len value.
If a table contains hundreds of millions of rows, and you use timestamp columns for tracking changes, 32-bit MySQL won't allow setting group_concat_max_len to a high enough value to detect changes in any of the rows, and CHECKSUM TABLE statement would probably be too slow. In this case you may need to implement your own function to track changes.

The MySqlDependency object uses information from MySqlCommand objects to determine what tables should be checked with the CheckTimeout interval in milliseconds. After you add MySqlCommand objects with the AddCommandDependency(MySqlCommand) method, invoke the Start(String) method with connection string to use when checking for changes. The MySqlDependency object will start polling the server periodically until you invoke the Stop method.

Every time the MySqlDependency object detects that a table has been changed it invokes the OnChange event handler, where you can perform necessary actions.

This class is not available in Mobile Edition.

Example

This example shows how to track changes within several commands, including command that uses several tables.

csharp

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 Devart.Data.MySql.OnChangeEventHandler(dependency_OnChange);
  MySqlDependency.Start(connectionString);
}

void Stop()
{
  MySqlDependency.Stop(connectionString);
}

void dependency_OnChange(object sender, Devart.Data.MySql.MySqlTableChangeEventArgs e) {
  // process changes
}

Inheritance Hierarchy

System.Object
Devart.Data.MySql.MySqlDependency

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

MySqlDependency Members
Devart.Data.MySql Namespace

Send Feedback