How to determine if a database has been altered fo

Question

At the company I work for all applications pull information from that database, I have decided to write a detailed answer to answer how different databases can let the user know they have been altered. I will answer for the following types:

• Access
• SQL
• Oracle
• File systems (Files and folders)

Why I have done this?...... The company I work for have many different databases and applications that use these databases. However the applications spend a lot of time within the database checking to see if the data has been changed. I have complied this list to show how certain databases/files can use different tools to let an application know it has been changed. So an event can be fired off. This will hopefully reduce computing power and speed up the applications.

Please edit as you seem fit. If you need any other information a comment would be great. I am still in the process of adding the Oracle database solution and editing the Access and SQL.

Answer 1

Access and FileSystems/Files

For the access point I have used a SystemFileWatcher. This keeps an eye on the database and if it has been modified it will run the code to get the new data from the database. This means that the application is not constantly going in to the database and grabbing new data when it is not needed.

The FileSystemWatcher can run different code from the events such as name changed, moved or modified. I only need to use the modified. I have got the database path from an XML File I am using which means it is not hard coded and can be changed from the xml file and the watcher will watch else where.

Protected Overrides Sub OnStart(ByVal args() As String)     
    Dim g1 As New FileSystemWatcher()
    g1.Path = GetSingleNode(XmlFileName, "data/G1Path")

    g1.NotifyFilter = (NotifyFilters.LastAccess Or NotifyFilters.LastWrite Or NotifyFilters.FileName Or NotifyFilters.DirectoryName)

    g1.Filter = GetSingleNode(XmlFileName, "data/G1Filter")
    AddHandler g1.Changed, AddressOf OnChanged

    g1.EnableRaisingEvents = True

    Dim g2 As New FileSystemWatcher()
    g2.Path = GetSingleNode(XmlFileName, "data/G2Path")

    g2.NotifyFilter = (NotifyFilters.LastAccess Or NotifyFilters.LastWrite Or NotifyFilters.FileName Or NotifyFilters.DirectoryName)
    g2.Filter = GetSingleNode(XmlFileName, "data/G2Filter")

    AddHandler g2.Changed, AddressOf OnChanged
    g2.EnableRaisingEvents = True

End Sub

Protected Overrides Sub OnStop()

End Sub

Public Shared Function GetSingleNode(ByVal xmlPath As String, ByVal nodeLocation As String) As String
    Try
        Dim xpathDoc As New XPathDocument(xmlPath) 'gets the nodes from the XML file ready to be put in to the network path/variables
        Dim xmlNav As XPathNavigator = xpathDoc.CreateNavigator()

        Return xmlNav.SelectSingleNode(nodeLocation).Value

    Catch ex As Exception
        Throw

    End Try
End Function

After this I simply have an on changed function. Hope this helps anyone that needs it.

FileSystems/Files

For the file path and system paths the code above is very similar just using different paths and filters to get the certain types or names of files. This will then run the code if these have been changed/modified. If anybody would like code for this please write a comment and I can supply some.

SQL Databases

In the SQL databases there are multiple ways of checking to see if the data has been changed. I will reference a few MSDN Pages along with another question to provide information to these. However the way I used was slightly different as I didn't have a service broker running and no queues were enabled on my SQL databases.

Is there something like the FileSystemWatcher for Sql Server Tables?

http://msdn.microsoft.com/en-us/library/62xk7953.aspx#Y342

However the way I used was by using a checksum and a timer and checking the checksum on a loop to see if the database was changed. As the 'hash' always changes if the data is changed:

http://sqlserverplanet.com/design/how-to-detect-table-changes

http://www.mssqltips.com/sqlservertip/1023/checksum-functions-in-sql-server-2005/

My Code:

'Within main to get the first checksum value ready to be comapred with at a later date. These are global variables

Dim newdata As DataTable = SQLMethods.ExecuteReader(ConnectionString1, "SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Alarms")
checksum = newdata.Rows(0).Item(0)


    Timer1.Start()

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
    Timer1.Stop()
    Dim newdata As DataTable = SQLMethods.ExecuteReader(ConnectionString1, "SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Alarms")
    checksumNew = newdata.Rows(0).Item(0)

    If checksum <> checksumNew Then
        MsgBox("Hello")


        checksum = checksumNew
    End If
    Timer1.Start()
End Sub

As you can see if they do match the checksum is changed to match so the next time it happens they will be the same unless the database is indeed changed. I have stopped and then restarted the time to avoid confusion of the message box, however the message box is used for debugging purposes as an event could be fired here or what ever code was wanting to happen if the database was changed.

Oracle

After doing research I have not been able to implement this solution in my own application but hopefully it will provide info to other users. In Oracle there is something called OracleDependencyClass which provides an application a notification if the chosen data has been modified. I will supply some hyper-links that have some examples and the basics of how to use these in the hope someone doesn't need to mirror my own research.

Developing Applications with Database Change Notification

OracleDependency Class

Oracle® Data Provider for .NET Developer's Guide - OracleDependency Class(2)

Example of using the class in C# and VB.NET

If these pages don't help there are plenty of other webpages that you can access if you either search for "oracle dependency", "OracleDependency Class" and "Database Change Notification".