.net - Monitor New Data in MySQL Table

Question

I want to know if there's new row in my table. How can I do that in VB.Net ?

Example An Insert Query has triggered then a MessageBox will prompt telling me that there's new data inserted in my table.

If its possible can someone explain and tell me how can I do it? (It would be better if there is code :3)

Answer 1

Your question remains unclear. Monitoring only one type of change - "new data" (INSERT) as per the title - is more complicated than detecting any change (as per I want to detect the Changes in my table in comments which is simpler).

MySql provides the means to get the checksum of a table:

checksum table TABLE_NAME [QUICK | EXTENDED]

MySQL Workbench results for an InnoDB and MyISAM table:

By watching for changes in those return values, you can detect any change. But note:

• The table must have been created with the Checksum = 1 option
• The QUICK option does not work on InnoDB tables prior to version 5.7.2 (IIRC and the current Community version is 5.7.14).

Luckily, if you do not specify an option, MySQL seems to pick the fastest one which will return a value. So, it becomes easy to track changes by table on a Timer:

' Track last checksum by table
Friend Class TableItem
    Public Property Name As String
    Public Property CheckSum As Int64

    Public Sub New(n As String)
        Name = n
        CheckSum = 0
    End Sub
End Class
' a list of them to track more than one table:
Private Tables As List(Of TableItem)

Initialize:

Timer1.Enabled = True

Tables = New List(Of TableItem)
Tables.Add(New TableItem("Sample"))
Tables.Add(New TableItem("SampleISAM"))

The Timer Tick event:

' Note: cannot use Parameters for table or col names
Dim sql = "CHECKSUM TABLE {0} "

Using dbcon As New MySqlConnection(mySQLConnStr)
    dbcon.Open()
    Using cmd As New MySqlCommand(sql, dbcon)
        ' loop thru collection, polling one at a time
        For Each tbl As TableItem In Tables
            cmd.CommandText = String.Format(sql, tbl.Name)

            Using rdr As MySqlDataReader = cmd.ExecuteReader()
                If rdr.Read Then
                    Dim thisResult = rdr.GetInt64(1)

                    ' ignore the first result
                    If tbl.CheckSum = 0 Then
                        tbl.CheckSum = thisResult
                        Return
                    End If
                    ' save the last non-zed value
                    If tbl.CheckSum <> thisResult Then
                        tbl.CheckSum = thisResult
                        ' method to do something when changed:
                        TableChanged(tbl.Name)
                    End If

                End If
            End Using
        Next
    End Using
End Using

My do something method us just reporting the changes to a listbox:

Private Sub TableChanged(tbl As String)
    lb.Items.Add(String.Format("Table {0} changed {1}", tbl,
                               DateTime.Now.ToString("HH:mm:ss.ffffff")))
End Sub

To actually watch for something like only INSERTS, you'd need to use some sort of log table. Add a trigger which updates that table with a TimeStamp and maybe action code ("insert", "delete"). Then just check the TimeStamp for changes, perhaps filtering out non-watch actions.

Particularly a version to watch multiple tables or certain change events will work better as a class. The timer code can be encapsulated and it could raise events for a table changes.

• MySQL v. 5.6 13.7.2.3 CHECKSUM TABLE Syntax