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

Posted on 2008-11-19
Last Modified: 2012-05-05
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
Question by:mark_norge
    LVL 83

    Expert Comment

    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.

    Author Comment

    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
    LVL 83

    Accepted Solution

    Following is an example of a stored procedure
    ALTER PROCEDURE [dbo].[AddUser](@UserID int, @UserName VarChar(50))
        INSERT INTO dbo.Users

    and you can use it like this

    dim dbcon as new sqlclient.sqlconnection("connectionstring")
    dim dbcmd as new sqlclient.sqlcommand("AddUser")
    dbcmd.parameters.addwithvalue("UserID", 1)
    dbcmd.paramters.addwithvalue("UserName", "CodeCruiser")

    Author Closing Comment

    Hi CodeCruiser,
    Thanks a lot for your answer. I have been experimenting with it and have it working!
    LVL 83

    Expert Comment

    Glad to help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now