[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

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
0
mark_norge
Asked:
mark_norge
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now