Refresh ADO dataset when underlying table is updated

Question

Is it possible to have an ADO Dataset that 'knows' if or when the underlying table has been updated?

At the moment I have to check an item status each time I receive a file for that item in a folder. This results in a lot of sql queries as we get up to 1300 events at a time.

I'm thinking to add a ADO Dataset to the application which holds the status and use Locate to check the status.

Instead of having a regular refresh with say a timer, I'd like the dataset to be updated as soon as the underlying table records are changed.

Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:28

ADO Dataset retrieve data to client based on Client / Server concept. Like a web browser.

That means: Client Request -> Server, Server Answer -> Client. So Client really can't know if any data has been changed.

Simplest way is to requery data (Close/Open) and fetch data by your needs, not all 1300 at once. This is most used solution.

Anyway, if amount of data is really BIG and you want to play with optimization You have two ways:

A. Build a log table and register table data changes using triggers. Periodically ask server for changes (you may do this in a background thread):

select L.RECORD_ID, L.OPERATION_ID
from FILE_LOG L
where L.FDATESTAMP between :LAST_WATCH and :CURRENT_STAMP and L.FOLDER_ID = :FOLDER_ID

You will get RECORD_ID and OPERATION_ID (ie insert/update/delete).

B. If you are not linked to DBMS, Firebird/Interbase have events concept. Using triggers you can inform client that data has been changed, and you can requery just modified data.

I like option B because I would respond to a database event, fired by a trigger on the event table, rather than having to regularly check if something has changed. Thanks for the idea! — Pieter van Wyk, Jul 13 '12 at 14:48

score 0 · Answer 2 · answered Jul 13 '12 at 11:56

As far as we are aware, there is only one way in SQL Server to detect if any change has been made to a table and that is to use the Dynamic Management View sys.dm_db_index_usage_stats (http://msdn.microsoft.com/en-us/library/ms188755.aspx).

For my purposes, I created a function to make accessing the information easier.

create function [dbo].[_efnLastTableUpdateTime](@TableName varchar(255))
/*  Function to return the last datetime that a table was modified.
    Note that because this is from a dynamic management view, it is only
    since the server was started, i.e. will return null if no change since
    the server was started.
    SQL2005 or later.
*/
returns datetime
as
begin
    declare @Result datetime
    set @Result = (
            select top 1 [last_user_update]
            from sys.dm_db_index_usage_stats
            where object_id=object_id(@TableName)
            order by [last_user_update] desc
        )
    return @Result
end
GO

Then I built into my descendant of TADOQuery a function to access it and a property to switch the functionality on or off. I can then call this on demand which results in a very efficient response.

function TMyADOQuery.HasBeenUpdatedSinceOpen(const ACloseIfHasBeen: boolean = false): boolean;
const
  sSelectTableUpdateTime = 'select [dbo]._efnLastTableUpdateTime(''%s'')';
var
  NewUpdateTime: TDateTime;
begin
  Result := false;
  if(_TrackUpdated) and (Active) and (_TableName > '') then begin
    NewUpdateTime := TrackUpdateQuery.SelectScalarDate(Format(sSelectTableUpdateTime, [_TableName]), 0);
    Result := (FLastUpdateTime <> NewUpdateTime);
    FLastUpdateTime := NewUpdateTime;
  end;
  if(Result) and (ACloseIfHasBeen) then
    Close;
end;

TrackUpdateQuery is another instance of TADOQuery created internally and SelectScalarDate is and extension on my TADOQuery class.

Note that the user must have the VIEW SERVER STATE permission granted to be able to access the management view.

Refresh ADO dataset when underlying table is updated

2 Answers2