Avatar of spen_lang
spen_lang asked on

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
.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment
Jacques Bourgeois (James Burger)

8/22/2022 - Mon
plusone3055

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/
Jacques Bourgeois (James Burger)

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.
ASKER
spen_lang

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jacques Bourgeois (James Burger)

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.
ASKER
spen_lang

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?
ASKER CERTIFIED SOLUTION
Jacques Bourgeois (James Burger)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
spen_lang

Thank you very much for your answers they have been extremely helpful
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
spen_lang

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

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...
Jacques Bourgeois (James Burger)

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
spen_lang

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

Thanks, Greg
Jacques Bourgeois (James Burger)