Muliple User Database Application

Hi,

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()

        'Connection
        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")


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

        'Dataset
        dsUsers = New DataSet()
    End Sub

    Public Function GetUsersInfo() As DataSet
        daUsers.Fill(dsUsers)
        Return (dsUsers)
    End Function

    Public Sub UpdateUsersInfo(ByVal dsChanges As DataSet)
        daUsers.Update(dsChanges)
    End Sub

End Class

Open in new window


Thanks, Greg
spen_langAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
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/
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
spen_langAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
spen_langAuthor Commented:
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?
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spen_langAuthor Commented:
Thank you very much for your answers they have been extremely helpful
0
spen_langAuthor Commented:
Sorry last question, Is there a simple way of refreshing a dataset or do I need to clear it and refill it?
0
spen_langAuthor Commented:
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...
0
Jacques Bourgeois (James Burger)PresidentCommented:
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

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

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

Thanks, Greg
0
Jacques Bourgeois (James Burger)PresidentCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.