ODP.NET

I am using VS2008, System.Data.OracleClient, Oracle 10g, and ODAC 10.2.0.20. I haven't been able to get transactions to work. When I use 'connection.BeginTransaction()', the rollback doesn't work. When I use TransactionScope, the output parameter is always DBNull. Any ideas/comments?

Here's the sample code:

// #define ENABLE_TRANSACTION // failure is 'rollback not working'
#define ENABLE_TRANSACTION_SCOPE // failure is 'no output parameter value'

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.OracleClient;
#if ENABLE_TRANSACTION_SCOPE
using System.Transactions;
#endif

namespace TestOracleTransaction
{
class Program
{
static void Main(string[] args)
{
#if ENABLE_TRANSACTION_SCOPE
using (TransactionScope scope = new TransactionScope())
#endif
{
string connectionString = "Data Source=ORADEV;User ID=user;Password=pwd";
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
connection.Open();

#if ENABLE_TRANSACTION
using (OracleTransaction transaction = connection.BeginTransaction())
#endif
{
try
{
#if ENABLE_TRANSACTION_SCOPE
if (Transaction.Current == null)
{
throw new ArgumentException("no ambient transaction found for OracleClient");
}
#endif

OracleCommand command = connection.CreateCommand();
#if ENABLE_TRANSACTION
command.Transaction = transaction;
#endif

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TIS.P_TIS_GATEWAY_INFO_ADD";

OracleParameter param = command.CreateParameter();
param.ParameterName = "p_gateway_id";
param.Direction = ParameterDirection.Input;
param.DbType = DbType.Int64;
param.Value = 18;
command.Parameters.Add(param);

param = command.CreateParameter();
param.ParameterName = "p_info_id";
param.Direction = ParameterDirection.Input;
param.DbType = DbType.Int64;
param.Value = 79;
command.Parameters.Add(param);

param = command.CreateParameter();
param.ParameterName = "p_user";
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
param.Value = "spms";
command.Parameters.Add(param);

param = command.CreateParameter();
param.ParameterName = "p_gateway_info_id";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int64;
param.Size = sizeof(Int64);
command.Parameters.Add(param);

int count = command.ExecuteNonQuery();

object value = command.Parameters["p_gateway_info_id"].Value;

long id = (value == DBNull.Value) ? -1 : Convert.ToInt64(value);
if (id < 0)
{
// FAILURE - no output parameter value when TransactionScope enabled
throw new ArgumentException("no return value");
}

#if ENABLE_TRANSACTION
// FAILURE - rollback doesn't work when Transaction enabled
transaction.Rollback();
#endif
#if ENABLE_TRANSACTION_SCOPE
scope.Complete();
#endif
}
catch (Exception ex)
{
System.Console.WriteLine("ERROR: " + ex.Message);
#if ENABLE_TRANSACTION
transaction.Rollback();
#endif
}
}
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
}
}
}