single transaction over multiple databases

[dragon52]

Hi,

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?

conn1 = new MySqlConnection(db1);
conn1.Open();
tran1 = conn1.BeginTransaction();

conn2 = new MySqlConnection(db2);
conn2.Open();
tran2 = conn2.BeginTransaction();

I don't know how to combine the two tasks into one transaction.
What I know is the following

[I][U]database1[/U][/I]

conn = new MySqlConnection(db1);
conn.Open();
tran = conn.BeginTransaction();

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandText ="[B]INSERT[/B] INTO UserTbl (UserID,Name) Values(?ID,?Name)";
cmd.Parameters.Add("?UserID",MySqlDbType.String).Value ="1";
cmd.Parameters.Add("?Name",MySqlDbType.String).Value = "x";

cmd.ExecuteNonQuery();

[I][U]change to database2 ??[/U][/I]

cmd.CommandText="[B]DELETE[/B] FROM UserTbl WHERE UserID='1'";
cmd.ExecuteNonQuery();

tran.Commit();

[Luuk]

"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