Multi-User Issue (also posted in VB.NET forum)

[colttaylor]

I've taken over development of a Vs2003(vB)/MsSql2000 application which usually runs in on a Windows 2000 server with multiple workstations running the application simultaneously in Remote Desktop Sessions.

The central user interface element of the application is an un-databound grid (actually a farpoint spreadsheet) which displays sql table rows that are accessable from every workstation. The grid refreshes its content whenever a specific subroutine is called. That subroutine performs a query which loads its results into a collection which is then loaded into the grid. The subroutine is called during startup and at other times using the rather elaborate mechanism described below. Each workstation also has the ability to modify any row in the grid via a dialog-based editor. The grid itself is not setup to allow inplace editing.

The grid usually contains about 200 records and 20-50% of these records get modified (some repeatedly) every hour.

My question involves the classic multi-user issues of keeping all of the seperate workstation's grids current as individual workstations make modifications to the data.

What is Microsoft's recommended method for handling multi-user data-refresh requirements in custom applications written in VB.NET2003 with MsSQL2000?

Now that you know what I'm looking for, let me add a little more detail. The existing code handles this multi-user refresh issue in the following way...

A SQL Trigger is attached to the SQL Table's Insert/Modify and Delete events. That Trigger calls the xp_logevent built-in stored procedure to make an entry in the NT Event Log.
The application contains a subroutine to handle EventLog("Application").EntryWritten messages. That subroutine calls the subroutine (mentioned earlier) which refreshes the grid contents through a new SQL query.

In this way, whenever any workstation changes the table data, a trigger fires which makes a log entry and broadcasts an EntryWritten message into the global windows environment. The individual applications (each running in their own RDP session) hear the message and respond by refreshing their grid.

This all seems a little "rube goldberg" to me, but it has worked for many years.

That is, until we moved the SQL2000 server to a seperate machine to improve program responsiveness.
Now when the trigger is fired and the log entry is made, the EntryWritten message is broadcast into the windows environment of the dedicated SQL server where there are no applications listening for it. Meanwhile, the applications continue to run with unrefreshed data on the (now) dedicated RDP server from where the EntryWritten messages cannot be "heard".

So my question really comes down to this...

In an environment where the SQL server and the RDP server are two seperate machines, what is the best method for coordinating multi-user screen refreshes across seperate applications running in distinct RDP Client sessions?

Thanks in advance for any assistance.
Peace,
Colt Taylor
Computer Golf Sofware



If it's stupid but it works, it isn't stupid

 

[RiverGuy]

Honestly, I think a timer to refresh the grid at regular intervals is the cleanest, easiest and best solution. This way is probably more scalable should you want to run the application on users' own desktops instead of Terminal Services in the future.

 

[colttaylor]

You are probably right and at the scale that my app is currently running, that probably wouldn't be too much of a burden for the sql server or the network. Still it seems strange that there isn't an industry standard approach to handling this problem.

If I go with the timer approach, I will probably split the refresh query into two parts: one to test if anything has changed since the last refresh and the other to actually fetch the rows. This would minimize the network traffic at the cost of a slightly higher burden on the sql server. I would probably also make the timer interval user definable so that the application can be "tuned" to the available resources.

Thanks for the quick reply. If you have any other ideas, please let me know.

Peace,
Colt

If it's stupid but it works, it isn't stupid

 

[Auguy]

Just adding my two cents. Using a timer is exactly how I handled this situation for a trucking company's "Load" board grid. I refresh the screens every nn.n minutes where nn.n is stored for each screen in one of the tables. I am in the process of making this time configurable (right click on grid header) by the user with a minimum and maximum value. There are approx. 10 users with most of them running from their desktop and one terminal service user.

Auguy
Northwest Ohio