How to make sure datasource and datatable are updated for application with multiple users

Hi Experts,

I have a project with a central application (used by multiple users) and a decentral application (each used by one user). The applications communicate by xml files. In the central application booking lines are created and these are send to the decentral application.

The central and decentral application both have an SQL datasource with BookingID (primairy key with auto numbering) and BookingNumber column. I have used FillSchema to make sure the BookingID is always unique.

The BookingNumber column must also be unique. Most BookingNumbers are created by the central application but I also receive BookingNumbers from the decentral applications. Therefore I can not use autonumbering. The solution I have choosen (with help from experts-exchange) is to first update, empty and fill the datatable and then add 1 to the highest found BookingNumber during the save operation. After creating the row I again update the SQL datasource to make sure the highest BookingNumber is available for other users.

I have not tested the method above with multiple users, but I believe it should work. However it seems like a lot of roundtrips to the SQL datasource. My question therefore is: is this the normal way of working or is there a better solution?

Regards, MB
mark_norgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

CodeCruiserCommented:
You can reduce it to one server trip by using a stored procedure. Just pass the data to the stored procedure which should perform all the validation and testing locally and then insert the record. The store procedure can then return you the BookingNumber as well which you can use to update the in memory datatables rather than reloading everything.
0
mark_norgeAuthor Commented:
Hi CodeCruiser,
I have never used a stored procedure. Your solution sounds like a "standard" way of doing this. If so, can you please provide me with a code sample that illustrates your solution.
Thanks in advance, Mark
0
CodeCruiserCommented:
Following is an example of a stored procedure
ALTER PROCEDURE [dbo].[AddUser](@UserID int, @UserName VarChar(50))
AS
SET NOCOUNT ON
BEGIN
    INSERT INTO dbo.Users
      (
      UserID,
      Name
      )
    VALUES
      (
      @UserID,
      @UserName,
      )
END
RETURN

and you can use it like this

dim dbcon as new sqlclient.sqlconnection("connectionstring")
dbcon.open
dim dbcmd as new sqlclient.sqlcommand("AddUser")
dbcmd.commandtype=CommandType.StoredProcedure
dbcmd.parameters.addwithvalue("UserID", 1)
dbcmd.paramters.addwithvalue("UserName", "CodeCruiser")
dbcmd.connection=dbcon
dbcmd.executenonquery()
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
mark_norgeAuthor Commented:
Hi CodeCruiser,
Thanks a lot for your answer. I have been experimenting with it and have it working!
0
CodeCruiserCommented:
Glad to help.
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
Visual Basic.NET

From novice to tech pro — start learning today.