473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

single transaction over multiple databases

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?

Expand|Select|Wrap|Line Numbers
  1. conn1 = new MySqlConnection(db1);
  2. conn1.Open();
  3. tran1 = conn1.BeginTransaction();
  4.  
  5. conn2 = new MySqlConnection(db2);
  6. conn2.Open();
  7. tran2 = conn2.BeginTransaction();
  8.  
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
  2.  
  3. conn = new MySqlConnection(db1);
  4. conn.Open();
  5. tran = conn.BeginTransaction();
  6.  
  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";
  13.  
  14. cmd.ExecuteNonQuery();
  15.  
  16. change to database2 ??
  17.  
  18. cmd.CommandText="DELETE FROM UserTbl WHERE UserID='1'";
  19. cmd.ExecuteNonQuery();
  20.  
  21. tran.Commit();
  22.  
  23.  
Feb 28 '13 #1
1 5167
Luuk
1,047 Expert 1GB
"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

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

Similar topics

0
by: Heather | last post by:
Hello, I'm writing to see if anyone is familiar with a tool that can update SQL Server code (i.e. stored procedures, views or table modifications) throughout multiple databases. Currently we...
1
by: RichardF | last post by:
Is it possible to run a single query that pulls data from multiple tables in multiple databases, perhaps also from multiple SQL Servers? Any input would be appreciated. Thanks! RichardF
3
by: DKode | last post by:
I am preparing to build an app that will pull data from multiple databases on the same sql server. For performance sake, is there a more "effecient" way to grab data from multiple databases at...
9
by: TC | last post by:
I need to design a system which represents multiple "projects" in SQL Server. Each project has the same data model, but is independent of all others. My inclination is to use one database to store...
6
by: BostonNole | last post by:
I am using SQL 2000 and VB.NET (VS 2005). I have three stored procedure: sp_A, sp_B and sp_C I need to be able to call sp_A once and sp_B and sp_C 1 to n number of times all within a single...
2
by: DKK | last post by:
We are using Transaction Replication across 3 DB's. Data from DB1 needs to be replicated to DB2 from where it goes to DB3. There are no independent updates in DB2 and DB3. We are using SQL Server...
1
by: foneman | last post by:
I'm running Sambar 7.0, PHP 5.2 and PostgreSQL 8.2 with WinXP. I built a database that's fed with form data, and created summaries with views and PHP scripts. I cloned the database two different...
1
by: richard.crosh | last post by:
What is the IBM recommendation for the number of DB2-LUW databases per instance on AIX? With Oracle, it is one-to-one. In DB2 multiple databases can co-exist in an instance but is this...
4
by: JoyceBabu | last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a...
0
by: SLauren | last post by:
Hi, Can anyone please suggest me how can i write a single stored procedure which will work for multiple databases? Thanks in advance Regards, Lauren
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.