TKGhoul TKGhoul - 1 year ago 118 Question

Monitor New Data in MySQL Table using VB.Net

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
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 Source

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:

enter image description here

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)


Timer1.Enabled = True

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

The Timer Tick event:

Dim sql = "CHECKSUM TABLE {0} "

Using dbcon As New MySqlConnection(mySQLConnStr)
    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
                    End If
                    ' save the last non-zed value
                    If tbl.CheckSum <> thisResult Then
                        tbl.CheckSum = thisResult
                        ' method to do something when changed:
                    End If

                End If
            End Using
    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,
End Sub

enter image description here

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.

This version works better as a class which raises an event for a table change.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download