Inserting into two tables with C# and MySQL

Question

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

you have a userid and accountid in both tables; is that necessary? could userid be left off the company table since you already have the foreign key, accountid, to point back to the correct account in the user table? — Zach Green
– Zach Green, Commented Mar 12, 2011 at 18:03
Actually, I hadn't thought about that. The UserID on the accounts table is actually pointless thinking about it now. — Dave
– Dave, Commented Mar 12, 2011 at 18:05

ngduc · Accepted Answer · 2011-03-12 17:58:44Z

1

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

answered Mar 12, 2011 at 17:58

ngduc

1,43112 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

Okay. So just to check my logic here, I would wrap the INSERT statements into a transaction, then I would run the Account INSERT first, bring back the LAST_INSERTED_ID() from that and use it to populate the User INSERT statement?

ngduc Over a year ago

correct: ...and use it to populate the Account INSERT statement.

ngduc Over a year ago

this is a good article which is similar to your problem - onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.html

Jorge Villuendas Zapatero · Accepted Answer · 2011-03-12 18:05:10Z

1

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.

answered Mar 12, 2011 at 18:05

Jorge Villuendas Zapatero

2,33016 silver badges15 bronze badges

Collectives™ on Stack Overflow

Inserting into two tables with C# and MySQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related