Insert into two tables with C # and MySQL

Question

I am currently writing webapp in C # and I need to collect new user information in the registration form. Information must be entered in two separate tables, one for user information, such as name, email address, etc. Another for company information, such as company name, website URL, etc.

I use the MySQL Connector for .NET and write parameterized queries using MySqlCommand.

What I need to do is insert the user information into the User table, and then create an entry in the Accounts table and link them together by their identifiers.

So, the user table will resemble:

UserID | Username | Password | EmailAddress | AccountID 11 Dave 1234 Em@il 14 

The table of companies will resemble:

 AccountID | CompanyName | WebsiteURL | UserID 14 MyCo my.com 11 

I reviewed the use of the MySQL LAST_INSERTED_ID () function, however, I am concerned that using this can mix two records if two people register at once.

I would appreciate any thoughts on this from you, knowledgeable people.

Thanks,

Dave

Answer 1

I think you should use Transaction to wrap these two INSERTs. Thus, make sure that both of them run together.

Answer 2

According to the MySQL documentation, LAST_INSERTED_ID () returns the last identifier generated for each connection, so two different users logging in at the same time will get different identifiers.

Also, as ngduc says, it would be better to use a transaction to make sure both records are inserted.