Muliple User Database Application

Posted on 2013-09-03
Medium Priority
Last Modified: 2013-09-05

I am creating a database application where more that one user may be access it at the same time. For the backend I am using MSSQL 2008.

I have connected to the database and performed updates using dataAdatpers and datasets as a single user but please could you give me some advice on how I will need to change my application so that it can handle muliple users?

E.g. ensure that two users do not attempt to update the same record at the the same time, perform some sort of locks when a user has a record open for edit.

How often and how should I refresh my datasets so that the users always have the latest data?

I am currently using the following code to access the database and then binding the data to controls on my form.

Imports System.Data.SqlClient
Public Class dataUserAccess
    Private cnKPI As SqlConnection
    Private daUsers As SqlDataAdapter
    Private cmdSelectUsersInfo As SqlCommand
    Private cmdUpdateUsersInfo As SqlCommand
    Private cmdInsertUsersInfo As SqlCommand
    Private cmdDeleteUsersInfo As SqlCommand
    Private dsUsers As DataSet

    Public Sub New()

        cnKPI = New SqlConnection("")

        'select command
        cmdSelectUsersInfo = New SqlCommand
        cmdSelectUsersInfo.Connection = cnKPI
        cmdSelectUsersInfo.CommandType = CommandType.StoredProcedure
        cmdSelectUsersInfo.CommandText = "proc_UserAccess_All"

        'update command
        cmdUpdateUsersInfo = New SqlCommand
        cmdUpdateUsersInfo.Connection = cnKPI
        cmdUpdateUsersInfo.CommandType = CommandType.StoredProcedure
        cmdUpdateUsersInfo.CommandText = "proc_UserAccess_Update"

        cmdUpdateUsersInfo.Parameters.Add("@UserID", SqlDbType.VarChar, 50, "UserID")
        cmdUpdateUsersInfo.Parameters.Add("@Username", SqlDbType.VarChar, 50, "Username")
        cmdUpdateUsersInfo.Parameters.Add("@InvoiceAccuracy", SqlDbType.Bit, 1, "InvoiceAccuracy")
        cmdUpdateUsersInfo.Parameters.Add("@EmployeeAccidentRate", SqlDbType.Bit, 1, "EmployeeAccidentRate")
        cmdUpdateUsersInfo.Parameters.Add("@Settings", SqlDbType.Bit, 1, "Settings")
        cmdUpdateUsersInfo.Parameters.Add("@Users", SqlDbType.Bit, 1, "Users")
        cmdUpdateUsersInfo.Parameters.Add("@Active", SqlDbType.Bit, 1, "Active")
        cmdUpdateUsersInfo.Parameters.Add("@UniqueKey", SqlDbType.Int, 1, "UniqueKey")

        'insert command
        cmdInsertUsersInfo = New SqlCommand
        cmdInsertUsersInfo.Connection = cnKPI
        cmdInsertUsersInfo.CommandType = CommandType.StoredProcedure
        cmdInsertUsersInfo.CommandText = "proc_UserAccess_Insert"

        cmdInsertUsersInfo.Parameters.Add("@UserID", SqlDbType.VarChar, 50, "UserID")
        cmdInsertUsersInfo.Parameters.Add("@Username", SqlDbType.VarChar, 50, "Username")
        cmdInsertUsersInfo.Parameters.Add("@InvoiceAccuracy", SqlDbType.Bit, 1, "InvoiceAccuracy")
        cmdInsertUsersInfo.Parameters.Add("@EmployeeAccidentRate", SqlDbType.Bit, 1, "EmployeeAccidentRate")
        cmdInsertUsersInfo.Parameters.Add("@Settings", SqlDbType.Bit, 1, "Settings")
        cmdInsertUsersInfo.Parameters.Add("@Users", SqlDbType.Bit, 1, "Users")
        cmdInsertUsersInfo.Parameters.Add("@Active", SqlDbType.Bit, 1, "Active")
        cmdInsertUsersInfo.Parameters.Add("@CreateDate", SqlDbType.DateTime, 1, "CreateDate")
        cmdInsertUsersInfo.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 50, "CreatedBy")

        'Delete command
        cmdDeleteUsersInfo = New SqlCommand
        cmdDeleteUsersInfo.Connection = cnKPI
        cmdDeleteUsersInfo.CommandType = CommandType.StoredProcedure
        cmdDeleteUsersInfo.CommandText = "proc_UserAccess_Delete"
        cmdDeleteUsersInfo.Parameters.Add("@UniqueKey", SqlDbType.Int, 1, "UniqueKey")

        daUsers = New SqlDataAdapter
        daUsers.SelectCommand = cmdSelectUsersInfo
        daUsers.UpdateCommand = cmdUpdateUsersInfo
        daUsers.InsertCommand = cmdInsertUsersInfo
        daUsers.DeleteCommand = cmdDeleteUsersInfo

        dsUsers = New DataSet()
    End Sub

    Public Function GetUsersInfo() As DataSet
        Return (dsUsers)
    End Function

    Public Sub UpdateUsersInfo(ByVal dsChanges As DataSet)
    End Sub

End Class

Open in new window

Thanks, Greg
Question by:spen_lang
  • 6
  • 5
LVL 22

Expert Comment

ID: 39461041
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
LVL 40
ID: 39462425
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.

Author Comment

ID: 39462889
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 40
ID: 39462939
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.

Author Comment

ID: 39462966
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?
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 2000 total points
ID: 39464291
You seem to get it in your first paragraph.

For the flag, this is something I did in the billing table mentioned at the end of my first post. No need to create a lock table, you can do it directly in the table that contains the data.

I implemented that with an extra text field in the table, (lets call it LockFlag) that contains an empty string by default.

I added a parameter to the store procedure for the Select to accept the user name. That stored procedure had 2 SQL commands in it. It first checked if LockFlag contains an empty string and if so, recorded the name that was passed. The SELECT command then returned the necessary data along with the content of the LockFlag.

In the application, when returning from the Select, I checked the LockFlag. If it was my user name, then everything was OK. If it was somebody else name, then somebody was already in the record and I reacted accordingly.

When your user leaves the record, just make sure to send an update that will put back a Null in that field to free the record for somebody else. You should also be sure to trap Exceptions all the way and free the LockFlag in case of an exception so that it does not stay "locked". If this ever happen, you can edit the table manually in the database, or simply have the same user go in an out of the record to reset the LockFlag.

It might be nice to have a Retry or Refresh button on your form so that your user can check from time to time to know when the other user has finished. This could be automated through a Timer to make it automatic.

Author Comment

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

Author Comment

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

Author Comment

ID: 39464489
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...
LVL 40
ID: 39465115
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… )

Open 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

Open 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

Open in new window


Author Comment

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

Thanks, Greg
LVL 40
ID: 39466317

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question