[2008] Keeping data up to date when other apps are modifying it

[chris128]

Hi Guys,

Just a fairly general question here about how to keep database driven app's on screen data up to date.

I'm connecting to an SQL server to retrieve information on which employee's are signed in or out of the building in our company. My program also modifies the SQL table to update when new employee's have signed in or out. This is all fine when my app is just being run on one machine (which is how its setup now) but I have been asked to set this up on another machine in another office, so both PCs will be running my app and both will be modifying the SQL table.
The on screen data at each PC needs to be as up to date as possible because if there is a fire in one office then the list of signed in employees shown on screen at the other PC is used as the fire register.

So yeah, is my only option to use a timer that just runs every minute or something and updates all of the data in my program to the most current or is there a better solution? (loading all of the SQL table data could take between 10 and 20 seconds when the network is going slow so I want to avoid having to do that every minute if possible)

[szlamany]

If it's MS SQL 2005 you can use something called Notification Services to have the server fire an event in code when something changes.

Is that what you are using?

[chris128]

Yeah, its the express edition of SQL server 2005 though, does that still have it?
I take it this process doesnt have any negative points? Doesnt impact performence noticably or anything?

Thanks

[szlamany]

I never used it - a MS webinar that I did a couple of years ago made it seem like it was somewhat like a trigger as far as how costly it was.

Here's a link

https://www.microsoft.com/sql/techno...n/default.mspx

There is a FAQ link on that site.

I am not sure it's part of EXPRESS though - there is some page at the MS site that talks about differences between the editions.

[chris128]

Cheers for the link, here's a quote from the FAQ:

Notification Services requires a SQL Server 2005 Standard or Enterprise Edition license. Licensing costs are based on SQL Server 2005 pricing, which varies depending on the deployment scenario

darn.
Anyone got any other suggestions?

Cheers
Chris

[szlamany]

Well...

The use of EXPRESS edition implies that you are running your app and the database on the same workstation - multi-user is always possible with EXPRESS of course.

If you were running single-user on a workstation then having the "service" notify the client or having a client-thread check for changes amounts to pretty much the same thing.

With that said put a trigger into your table that sets a "datetime" value in some other table that you can read from the client.

Whatever cycle you feel is reasonable go and run a query like:

Select Convert(varchar(23),LastUpdate,121) From LastUpdateTable
    Where LastUpdate>='xxxx-xx-xx hh:mm:ss.mmm'

Of course the 'xxxx...' date value is passed in by you with the last date/time you checked (actually the last date/time returned).

If a value is returned it will be the date/time of the last change. You could also use GETDATE() for a return value - but I'm afraid that you could have timing issues with this.

Use EXECUTESCALAR to run this query - since it returns a single row and a single value. The value returned will be used in the "next" check cycle as the value in those quotes.

If no value is returned the WHERE clause is what stopped it - so that means no update has happened.

I guess you would put that in a separate thread in your app - run it every 10 or 15 seconds (or whatever your experience tells you is reasonable).

Make sure you display the "date/time" value in your UI so that the users know that the display is based on values up to that time.

[chris128]

Sorry I'm probably just being stupid but I dont see the purpose of what you suggested. Surely that just gives me a date/time of when it was last updated, which is not really what I am after - I could do that just by updating a label in my program with the current time on each tick event of the timer that would start the data refresh.

I basically just wondered if there is any other way than having a timer pull the whole table accross again every minute or whatever. Like a method that would only need to pull accross data that had changed, instead of just grabbing everything and refreshing the whole table in my app.

Thanks

[szlamany]

The whole point is to only bring the "whole table" back when something has changed.

Why bother the network - cause screen refreshes - when nothing has changed?

That's the whole point of "notification services" as well. To tell the client that an "event" occurred and then let the client request the data needed to react to that event.

[chris128]

Ah right so basically you mean when one machine updates the table with all the data in, store the date and time in another table and then every minute or whatever both machines that my app is installed on will query that date/time table to see if the value in there is later than the date/time value that they last wrote to it with? (cos they obviously do a refresh of the data after they update the table)
Hope that makes sense, but yeah I think I see what you meant now

I will give that a go and get back cheers

[MaximilianMayrhofer]

Just out of curiousity, databases are stored as a number of files, even in MS SQL. Why not use the .Net FileWatcher to trigger events whenever the database file is changed, as it will be when people enter or leave.

[jmcilhinney]

Just out of curiousity, databases are stored as a number of files, even in MS SQL. Why not use the .Net FileWatcher to trigger events whenever the database file is changed, as it will be when people enter or leave.

You normally wouldn't know exactly which file relates to a specific database, particularly on a remote machine. Also, I would expect a data file could change without records being edited so you'd get false positives.

[chris128]

Yeah I was going to say, I think the files will get modified by general SQL server processes so we would get a lot of false positives. I'm going to try implementing the Last Update field/table that szlamany mentioned today and see if that works out alright

[MaximilianMayrhofer]

Yea that's a pretty excellent approach. I'll be taking advantage of that in an app i'm writing as well.

[szlamany]

Make sure you start with a GETDATE() from the server - to see what the time is on that machine.

And wrap that in Convert

Select Convert(varchar(23),GetDate(),121)

