Multi-user program issues

[AK47]

I am creating a business software package in visual basic.net that will handle invoices, quotes, purchase orders, inventory items, customers, etc. This program is multi-user meaning that different people could each be running an instance of the program on their computer, with each instance modifying a central database. The problem is that one instance of the program could for example add a new inventory item, then how would I make the other instances of the program update their inventory lists to recognize the change? The program works with a dataset that it stores locally to avoid extra calls to the central database, so how do I tell the program when to update its local dataset in one instance when another instance of the program made a change to the central database?
I hope that didn't confuse anyone. Any help is appreciated, I love examples :)
Thanks!

[Comatose]

Well, since I don't have much of your code, I'm going to throw a few concepts at you. I'm not sure how your network works, or if you are comfortable with the idea... but think of this.... you could create a server program that sits on the same computer as the database you are working with...and when there is an update to the database, the client will alert the server, then the server, will alert all the other clients. The problem you are going to find is that when all the other clients are told about the update, and they query your database, it's going to be hit with all these clients at once. The best way with that would be to have the client that updates the database tell the server program exactly what change was made, and then send the change to all the other clients. The clients then make the update to their local databases. You are still looking at limitations by way of the number and amount of changes that can be made at once.... You could tell each client program to wait a certain amount of time, and stagger that among all your clients so that the toll on the database isn't that immense. I'm not sure about your limitations on bandwidth either... so that might be a bad idea (using a central server).

That's how I'd do it. I'd build a central server, and have that server monitor the updates in the database. when the database gets updated, the client program sends a notification to the server prog. The server prog sends a notification to the first client, and then when that client is done with it's update, the server prog sends a notification to the next client connected. When that client is done with it's update, it sends a notification to the next client....etc, etc. Problem with this, is the time it may take for a client to update. If you have a lot of clients connected, it might take a really long time from the moment that client1 updates, and client20 updates.

You could have your client app check the size of the database (if it's on a mapped drive, or something nuts) against the size of it's own every so often (in a timer), and if they are different then there must have been a change.... this way isn't very effective though, since the size of the database doesn't have to change when something in the record is changed, and you wouldn't know which record was updated.

Each Method has it's own positives and negatives... (“It has been counted and counted, weighed and divided"). So, let me know what you come up with. If you can find a more effecient method beyond any of these, please... let me know for my own personal future usage.

[AK47]

Concepts are a great place to start :) I am willing to look into a server/client setup that would notify all the clients when an update takes place. I won't necessarily have access to the server computer, and the program will be able to connect to a number of different databases, whether Microsoft SQL, or PostgreSQL on Linux, so I wouldn't be able to have a server program reside in the same place as the database. I think you are going in the right direction though, perhaps I could create a lightweight client/server module in the program itself, that way for example if client1 updates the database, it could send a message to all the other clients detailing what exactly they need to refresh in their local datasets? I think it is possible to have such a module within the program that listens on a certain port, then in order to find the other clients I could broadcast a message to that port on each computer and they'd receive the message?

A timer is an interesting idea; perhaps I could set a flag in a system table or something that would have a timestamp and what was updated whenever a client updates the database. Each client could store a local timestamp when they last updated their dataset and on a timer check the system table to see if their timestamp is outdated, and if so refresh whatever needs refreshing. I guess this would require many extra requests to the database, since the timer would have to be every 5 seconds or so in order to be effective.

It all sounds good in theory, but we'll see how it works in practice. I think I'll try the client/server model first, do you have any idea how I might get started with that, or where to look to get started?

This is a requirement for my program so I'll need to have something working. It may take me some time, but I'll post back here with results when I am finished with it.

Thanks for the help!

[Comatose]

that's a good place to start.... if you need any help with it, just post, or let me know.... I'll see what I can't do.

http://www.vbip.com/winsock/winsock_control_ssahmed_01.asp

[AK47]

Any idea how I might use the System.Net namespace to broadcast a message to the other programs running on a given network. How can I send some string data from a tcpclient to any given number of tcplisteners on the network? Can I broadcast the data on the network, or is there a way to get a list of IP addresses for all the tcplisteners on the network, so that I could loop through and send the data to each listener?
Thanks.

[Comatose]

If you are trying to do so client to client you are going to need the IP address of the other clients. You can do it with a list... like, save a text file with IP addresses, 1 per line, and read in each line of the file (each IP) into an array... then when you need to broadcast the info, loop through the array, and send it to each client..... or if you have a central server, you can just loop through all the connected sockets and post the broadcast. Never messed with System.Net, and since I use sockets at the lowest level I can find, I would never try to move to an .net oo for sockets.... but that's just personal preference.

[AK47]

Ok, The light bulb just turned on! :idea: :cheesy: I have a user accounts table that is used to authenticate anyone logging in to the system. The table stores name, password, and some default user settings such as font type, color, etc. It just occurred to me: why not store one more piece of information in the user records, an IP address!! The IP address would be set when the user successfully authenticates and would be the IP of the machine they are using. So that is where I'll find my list of IP addresses to loop through and send updates to (thank you for the suggestion of looping through an array of IP's, pointed me towards the user accounts table). Now as for sending the text message, I came across a package that will do just that, see: http://www.codeproject.com/useritems/UnoLibsNet_V2.asp
I don't see where the package follows any standards, and it is not completely functional, but the source code is provided along with examples and it does what I need: gets the local IP address and sends a text message from one IP to another. I'd like to take the concepts in it and completely overahul the code for better flexibility and functionality, but that will be later.
So that's it, problem solved!!
Thanks for the help!

[Comatose]

My Pleasure. You need any other help, I'll be here frequently.

[gearup4it]

Hi AK47,

I am writing a similar Business Solution, and see no need for IP hopping??
ALL the Logic and Transaction Processing should be handled in the SQL Datalayer.
I have Independent User Control modules that run threads which handles Data Acquisitions.

Let me know if you need more details
Regards
GearUp4IT