How to determine if a database has been modified for Access, SQL, Oracle, or file systems

Question

At the company, I work to ensure that all applications extract information from this database, I decided to write a detailed answer to answer how different databases can tell the user that they have been changed. I will answer the following types:

• Access
• SQL
• Oracle
• File Systems (Files and Folders)

Why did I do this? ...... The company I work for has many different databases and applications that use these databases. However, applications spend a lot of time checking the database to see if the data has changed. I completed this list to show how certain databases / files can use different tools so that the application can find out that it has been modified. Thus, an event can be triggered. This, we hope, will reduce computing power and speed up applications.

Please edit as you think. If you need any other information, the comment will be great. I am still in the process of adding an Oracle database solution and editing Access and SQL.

Answer 1

Access and file systems / files

For the access point, I used SystemFileWatcher. This keeps track of the database, and if it has been modified, it will run code to retrieve new data from the database. This means that the application does not constantly enter the database and captures new data when it is not required.

FileSystemWatcher can run other code from events such as name change, move or change. I only need to modify. I have a database path from an XML file that I use, which means that it is not hardcoded and can be modified from an xml file, and the observer will look elsewhere.

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 that, I just have a function with a modified function. Hope this helps anyone who needs it.

File systems / files

For the file path and system paths, the code above is very similar, just using different paths and filters to get specific file types or names. Then it will run the code if they have been changed / changed. If someone wants a code for this, write a comment and I can provide some.

SQL Databases

In SQL databases, there are several ways to check if data has changed. I will link to several MSDN pages along with another question to provide them with information. However, the method I used was slightly different, because I did not have a broker service, and no queues were included in my SQL databases.

Is there something like FileSystemWatcher server tables for Sql?

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

However, the way I used it was to use a checksum and a timer and check the checksum in a loop to see if the database was modified. Because 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 is:

'Inside main, to get the first checksum value, ready to be merged with 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 match, the checksum is changed so that the next time this happens, they will be the same if the database is not changed. I stopped and then ran the time again to avoid confusion in the message box, however the message box is used for debugging purposes, since it could be an event or something from the code that would have happened if the database were changed.

Oracle

After research, I could not implement this solution in my own application, but I hope it will provide information to other users. There is something in Oracle called OracleDependencyClass that gives the application a notification if the selected data has been changed. I will put some hyperlinks in which there are some examples and the basics of how to use them in the hope that someone does not need to reflect my own research.

Database Application Notification

Class OracleDependency

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

Class usage example in C # and VB.NET

If these pages do not help, there are many other web pages that you can access if you are either looking for "oracle dependency", "OracleDependency Class", and "Database change notification".