如何确定是否数据库已经改变了访问，SQL，Oracle或文件系统 [英] How to determine if a database has been altered for Access, SQL, Oracle or File Systems

问题描述

目前我公司工作的所有应用程序拉从该数据库中的信息，我决定写一个详细的回答回答不同的数据库可以如何让用户知道他们已经改变。我会回答以下类型：

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:

• 访问


• SQL


• 的Oracle


• 文件系统（文件和文件夹）

我为什么这样做？......我所在的公司工作，有许多不同的数据库，并使用这些数据库的应用程序。然而，应用程序花费了大量的时间数据库检查，查看是否数据已经改变内。我已遵守该列表以显示如何某些数据库/文件可以使用不同的工具，让应用程序知道它已被更改。因此，一个事件可以被解雇了。希望这将降低计算能力和加快应用程序。

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.

请编辑你似乎契合。如果您需要任何其它信息的评论将是巨大的。我仍然在加入甲骨文数据库解决方案和编辑Access和SQL的过程。

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.

推荐答案

有关接入点我已经使用了SystemFileWatcher。这使得对数据库的眼睛，如果它已被修改它将运行code键从数据库中获取新的数据。这意味着，应用程序不经常会在向数据库中，并且在不需要时抓住新的数据。

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.

FileSystemWatcher对象可以运行，如名称更改，移动或修改的事件不同的code。我只需要使用修改过的。我有从我使用的，这意味着它是不难codeD，并且可以从XML文件中改变，观察者会看其他XML文件数据库路径在哪里。

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.

有关文件路径和系统路径高于code是非常相似的只是使用不同的路径和过滤器，以获得特定类型或文件名。那么这将运行code，如果这些已经被改变/修改。如果有人想code此请写一个评论，我可以提供一些。

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数据库有检查，查看是否数据已经被改变的多种方式。我将引用一些MSDN页面有一个问题向这些提供信息。不过我用的方式略有不同，因为我没有一个服务代理运行，没有人排队在我的SQL数据库启用。

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.

<一个href=\"http://stackoverflow.com/questions/1311924/is-there-something-like-the-filesystemwatcher-for-sql-server-tables\">Is有什么样的FileSystemWatcher的SQL Server的表？

http://msdn.microsoft.com/en-us/库/ 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-表更改

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

我的code：

在主让准备将昏迷preD与日后的第一个校验和值。这些都是全局变量

'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

正如你可以看到，如果他们匹配校验更改为它发生，他们将是相同的，除非数据库确实改变下一次匹配等等。我已经停止，然后重新启动，以避免消息框混乱的时间，但是用于调试目的，一个事件可以在这里解雇或什么都code是想如果数据库改变发生的消息框。

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中有一种叫做OracleDependencyClass如果选择的数据已被修改，它提供一个应用程序的通知。我将提供有一些例子，如何使用这些，希望有人并不需要反映我自己的研究的基础部分超链接。

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.

发展与数据库更改通知应用程序

OracleDependency类

甲骨文对于®.NET开发人员指南数据提供商 - OracleDependency类（2）

<一个href=\"http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleDependency.html\"相对=nofollow称号=示例>在C＃中使用类的实例和VB.NET

如果这些页面不帮还有很多其他的网页，你可以访问，如果你无论是搜索神谕依赖，OracleDependency类和数据库更改通知。

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".

这篇关于如何确定是否数据库已经改变了访问，SQL，Oracle或文件系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！