That will return your starting time - and you use that in your next check to see if an update occured.

Don't use local time of the workstation - it will be off compared to the GETDATE() of the server.

Also - keep in mind that although it increments as HH:MM.SSS - not every SSS is represented. They jump along skipping values (clock speed not as quick)

[chris128]

Ahh cheers thanks for that tip, I was just literally writing that bit now but I was using the date on the workstation.

[szlamany]

Select Convert(varchar(23),LastUpdate,101) From LastUpdateTable
    Where LastUpdate>='xxxx-xx-xx hh:mm:ss.mmm'

Oops - this 101 style I posted is wrong.

Should be 121 - sorry...

Select Convert(varchar(23),LastUpdate,121) From LastUpdateTable
    Where LastUpdate>='xxxx-xx-xx hh:mm:ss.mmm'

[chris128]

What exactly is the 121 for? I'm currently having a problem with writing to the SQL table (different thing) so I just need to get this sorted before I can carry on trying to implement what you suggested

[szlamany]

121 is a style of format that CONVERT will turn a date into

YYYY-MM-DD HH:MM:SS.MMM

which is 23 characters long - thus the varchar(23) as well.

By passing the date around between the SQL and the VB you will get a nice "date/time" value to use as your time stamp.

When you update your LASTUPDATE table use GETDATE() for that.

Update LastUpdateTbl Set LastUpdate=GetDate()

[chris128]

OK thanks for the extra info
I just finished getting the rest of my application working so now its purely keeping the on screen data up to date that is left to do.
I noticed in SQL Server Management Studio there is a section in each database labelled "Triggers" which sounds like it might be useful for this situation but when I try to add a new one, I just get this template and I have no idea what to do with it:

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END

If anyone has any experience with this then feel free to add some input but I think for now I will just stick to the LastUpdate method and see how well this works
EDIT: I've just realised, I already write the time to another table each time an update is made (along with some other data, this table acts as a log of when people signed in or out). So I could just query this table every minute and then do a refresh based on the result of that couldnt i

[szlamany]

You could certainly use a trigger - I've not done many of those myself. The nice thing about a trigger is that it will automatically change the last update without you adding extra logic to the client app.

If you are doing you sign-in/sign-out inserts in STORED PROCEDURES just add some additional query logic to do this.

We would need to know more about where you are doing your existing INSERT's and UPDATE's.

[chris128]

Hmm well I think I'll just stick to your first method I'm struggling to get it working though... it sounds simple in theory and I understand it when I read your first post but then when I try and implement it in my app I get stuck.

Basically at the moment I have one sub that loads all of the data from the database, then in the sub that writes updated data to the database I have added a command to write the server's current time to a table called tblLastUpdate.
Now every 30 seconds a timer tick event fires that uses the following code:

myutil2.DB.checkserverlastupdate()
 
        If Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Second, serverlastupdatetime, mylastupdatetime) > 10 Then
            myutil2.DB.loaduserstatus()
        Else
            MessageBox.Show("no update")
        End If
        lastupdatelbl.Text = "Last Refresh: " & myutil2.DB.servertime.ToString

The sub that the first line is calling just sets the variable named serverlastupdatetime to the value of the only cell in the tblLastUpdate table. Then obviously its just comparing the serverlastupdate time to the variable named mylastupdatetime.
This mylastupdatetime variable gets set each time that the data is loaded from the database, thats right isnt it?
If the time is found to differ by more than 10 seconds then the data is loaded from the database again and if not then a messagebox pops up (for testing purposes).
However, it ALWAYS pops up with the messagebox even if I go in and edit the tblLastUpdate data to a time 10 minutes from now.
Any ideas what im doing wrong?

Cheers
Chris

[szlamany]

Show me the query that's getting the last update date/time value from the DB.

[chris128]

Public Sub checkserverlastupdate()
        Try
            Dim lastupdatecmd As New SqlCommand
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter("SELECT LastUpdate FROM tblupdate", connection)
            connection.Open()
            da.Fill(ds)
            Dim rowcollection As DataRowCollection = ds.Tables(0).Rows
            serverlastupdatetime = rowcollection(rowcollection.Count - 1).Item(0)
            connection.Close()
        Catch ex As Exception
            connection.Close()
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
 
    End Sub

There are two columns in the tblUpdate table and only one row. This row just has the LastUpdate column set to the date the last update was done and the other column is just named ID and is set to a value of 1 in the only row in there.
I checked the value of serverlastupdatetime at runtime and it appeared to have the date from the SQL table in it. I just dont see where I'm going wrong.

Thanks again for your time

[szlamany]

For speed and less drain on the server I suggested using an EXECUTESCALAR of the SELECT statement

Select Convert(varchar(23),LastUpdate,121) From LastUpdateTable
    Where LastUpdate>='xxxx-xx-xx hh:mm:ss.mmm'

In the quotes you would place the last "date/time" that was returned.

If the execute scalar returns nothing then the WHERE clause indicates no new entry. If a string is returned it will be the date/time of the last update and you then use that in the next "string build" for the above query.

[chris128]

Ah yeah, I did originally set it up using the ExecuteScalar method but then changed it for some reason (which I cant remember now :S)
I'll try what you suggested and get back to you, thanks