Views: 5.8K
Replies: 0
Archived
|
TransactionDecorator with Oracle.DataAccess.Client.OracleConnectionHi, i'm using Pattern In Action 4.0 and i'm connecting to Oracle Databse with Oracle.DataAccess.Client provider.
Oracle.DataAccess.Client version : 4.112.3.0 Oracle Database version: 11g I need execute many step in transaction from Service Layer using TransactionDecorator I need include in ServiceLayer the reference to Oracle.DataAccess for propagate the same connection between step I'm not sure that MSDTC work on production environment and i would like to use local transaction with same connection I have put in oracle connectionstring the value PROMOTABLE TRANSACTION=LOCAL connectionString="User Id=xxx;Password=xxx;Data Source=oraclex;PROMOTABLE TRANSACTION=LOCAL"
This is method in Service Layerusing (TransactionDecorator transaction = new TransactionDecorator()) { using (Oracle.DataAccess.Client.OracleConnection connection = DataObjects.ONYX.ODP.NET.Db2.Connect()) { //1. Insert dossier and fill structure - recorsive dx = dossierDao.OnyxSsprodInsert(dx, connection); //4. Insert noteDinamiche notaDinamicaDaoDao.InsertCollection(dx.NoteDinamiche, connection); //5. Insert tibco data ricezione ricezioneDao.TibcoWsInsert(dx.DataRicezione, connection); //6. Insert tibco prestazi trans prestazioneDao.InsertCollection(dx.Prestazioni, connection); //7. Insert Note Sintetiche notaSinteticaDao.InsertCollection(dx.NoteSintetiche, connection); } } This is Method in Dao Layer public Dossier OnyxSsprodInsert(Dossier dossier, OracleConnection connection = null) { if (dossier.IsValid()) { bool close = false; if (connection == null) { string constr = ConfigurationManager.ConnectionStrings[1].ConnectionString.Trim(); connection = Db2.Connect(); // get new connection close = true; } // Create a command to execute the sql statement. OracleCommand cmd = new OracleCommand("insertdossier", connection); //DbCommand cmd = db. //OracleCommand cmd = new OracleCommand("insertDossier_type", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(Take(dossier)); try { // Execute command; Have the parameters populated cmd.ExecuteNonQuery(); // Create the OracleDataAdapter OracleDataAdapter da = new OracleDataAdapter(cmd); // Populate a DataSet with refcursor1. DataSet ds = new DataSet(); da.Fill(ds, "p_return_cursor", (OracleRefCursor)(cmd.Parameters["p_return_cursor"].Value)); ..... } catch (Exception e) { System.Diagnostics.Debug.WriteLine("Error: {0}", e.Message); throw; } finally { // Dispose OracleCommand object cmd.Dispose(); // Close local connenction if exists if (close) { if (connection != null) { connection.Close(); connection.Dispose(); } } } } else { //throw fault exception throw new ArgumentException(string.Join(System.Environment.NewLine, dossier.Errors.ToArray())); } return dossier; } is it the correct way ? every suggestion are greatly appreciated !!!!! NOTE: I've implemented upper solution after i've read this post : Using TransactionScope with ODP.NET If you are using Oracle Database Provider for .NET (ODP.NET) and you want to use TransactionScope'd transactions, you may have gotten the dreaded "Data provider internal error(-3000) [System.String]" exception. I'll explain how I fixed it. First, if you want to support distributed transactions, make sure you have the OracleMTSRecoveryService service installed and running on your machine (it comes with the Oracle Data Access Components installation), or any other machine on your network, as long as properly configured on the MSDTC tab on the Component Services applet. If you don't need distributed transactions, you can place the connection string setting PROMOTABLE TRANSACTION=LOCAL on your connection string, for example: DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.Some.Community)(PROTOCOL=TCP)(HOST=Some.Host)(PORT=1522))(CONNECT_DATA=(SID=SomeSid)));USER ID=SomeUsername;PASSWORD=SomePassword;PROMOTABLE TRANSACTION=LOCAL Now you can have: using (TransactionScope tx = new TransactionScope()){ using (OracleConnection con = new OracleConnection(@"DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.Some.Community)(PROTOCOL=TCP)(HOST=Some.Host)(PORT=1522))(CONNECT_DATA=(SID=SomeSid)));USER ID=SomeUsername;PASSWORD=SomePassword;PROMOTABLE TRANSACTION=LOCAL")) using (OracleCommand cmd = con.CreateCommand()){ con.Open(); //no longer crashes here cmd.CommandText = "SELECT sysdate FROM dual"; DateTime date = (DateTime)cmd.ExecuteScalar();} } If you need more information, check the Oracle Data Provider for .NET Developer's Guide, it's the e10927.pdf file on the B28359_01\win.111 folder of the Oracle documentation (version 11g). Vito Iacono, Mar 05, 2014
|