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
- conn1 = new MySqlConnection(db1);
- conn1.Open();
- tran1 = conn1.BeginTransaction();
- conn2 = new MySqlConnection(db2);
- conn2.Open();
- tran2 = conn2.BeginTransaction();
What I know is the following
Expand|Select|Wrap|Line Numbers
- database1
- conn = new MySqlConnection(db1);
- conn.Open();
- tran = conn.BeginTransaction();
- MySqlCommand cmd = new MySqlCommand();
- cmd.Connection = conn;
- cmd.Transaction = tran;
- cmd.CommandText ="INSERT 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();
- change to database2 ??
- cmd.CommandText="DELETE FROM UserTbl WHERE UserID='1'";
- cmd.ExecuteNonQuery();
- tran.Commit();