ODP.NET

I was thinking the below fix post 11.2.0.3 was the solution to my issue.

Oracle 11.2.0.3 client with the be patch against an Oracle 11.2.0.2 database and a SQL 2012 instance.

*12352406 - PROBLEM IN COMMITTING A TRANSACTION IS MSDTC WHEN SQL CONNECTION IS OPENED FIRST*

Basically, I have cobbled togther some code from different sources to perform a POC using MSDTC to backout transactions from two different resources managers (Oracle and SQL Server)

In the below code if SQL is first it works as expected and if Oracle is first it works as expected.


However, I can't get both resource managers to properly participate in a transaction.


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

using System.Data.SqlClient;

using System.Transactions;



namespace tran_sproc3

{

class Program

{

static void Main(string[] args)

{



string _connstring = "Data Source=TNS1,User Id=scott;Password=tiger;";

try

{

OracleConnection connObj = new OracleConnection(connstring);

// OracleTransaction _tranObj;

//_connObj.Open();

//_tranObj = _connObj.BeginTransaction();

OracleCommand cmdObj = connObj.CreateCommand();





SqlConnection cn = new SqlConnection("server=(local)\\SQL2012;database=DB1;Integrated Security=SSPI;");

SqlCommand cmd = cn.CreateCommand();



try

{



//using (TransactionScope scope = new TransactionScope())

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))

{



//* SQL Server

cn.Open();

cmd.CommandText = "pTest";

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

Console.WriteLine("SQL Server Stored proc run successfully");



//* oracle

_connObj.Open();

cmdObj.CommandText = "procInsertSamplePODetails";

_cmdObj.CommandType = CommandType.StoredProcedure;

_cmdObj.Parameters.Clear();

_cmdObj.ExecuteNonQuery();

Console.WriteLine("Oracle Stored proc run successfully");



//Intentionally cause an exception

/*

_cmdObj.CommandText = "INSERT INTO NonExistentTable(InvID, InvDate, Remarks) VALUES(:InvID, SYSDATE, :Remarks)";

_cmdObj.CommandType = CommandType.Text;

_cmdObj.Parameters.Clear();

_cmdObj.Parameters.Add(new OracleParameter("InvID", "A02"));

_cmdObj.Parameters.Add(new OracleParameter("Remarks", "Sample invoice 2"));

_cmdObj.ExecuteNonQuery();

//_tranObj.Commit(); */



scope.Complete();



}



}

catch (Exception ex)

{

Console.WriteLine("Uh oh, rollback initiated...");

Console.WriteLine(ex.ToString());

// _tranObj.Rollback();

// }



}

finally

{

//_tranObj.Commit();

_connObj.Close();

_connObj.Dispose();

_connObj = null;

cn.Close();

cn.Dispose();

cn = null;

Console.WriteLine();

Console.WriteLine("ENTER to continue...");

Console.ReadLine();

}



}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

}

}