Muliple User Database Application

you do not need to change your application my friend
what you will need to do is add to your SQL and create Race conditions

if you are unfamiliar with what a race condition is this should explain it clearly
http://www.celticwolf.com/blog/2010/04/27/what-is-a-race-condition/

There are many different possible answers to your question. Here are a few ones.

Build the application as web sites such as Amazon are built, even if you are writing a Windows application.

Get as little data as possible at one time. From what I see from your code, there is absolutely no filter on the Select, meaning that you are bring in a lot of stuff that the user might not need.

When possible, get only one record at a time. Because of the way old databases where working differently, many programmers load a table or a subset of a table and display only one record at a time. If they need to move through records with something like Next and Previous buttons, use the click event of the buttons to update the current record and then fetch the next one from the database. That way, you minimize the time that the data spends in the application before the update, and the user always see a very recent version of the data. Current databases are a lot faster than older ones, and this is very quick. In fact, it is often faster that moving through a DataTable, because DataTables can be pretty huge and force the system to use the swap file.

When possible, I would rather go for a ComboBox, let them chose and bring only the data they choose. That way they always have fresh data. Very often, if the users needs or prefer a grid to select something, I would not let them edit right in the grid. When they click somewhere in a row, I go fetch the current data for that row in the database and let them edit it in only a Form.

