CODEWITHSUNDEEP
c#.netmysql
Dave
Dave

Reputation: 1076

Inserting into two tables with C# and MySQL

I'm currently writing a webapp in C#, and it requires me to gather new user information on a signup form. The information needs to be entered into two seperate tables, one for user information such as their Name, Email address and so on. The other for company information such as company name, website URL etc.

I'm using the MySQL Connector for .NET, and have been writing parameterized queries using MySqlCommand.

What I need to do, is insert the users information into the User table, then create a record in the Accounts table and link the two together by their ID's.

So the user table would resemble:

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

The company table would resemble: 

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

I considered using the LAST_INSERTED_ID() MySQL function, however I am worried that using that could mix two records if two people registered at once.

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

Thanks,

Dave

Upvotes: 4

Views: 936

Answers (2)

Jorge Villuendas Zapatero
Jorge Villuendas Zapatero

Reputation: 2330

According to the MySQL documentation LAST_INSERTED_ID() returns the last ID generated on a per-connection basis, so two different users, registering at the same time, will get different IDs.

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

Upvotes: 1

ngduc
ngduc

Reputation: 1413

I think you should use Transaction to wrap up those two INSERTs. That way will make sure both of them get executed together.

Upvotes: 1

Related Questions