Hi, I need to save user information on many tables as "User" "UserPersonalInfo" "UserWorkInfo" and so on.
All this informations must be stored on DB when user clicks Save button.
When user clicks the button I save the info on "User" table, which has an ID autoincrement, so I get the last ID inserted and I use this ID to save the other user info on the ohter tables.
I think this is not the best approach supposing 1000 users clicking same time the Save button the ID getting from Last ID inserted maybe is not the correct ID.
How can I deal with this situations? Store the info on multiples tables and assign to each user a unique ID.
Your link shows how to get a Guid in .net which would then get passed to the db. Depending on the situation, it might be more appropriate to let the db generate the guid.
declare @newID as uniqueidentifier;
set @newID = NEWID();
riestra
Member
40 Points
149 Posts
Save user information on many tables using user ID
Nov 17, 2012 04:28 PM|LINK
Hi, I need to save user information on many tables as "User" "UserPersonalInfo" "UserWorkInfo" and so on.
All this informations must be stored on DB when user clicks Save button.
When user clicks the button I save the info on "User" table, which has an ID autoincrement, so I get the last ID inserted and I use this ID to save the other user info on the ohter tables.
I think this is not the best approach supposing 1000 users clicking same time the Save button the ID getting from Last ID inserted maybe is not the correct ID.
How can I deal with this situations? Store the info on multiples tables and assign to each user a unique ID.
Thanks in advance!
Primillo
Star
8727 Points
1679 Posts
Re: Save user information on many tables using user ID
Nov 17, 2012 06:09 PM|LINK
Hi
Honestly I use the data type Guid for ID.
http://msdn.microsoft.com/en-us/library/system.guid.newguid(v=vs.100).aspx
Primillo
http://www.facebook.com/programandopuntonet
_Manvel_
Contributor
4240 Points
922 Posts
Re: Save user information on many tables using user ID
Nov 17, 2012 06:15 PM|LINK
As far as I know, if you use SCOPE_IDENTITY() to fetch newly inserted identity column value ( aka ID ) it's safe in highly concurrent environments.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Save user information on many tables using user ID
Nov 17, 2012 06:26 PM|LINK
Your link shows how to get a Guid in .net which would then get passed to the db. Depending on the situation, it might be more appropriate to let the db generate the guid.
declare @newID as uniqueidentifier;
set @newID = NEWID();
then do things with @newID.
Primillo
Star
8727 Points
1679 Posts
Re: Save user information on many tables using user ID
Nov 17, 2012 09:56 PM|LINK
Hi
In some scenarios you need to insert data in more than one SQL Tables at once (same button click).
Or in others you need to redirect the users with the new ID to another page, so I use session to save the new guid (created in code behind)
So if you create the guid in code behind you can do more.
Primillo
http://www.facebook.com/programandopuntonet
ramiramilu
All-Star
95413 Points
14106 Posts
Re: Save user information on many tables using user ID
Nov 18, 2012 08:39 AM|LINK
write a stored procedure and with Row lock and scope identity, there shouldnt be any problem...
thanks,
JumpStart
riestra
Member
40 Points
149 Posts
Re: Save user information on many tables using user ID
Nov 18, 2012 05:07 PM|LINK
Please, can you provide an example?
_Manvel_
Contributor
4240 Points
922 Posts
Re: Save user information on many tables using user ID
Nov 18, 2012 05:20 PM|LINK
http://developergeeks.com/article/260/how-to-return-last-inserted-identity-column-value-in-sql-server
riestra
Member
40 Points
149 Posts
Re: Save user information on many tables using user ID
Nov 18, 2012 05:41 PM|LINK
And waht about Last_insert_ID?
_Manvel_
Contributor
4240 Points
922 Posts
Re: Save user information on many tables using user ID
Nov 19, 2012 09:56 AM|LINK