Same as web sites do, manage changes made by two different users by learning the rules of working with concurrency (http://msdn.microsoft.com/en-us/library/cs6hb8k4(v=vs.80).aspx).

Think what happens when you order something at Amazon. When you call an item, they tell you how many are still in stock. But you get a confirmation only when you actually place the command. I have seen instances where they still had 4 items in stock when I called the product, but it ended up being backordered when I finally completed the order.

Since ADO.NET does not let you lock a record as old systems did (and there are a lot of good reasons for that, a topic for another discussion), you have to react after the fact when 2 users change the same piece of data. In the case of Amazon, they wait until the order is passed and check the quantity only at that time to confirm or not the immediate delivery of each item.

One thing I often use is a timestamp field. If you are not familiar with that type of field, it is updated automatically by SQL Server each time that a change is made to a record. So if you read the TimeStamp in your SELECT and somebody changes the row while the user is editing the same data, you can detect that situation with a simple WHERE clause on the UPDATE because the TimeStamp you have in your own record does not match the current TimeStamp for the row in the database. It's up to you to react the way you want at that point.

A final note: most programmers go overboard with that problem. It was so easy to simply lock a record while editing in the past, that locking was almost automatic. One of my first project in .NET was to make a conversion from VB6 to VB.NET on an application that used an Access database that contained the limit of 255 tables once. In VB6, locking was used almost everywhere. I did not want to have to work on concurrency on that level, so we made a good analysis of the database and ended up doing on only... 1 (one) table.

We found out that many tables that were locked in the VB6 applications were used by only one user. No need to work on those. For instance, only one user was adding and deleting items in the products table, and the same user was also the one that changed the basic price of an item when needed. What are the chances that this person would be updating the same item from 2 different applications at the same time? Infinitesimal, specially since he could not start 2 instances of the application.

At first view, it was impossible not to try to detect conflicts between users on a table such as the customers table. But in the end...

We had 60000 customers in the table. What are the chances that 2 employees will be changing data on the same customer at the same time. If it goes, it might well be the same data. I really do not think that one employee would be changing the phone number while another one is changing the address. The chances of something like that arising are so minimal, and the problem detection and corrections would be so easy to do manually in the very few instances that something like that would occur that we did not manage multi-users on the customer table. It's been 6 years, and as far as we can tell, it happened only once. Balancing that against the time I would have had to build something, somebody that is paid a lot less than me made the correction in a couple of minutes by calling the customer.

The only table that we decided to check for concurrency issues was the billing table. Very bad image if we were to bill a customer with somebody else order.

Hi JamesBurger,

Thank you very much for your response.

I currently load the data from the database into a dataset and then allow the user to scroll through the data using a BindingNavigator.

I do not understand how I can allow the user to scroll through the table (filtered) if I am only returning one record at a time to my database... Please could you provide an example?

Thanks, Greg

First of all, it looks to me as if what you are doing has only one table. A DataSet is a container for multiple DataTable linked together with DataRelations. If you have only one table, or if you have many tables but they are not linked, drop the DataSet and use only standalone DataTable. Unfortunately, there are too many bad examples of the use of DataSets on the net, and it propagates.

Unfortunately, I do not have simple code ready. And I do not have the time to write the code. But its really simple, you can use mostly the same code that you already have, but by fetching the records one by one, as needed, by adding an ID field parameter to your Select command and stored procedure. You can replace the BindingNavigator with standard or custom buttons if you want.

Have a first query that retrieves the ID fields for the data that the use has to navigate through, sorted in the order that you want them to scroll. Leave that hidden from the user. This limits the amount of data in memory.

Call the database to fetch the complete data only the first ID in the list and display it in the form.

When the user moves forward, update the current record if it has been changed, and retrieve the data for the next ID in the list.

Do it backward when the user moves backward.

That way, you always have only one record in memory at a given time, which saves a lot of resources. And the record is always fresh, since you get it only at the time when the user is ready to look at it.

I think I understand, basically have two select queries. The first one will return all searched for records (e.g. where date = '2013-09-01') Result equals 17 records. I will use this DataTable to allow the user to scroll the records, when the next button is hit it will use the second select query which has a uniqueKey parameter and retrieve the up-to-date version of the record.

The next thing I would like to do is set a "read-only" flag if a user has a record open for read. For example user "A" is viewing record 1234, user "B" then open record 1234 but is prompted with a message, "Record in use by User A, Record is read-only" I would then set all the controls as disabled so that user cannot edit the record. Is this possible, would I have to create a "lock" table?

Thank you very much for your answers they have been extremely helpful

Sorry last question, Is there a simple way of refreshing a dataset or do I need to clear it and refill it?

Also, I have done as you mentioned and created two datatables, one with unique key to scroll through and one for the edits.

However, if I add a new row and then save it to the database is it possible to refresh the dataset and retrieve the record i have just added? Usually I would use the unique key to retrieve a record, however, as this is a new record I do not know what the unique key is...

There is a way to refresh a DataTable through its Merge method, but to do that efficiently, it requires more code than simply clearing and refilling. It would be something to look at if there was a great number of records because when done correctly, it would prevent you from having to bring back all the table from the database after each refresh. But from what you say (something like 17 in you list), and since you are fetching the whole data one record at a time, you would not gain any performance, so the extra work is not worth it.

So clear and refill is probably the best way to go in your situation.

To retrieve the newly created identity, you can query it in the INSERT stored procedure and return to the application either through an Ouput parameter, a return value or a SELECT at the end of the procedure. If it is an identity (autonumber) field, you can query @@IDENTITY after the INSERT to retrieve the last autonumber field generated on your connection.

Here is a sample from my ADO.NET training manual (sorry for the French).

Here is the stored procedure. Note the OUT in the parameters declaration, and the SET on the last line, that sets that parameter value to the last ID field generated:

PROCEDURE spAjoutPhoto ( @Titre nvarchar(75), @NoSujet int, …
@NoPhoto int OUT)
INSERT INTO tbPhotos( Titre , NoSujet… )
VALUES (@Titre , @NoSujet… )
SET @NoPhoto = @@IDENTITY

Select allOpen in new window

Here is the Command object used to call it. The lines that set the connection and the procedure are removed to concentrate on what is important, the Direction in the last line:

Dim comPhotos As New SqlClient.SqlCommand()
With  comPhotos
	.Parameters.AddWithValue("@Titre", strTitre)
	.Parameters.AddWithValue("@NoSujet", cboSujet)
	…
	.Parameters.Add(New SqlClient.SqlParameter("@NoPhoto",SqlDbType.Int))
	.Parameters("@NoPhoto").Direction = ParameterDirection.Output
End With

Select allOpen in new window

And after the procedure is executed, here is how I retrieve the new ID:

Dim no As Integer
no = comPhotos.Parameters("@NoPhoto").Value

Select allOpen in new window

For future reference, do you have an example of the "Merge Method" or know of a decent tutorial anywhere?

Thanks, Greg

http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx