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

[sun21170]

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?

[Pinturiccio]

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");
                }
            }
        }
    }
}

[sun21170]

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

Thanks
Sunil

[Pinturiccio]

dotConnect Universal works with Distributed transactions when Microsoft Distribution Transaction Coordinator has default settings.

[wole]

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.

[Pinturiccio]

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.

[wole]

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.

[Pinturiccio]

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.