By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

single transaction over multiple databases

P: 72

I want to transfer a user record from one database to another database. I want to do that within a single transaction to maintain data integrity, ie INSERT record to db1 and DELETE record (same data) from db2. Can someone help me with the code?

I am using c#.

Thanks in advance

If I have two databases, doesn't it means I have two connections which in turn means two transactions?

Expand|Select|Wrap|Line Numbers
  1. conn1 = new MySqlConnection(db1);
  2. conn1.Open();
  3. tran1 = conn1.BeginTransaction();
  5. conn2 = new MySqlConnection(db2);
  6. conn2.Open();
  7. tran2 = conn2.BeginTransaction();
I don't know how to combine the two tasks into one transaction.
What I know is the following

Expand|Select|Wrap|Line Numbers
  1. database1
  3. conn = new MySqlConnection(db1);
  4. conn.Open();
  5. tran = conn.BeginTransaction();
  7. MySqlCommand cmd = new MySqlCommand();
  8. cmd.Connection = conn;
  9. cmd.Transaction = tran;
  10. cmd.CommandText ="INSERT INTO UserTbl (UserID,Name) Values(?ID,?Name)";
  11. cmd.Parameters.Add("?UserID",MySqlDbType.String).Value ="1";
  12. cmd.Parameters.Add("?Name",MySqlDbType.String).Value = "x";
  14. cmd.ExecuteNonQuery();
  16. change to database2 ??
  18. cmd.CommandText="DELETE FROM UserTbl WHERE UserID='1'";
  19. cmd.ExecuteNonQuery();
  21. tran.Commit();
Feb 28 '13 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,035
"If I have two databases, doesn't it means I have two connections which in turn means two transactions?"

Yes, you will have two transactions.

1) Read record from db1 and lock it, if you want nobody to change this, this will start transaction1
2) Insert this into db2, this will start transaction2
3) If this fails, rollback all transactions
4) If step2 was OK, commit step2, and DELETE the record from db1, which should sucess because you already locked that record.
5) commit transaction1
Mar 2 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.