Update Oracle database and SQL Server 2008 R2 database within a transaction

Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby sun21170 » Sat 15 Jun 2013 03:47

I need to perform update in a table in Oracle as well as update a table in a SQL Server 2008 R2 database.

Is it possible to run them in a transaction using dotConnect Universal?
sun21170
 
Posts: 6
Joined: Sat 31 Dec 2011 16:55

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby Pinturiccio » Tue 18 Jun 2013 14:20

You can work with the Oracle and SQL Server databases in the same transaction. Below is an example of the transaction usage. The DDL/DML scripts can be found in "C:\Program Files (x86)\Devart\dotConnect\Universal\Samples" (the default location of the installed provider).

Code: Select all
using System;
using Devart.Data.Universal;
using System.Transactions;

namespace UniTransaction
{
    class Program
    {
        static void Main(string[] args)
        {
            using (TransactionScope transScope = new TransactionScope())
            {
                try
                {
                    using (UniConnection sqlConn = new UniConnection("provider=sql server;Data

Source=xxx;Integrated Security=False;User ID=xxx; initial catalog=xxx;Enlist=true"))
                    {
                        sqlConn.Open();
                        UniCommand sqlComm = new UniCommand("update dept set dname='test' where deptno=30",
sqlConn);
                        sqlComm.ExecuteNonQuery();
                        sqlConn.Close();
                    }

                    using (UniConnection oraConn = new UniConnection("provider=Oracle;Data
Source=xxx;uid=xxx;pwd=xxx;Enlist=true"))
                    {
                        oraConn.Open();
                        UniCommand oraComm = new UniCommand("update dept set dname='test' where deptno=30",
oraConn);
                        oraComm.ExecuteNonQuery();
                        oraConn.Close();
                    }
                    transScope.Complete();
                }
                catch(Exception)
                {
                    Console.WriteLine("Transaction is aborted");
                }
            }
        }
    }
}
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby sun21170 » Tue 18 Jun 2013 14:58

Thanks for the sample code. If I use your code, do I need to also configure MSDTC ( Microsoft Distribution Transaction Coordinator)?

Thanks
Sunil
sun21170
 
Posts: 6
Joined: Sat 31 Dec 2011 16:55

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby Pinturiccio » Wed 19 Jun 2013 14:42

dotConnect Universal works with Distributed transactions when Microsoft Distribution Transaction Coordinator has default settings.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby wole » Thu 24 Oct 2013 02:39

I want to synchronize an oracle 11g database, a microsoft SQL server 2012 and an excel file together, whereby when ever i update any all others are also automatically updated.

Is it possible and how do i go about it.
wole
 
Posts: 2
Joined: Thu 24 Oct 2013 02:32

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby Pinturiccio » Thu 24 Oct 2013 14:43

The transaction function is that if one statement in the transaction failed, the result of other will not be applied. You want that, if one query is executed, other queries be executed too. dotConnect Universal doesn't provide functionality for synchronizing data in different databases. You will need to implement the synchronization and change tracking logics yourself.
To update your databases inside a transaction you may use the above example after adding a code for work with the Excel database.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby wole » Thu 24 Oct 2013 22:28

So how can the dotconnect universal software be of help to me, it i intend to update an oracle and a SQL server database through a SQL statement.
wole
 
Posts: 2
Joined: Thu 24 Oct 2013 02:32

Re: Update Oracle database and SQL Server 2008 R2 database within a transaction

Postby Pinturiccio » Fri 25 Oct 2013 14:52

dotConnect Universal allow you to update Oracle, SQL Server and Excel databases via SQL statement in Unicommand. You may use the TransactionScope class and update all three databases inside a single TransactionScope, which allows either to update all three databases or update no database in case of failure during the update.

If we understood you correctly, you want to update other two databases in case one of them is updated. In this case you must implement the application that tracks updates in databases and updates other databases. You can use dotConnect Universal for this. It allows working with all the three databases.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect Universal