Windows Forms semi-connected data-access best practice

[Ron Buckton]

I have looked around and have not found a best practice or pattern regarding
the use of data in a semi-connected multi-user windows form application.

What I have is this, a Windows Forms Client-Server application which ties in
to Microsoft SQL Server 2000. Currently the application acquires data on
clients from the database when it loads so that the end user can work on
said data. When changes are made they are made to the dataset to track
changes and properly manage new record creation. When the user exits, or
clicks save, changes are submitted using a variety of DataAdapters to
properly hierarchally update the data.

I believe though that although this has worked for single-user systems, this
is not the best practice for a multi-user system as data can become
inconsistent as changes are made by each application.

Would it be better to:

* Transmit transacted changes immediately to the database once the DataSet
can perform its constraint checks, rather than wait until the user clicks
"Save"

* Find or put together a means to constantly check for updates to actively
changing and highly used tables (such as clients) and freshen the data as
soon as is possible.

I would rather this application be as connected as possible to the database
server, and that updates be as real-time as possible. I have seen "some"
examples of a semi-connected application using a web-service and performing
some type of polling for connectivity however I do not wish to expose the
business logic as a web service at this time. None of the examples explain
how to manage a consistent state for real-time or pseudo-real-time data.

--
Ron A. Buckton, MCP
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
President
Chronicles Design
Url: http://www.chroniclesdesign.com

 

[William \(Bill\) Vaughn]

See >>> comments

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I have looked around and have not found a best practice or pattern regarding
the use of data in a semi-connected multi-user windows form application.

What I have is this, a Windows Forms Client-Server application which ties in
to Microsoft SQL Server 2000. Currently the application acquires data on
clients from the database when it loads so that the end user can work on
said data. When changes are made they are made to the dataset to track
changes and properly manage new record creation. When the user exits, or
clicks save, changes are submitted using a variety of DataAdapters to
properly hierarchally update the data.

I believe though that although this has worked for single-user systems, this
is not the best practice for a multi-user system as data can become
inconsistent as changes are made by each application.

a.. How "active" is the database? How many times will clients be accessing data created by and updated by other active clients? If the database is segmented so a client is responsible for a clearly defined subset of the database, the chances others will write changes to the database that affect other client's work is remote. In this case (a "collision avoidance" scenario), it's pretty easy to handle collisions--the client that owns the data is the only one permitted to change anything and others might be able to query/read the data, but not change it.
b.. In other designs where clients are constantly competing for resources shared by the database (airline reservation systems are an example), collisions are a way of life and you'll need complex mechanisms to constantly requery the data or setup something akin to Notification Services to inform clients that data they're interested in (or responsible for) has changed.
I prefer to design systems where collisions are avoided in the first place--like setting up intersections that prevent cars from crashing into one another--instead of figuring out where to place the emergency vehicles. This is not always possible, but it makes your implementation far simpler and easier to code and support.
In the second scenario, yes, it's important to keep the database current so you won't have the luxury of holding on to newly entered data for long. You'll also need to requery so your client has the most current data values. While server-side cursors could deal with some of these issues, they also cached data on the client so you were still working with stale data to some extent. They also didn't automatically refresh the client cache when the data changed on the server.
Using a Web service would not (in my opinion) make this much better as it does not know any more than any other query if the data has changed. Notification Services can help in some designs, but it's overkill for designs that could have been implemented more simply by segmenting the data.

I'm available for private consulting if that appeals to you.

hth