C# OracleConnection.BeginTransaction方法代码示例

        /**
            Permite eliminar un cliente
        */
        public string deleteCustomer(string id)
        {
            string deleteCustomer = "DELETE FROM CUSTOMERS.CUSTOMER WHERE Id = :Id";

            OracleTransaction transaction;
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    deleteCustomerAddress(id, connection, transaction);

                    using (OracleCommand command = new OracleCommand(deleteCustomer, connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", id));

                        command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    throw new PlatformException(e.Message);
                }
            }

            return "OK";
        }

        public string addAddress(string customer_id, Address address)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO CUSTOMERS.ADDRESS (Id, street, state, zip, country, address_type, city) VALUES");
            sql.Append("(CUSTOMERS.SEQ_ADDRESS.NEXTVAL, :street, :state, :zip, :country, :address_type, :city)");

            StringBuilder sqlCustomerAddress = new StringBuilder();
            sqlCustomerAddress.Append("INSERT INTO CUSTOMERS.CUSTOMER_ADDRESS (address_id, customer_id) VALUES");
            sqlCustomerAddress.Append("(CUSTOMERS.SEQ_ADDRESS.CURRVAL, :customer_id)");

            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
                OracleTransaction transaction;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                    {
                        command.Parameters.Add(new OracleParameter("street", address.street));
                        command.Parameters.Add(new OracleParameter("state", address.state));
                        command.Parameters.Add(new OracleParameter("zip", address.zip));
                        command.Parameters.Add(new OracleParameter("country", address.country));
                        command.Parameters.Add(new OracleParameter("address_type", address.address_type));
                        command.Parameters.Add(new OracleParameter("city", address.city));

                        command.ExecuteNonQuery();

                        using (OracleCommand secondCommand = new OracleCommand(sqlCustomerAddress.ToString(), connection))
                        {
                            secondCommand.CommandText = sqlCustomerAddress.ToString();
                            secondCommand.Parameters.Add(new OracleParameter("customer_id", customer_id));
                            secondCommand.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                    return "OK";
                }
                catch (Exception ex)
                {
                    throw new PlatformException(ex.Message);
                }
            }
        }

        public string deleteAddress(string id, string customer_id)
        {
            StringBuilder deleteCustomerAddress = new StringBuilder();
            deleteCustomerAddress.Append("DELETE FROM CUSTOMERS.CUSTOMER_ADDRESS ");
            deleteCustomerAddress.Append("WHERE customer_id = :customer_id AND address_id = :Id");

            StringBuilder deleteAddress = new StringBuilder();
            deleteAddress.Append("DELETE FROM CUSTOMERS.ADDRESS ");
            deleteAddress.Append("WHERE Id = :Id");

            OracleTransaction transaction;
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                connection.Open();
                transaction = connection.BeginTransaction();

                using (OracleCommand command = new OracleCommand(deleteCustomerAddress.ToString(), connection))
                {
                    command.Parameters.Add(new OracleParameter("Id", id));

                    try
                    {
                        command.ExecuteNonQuery();

                        using(OracleCommand secondCommand = new OracleCommand(deleteAddress.ToString(), connection))
                        {
                            secondCommand.CommandText = deleteAddress.ToString();
                            secondCommand.Parameters.Add(new OracleParameter("customer_id", customer_id));
                            secondCommand.ExecuteNonQuery();
                        }

                        transaction.Commit();

                        return "OK";

                    }
                    catch (Exception e)
                    {
                        throw new PlatformException(e.Message);
                    }
                }
            }
        }

        /**
            Crea un nuevo cliente
        */
        public string createCustomer(Customer customer)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO CUSTOMERS.CUSTOMER (Id, first_name, last_name, phone_number, ");
            sql.Append("email, creditcard_number, creditcard_type, status ) VALUES ( :Id, ");
            sql.Append(":first_name, :last_name, :phone_number, :email, :creditcard_number, ");
            sql.Append(":creditcard_type, :status)");

            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
                OracleTransaction transaction;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", customer.Id));
                        command.Parameters.Add(new OracleParameter("first_name", customer.first_name));
                        command.Parameters.Add(new OracleParameter("last_name", customer.last_name));
                        command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number));
                        command.Parameters.Add(new OracleParameter("email", customer.email));
                        command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number));
                        command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type));
                        command.Parameters.Add(new OracleParameter("status", customer.status));

                        command.ExecuteNonQuery();

                        createCustomerAddress(customer.Id, customer.address, connection, transaction);

                        transaction.Commit();
                    }

                }
                catch (Exception ex)
                {
                    throw new PlatformException(ex.Message);
                }
            }

            return "OK";
        }

    /**Funcion de insercion a la DB*/
    private int oracleInsert(ArrayList q)
    {
        /**Almacenamos data en BD*/
        int count = 0;
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString)/*"Data Source=localhost;Persist Security Info=True;User ID=webadmin;Password=webadmin;Unicode=True")*/;
        conn.Open();
        OracleTransaction t = conn.BeginTransaction();
        try
        {
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.Transaction = t;
            cmd.CommandType = CommandType.Text;
            //Ejecutamos cada insert en la lista
            foreach (String s in q)
            {
                cmd.CommandText = s;
                count += cmd.ExecuteNonQuery();
            }
            //Si fue exitoso, Commit
            t.Commit();
        }
        catch (Exception ex)
        {
            //Sino rollback
            Response.Write(ex);
            t.Rollback();
        }
        finally
        {
            //Cerramos conexion
            t.Dispose();
            conn.Dispose();
            conn.Close();

        }
        return count;
    }

    public static string addNewHardware(string imeModel, string performance, string relYear, string slikaPateka, string desc, string typeId, string companyId)
    {
        OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
        OracleCommand cmd = new OracleCommand();

        cmd.Connection = oracleConnection;
        cmd.CommandText = "INSERT INTO HardverProizvod(hardid,imeModel,performansi,godnaproizvod,slikaPateka,opis,brposetenost) values(SEQ_HARDVERPROIZVOD.nextval,:ime,:performansi,to_date(:godnaproizvod,'DD.MM.YYYY'),:slikapateka,:opis,:brposetenost)";
        cmd.Parameters.Add(new OracleParameter("ime", imeModel));
        cmd.Parameters.Add(new OracleParameter("performansi", performance));
        cmd.Parameters.Add(new OracleParameter("godnaproizvod", relYear));
        cmd.Parameters.Add(new OracleParameter("slikapateka", slikaPateka));
        cmd.Parameters.Add(new OracleParameter("opis", desc));
        cmd.Parameters.Add(new OracleParameter("brposetenost", "0"));
          OracleTransaction transaction =null;
        try
        {
            oracleConnection.Open();
            transaction = oracleConnection.BeginTransaction();
            int success = cmd.ExecuteNonQuery();
            if (success != 0)
            {
                OracleCommand addCompanyCmd = new OracleCommand();
                addCompanyCmd.Connection = oracleConnection;
                addCompanyCmd.CommandText = "INSERT INTO IzdavaHardver(koid,hardid) values (:koid,SEQ_HARDVERPROIZVOD.currval)";
                addCompanyCmd.Parameters.Add(new OracleParameter("koid", companyId));
                int companySuccess = addCompanyCmd.ExecuteNonQuery();
                OracleCommand addGameGenreCmd = new OracleCommand();
                addGameGenreCmd.Connection = oracleConnection;
                addGameGenreCmd.CommandText = "INSERT INTO TipHardver(hardid,katid) values(SEQ_HARDVERPROIZVOD.currval,:katid)";
                addGameGenreCmd.Parameters.Add(new OracleParameter("katid", typeId));
                int genreSuccess = addGameGenreCmd.ExecuteNonQuery();
            }
            transaction.Commit();
            return "Додавањето е успешно";
        }
        catch (Exception e)
        {
            transaction.Rollback();
            return e.Message;
        }
        finally
        {
            oracleConnection.Close();
        }
        return null;
    }

	public static void BLOBTest (OracleConnection connection) 
	{		
		Console.WriteLine ("  BEGIN TRANSACTION ...");

		OracleTransaction transaction = connection.BeginTransaction ();

		Console.WriteLine ("  Drop table BLOBTEST ...");
		try {
			OracleCommand cmd2 = connection.CreateCommand ();
			cmd2.Transaction = transaction;
			cmd2.CommandText = "DROP TABLE BLOBTEST";
			cmd2.ExecuteNonQuery ();
		}
		catch (OracleException) {
			// ignore if table already exists
		}

		Console.WriteLine ("  CREATE TABLE ...");

		OracleCommand create = connection.CreateCommand ();
		create.Transaction = transaction;
		create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
		create.ExecuteNonQuery ();

		Console.WriteLine ("  INSERT RECORD ...");

		OracleCommand insert = connection.CreateCommand ();
		insert.Transaction = transaction;
		insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
		insert.ExecuteNonQuery ();

		OracleCommand select = connection.CreateCommand ();
		select.Transaction = transaction;
		select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
		Console.WriteLine ("  SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");

		OracleDataReader reader = select.ExecuteReader ();
		if (!reader.Read ())
			Console.WriteLine ("ERROR: RECORD NOT FOUND");

		Console.WriteLine ("  TESTING OracleLob OBJECT ...");
		OracleLob lob = reader.GetOracleLob (0);
		Console.WriteLine ("  LENGTH: {0}", lob.Length);
		Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);

		//try {
			if (File.Exists(infilename) == false) {
				Console.WriteLine("Filename does not exist: " + infilename);
				return;
			}

			FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
			BinaryReader r = new BinaryReader(fs);
			
			byte[] bytes = null;
			int bufferLen = 8192;
			bytes = r.ReadBytes (bufferLen);

			while(bytes.Length > 0) {
				Console.WriteLine("byte count: " + bytes.Length.ToString());
				lob.Write (bytes, 0, bytes.Length);
				bytes1 = ByteArrayCombine (bytes1, bytes);
				if (bytes.Length < bufferLen)
					break;
				bytes = r.ReadBytes (bufferLen);
			}

			r.Close();
			fs.Close ();	
		//}
		//catch (Exception e) {
		//	Console.WriteLine("The file could not be read:");
		//	Console.WriteLine(e.Message);
		//}

		lob.Close ();

		Console.WriteLine ("  CLOSING READER...");
			
		reader.Close ();
		transaction.Commit ();
		transaction = null;
		lob = null;
		reader.Dispose();
		reader = null;
		create = null;
		insert = null;
		select = null;
	}

        /// <summary>
        /// ִ�ж���SQL��䣬ʵ�����ݿ�����
        /// </summary>
        /// <param name="SQLStringList">SQL���Ĺ�ϣ���keyΪsql��䣬value�Ǹ�����OracleParameter[]��</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connectionString))
                {
                    conn.Open();
                    using (OracleTransaction trans = conn.BeginTransaction())
                    {
                        OracleCommand cmd = new OracleCommand();
                        try
                        {
                            //ѭ��
                            foreach (DictionaryEntry myDE in SQLStringList)
                            {
                                string cmdText = myDE.Key.ToString();
                                OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
                                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                                int val = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();

                                trans.Commit();
                            }
                        }
                        catch
                        {
                            trans.Rollback();
                            throw;
                        }
                    }
                }

            }
            catch (Exception ex)
            {

                throw new Exception(ex.ToString());
            }
        }

 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="sqlList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
 public static void ExecuteSqlTran(Hashtable sqlList)
 {
     using (OracleConnection conn = new OracleConnection(ConnectionString)) {
         conn.Open();
         using (OracleTransaction trans = conn.BeginTransaction()) {
             OracleCommand cmd = new OracleCommand();
             try {
                 //循环
                 foreach (DictionaryEntry sql in sqlList) {
                     string cmdText = sql.Key.ToString();
                     OracleParameter[] cmdParms = (OracleParameter[])sql.Value;
                     PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                     int val = cmd.ExecuteNonQuery();
                     cmd.Parameters.Clear();
                     trans.Commit();
                 }
             }
             catch {
                 trans.Rollback();
                 throw;
             }
         }
     }
 }

    public static void Main (string[] args)
    {
        string connectionString =
          "Data Source=testdb;" +
          "User ID=scott;" +
          "Password=tiger;";
        OracleConnection connection = null;
        connection = new OracleConnection (connectionString);
        connection.Open ();
 
	Console.WriteLine("Setup test package and data...");
	OracleCommand cmddrop = connection.CreateCommand();
 
	cmddrop.CommandText = "DROP TABLE TESTTABLE";
	try { 
		cmddrop.ExecuteNonQuery(); 
	} 
	catch(OracleException e) {
		Console.WriteLine("Ignore this error: " + e.Message); 
	}
	cmddrop.Dispose();
	cmddrop = null;

Console.WriteLine("Create table TESTTABLE..."); 
	OracleCommand cmd = connection.CreateCommand();
 
	// create table TESTTABLE
	cmd.CommandText = 
		"create table TESTTABLE (\n" +
		" col1 numeric(18,0),\n" +
		" col2 varchar(32),\n" +
		" col3 date, col4 blob)";

	cmd.ExecuteNonQuery();
 Console.WriteLine("Insert 3 rows...");
	// insert some rows into TESTTABLE
	cmd.CommandText = 
		"insert into TESTTABLE\n" +
		"(col1, col2, col3, col4)\n" +
		"values(45, 'Mono', sysdate, EMPTY_BLOB())";
	cmd.ExecuteNonQuery();
 
	cmd.CommandText = 
		"insert into TESTTABLE\n" +
		"(col1, col2, col3, col4)\n" +
		"values(136, 'Fun', sysdate, EMPTY_BLOB())";
	cmd.ExecuteNonQuery();
 
	cmd.CommandText = 
		"insert into TESTTABLE\n" +
		"(col1, col2, col3, col4)\n" +
		"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
	cmd.ExecuteNonQuery();

Console.WriteLine("commit...");

	cmd.CommandText = "commit";
	cmd.ExecuteNonQuery();

Console.WriteLine("Update blob...");

			// update BLOB and CLOB columns
			OracleCommand select = connection.CreateCommand ();
			select.Transaction = connection.BeginTransaction();
			select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
			OracleDataReader readerz = select.ExecuteReader ();
			if (!readerz.Read ())
				Console.WriteLine ("ERROR: RECORD NOT FOUND");
			// update blob_value
			Console.WriteLine("     Update BLOB column on table testtable...");
			OracleLob blob = readerz.GetOracleLob (1);
			byte[] bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
			blob.Write (bytes, 0, bytes.Length);
			blob.Close ();
			readerz.Close();
			select.Transaction.Commit();
			select.Dispose();
			select = null;
			
 
	cmd.CommandText = "commit";
	cmd.ExecuteNonQuery();

Console.WriteLine("Create package...");
 
	// create Oracle package TestTablePkg
	cmd.CommandText = 
		"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
		"AS\n" +
		"	TYPE T_CURSOR IS REF CURSOR;\n" +
		"\n" +
		"	PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" +
		"END TestTablePkg;";
	cmd.ExecuteNonQuery();
 
	// create Oracle package body for package TestTablePkg
	cmd.CommandText = 
		"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
		"  PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +	
                "  IS\n" +
//.........这里部分代码省略.........

    public static string addNewGame(string name, string relYear, string imgPath, string desc, string company, string minPerf, string PrepPerf, string zhanr, string expansionOf)
    {
        OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
        OracleCommand cmd = new OracleCommand();

        cmd.Connection = oracleConnection;
        cmd.CommandText = "INSERT INTO Igra(igraid,ime,godizdavanje,slikapateka,opis,minperform,prepperform,brposetenost) values(seq_igra.nextval,:ime,to_date(:godizdavanje,'DD.MM.YYYY'),:slikapateka,:opis,:minperform,:prepperform,:brposetenost)";
        cmd.Parameters.Add(new OracleParameter("ime", name));
        cmd.Parameters.Add(new OracleParameter("godizdavanje", relYear));
        cmd.Parameters.Add(new OracleParameter("slikapateka", imgPath));
        cmd.Parameters.Add(new OracleParameter("opis", desc));
        cmd.Parameters.Add(new OracleParameter("minperform", minPerf));
        cmd.Parameters.Add(new OracleParameter("prepperform", PrepPerf));
        cmd.Parameters.Add(new OracleParameter("brposetenost", "0"));
        OracleTransaction transaction = null;
        try
        {
            oracleConnection.Open();
            transaction = oracleConnection.BeginTransaction();
            int success = cmd.ExecuteNonQuery();
            if (success != 0)
            {
                string igraid = getLatestAddedGameId(oracleConnection);
                OracleCommand addCompanyCmd = new OracleCommand();
                addCompanyCmd.Connection = oracleConnection;
                addCompanyCmd.CommandText = "INSERT INTO IzdavaIgra(koid,igraid) values (:koid,:igraid)";
                addCompanyCmd.Parameters.Add(new OracleParameter("koid", company));
                addCompanyCmd.Parameters.Add(new OracleParameter("igraid", igraid));
                int companySuccess = addCompanyCmd.ExecuteNonQuery();
                OracleCommand addGameGenreCmd = new OracleCommand();
                addGameGenreCmd.Connection = oracleConnection;
                addGameGenreCmd.CommandText = "INSERT INTO ZHANRIGRA(igraid,zhanrid) values(:igraid,:zhanrid)";
                addGameGenreCmd.Parameters.Add(new OracleParameter("igraid", igraid));
                addGameGenreCmd.Parameters.Add(new OracleParameter("zhanrid", zhanr));
                int genreSuccess = addGameGenreCmd.ExecuteNonQuery();
                if (expansionOf != null)
                {
                    OracleCommand addGameExpansionCmd = new OracleCommand();
                    addGameExpansionCmd.Connection = oracleConnection;
                    addGameExpansionCmd.CommandText = "INSERT INTO IMAVERZIJA (prvaigraid,prodolzenieid) values (:prvaigraid, :prodolzenieid)";
                    addGameExpansionCmd.Parameters.Add(new OracleParameter("prvaigraid", expansionOf));
                    addGameExpansionCmd.Parameters.Add(new OracleParameter("prodolzenieid", igraid));
                    int expansionSuccess = addGameExpansionCmd.ExecuteNonQuery();
                }

            }
            transaction.Commit();
            return "Додавањето е успешно";
        }
        catch (Exception e)
        {
            transaction.Rollback();
            return e.Message;
        }
        finally
        {
            oracleConnection.Close();
        }
        return null;
    }

	public static void CLOBTest (OracleConnection connection) 
	{		
		Console.WriteLine ("  BEGIN TRANSACTION ...");

		OracleTransaction transaction = connection.BeginTransaction ();

		Console.WriteLine ("  Drop table CLOBTEST ...");
		try {
			OracleCommand cmd2 = connection.CreateCommand ();
			cmd2.Transaction = transaction;
			cmd2.CommandText = "DROP TABLE CLOBTEST";
			cmd2.ExecuteNonQuery ();
		}
		catch (OracleException oe1) {
			// ignore if table already exists
		}

		Console.WriteLine ("  CREATE TABLE ...");

		OracleCommand create = connection.CreateCommand ();
		create.Transaction = transaction;
		create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
		create.ExecuteNonQuery ();

		Console.WriteLine ("  INSERT RECORD ...");

		OracleCommand insert = connection.CreateCommand ();
		insert.Transaction = transaction;
		insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
		insert.ExecuteNonQuery ();

		OracleCommand select = connection.CreateCommand ();
		select.Transaction = transaction;
		select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
		Console.WriteLine ("  SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");

		OracleDataReader reader = select.ExecuteReader ();
		if (!reader.Read ())
			Console.WriteLine ("ERROR: RECORD NOT FOUND");

		Console.WriteLine ("  TESTING OracleLob OBJECT ...");
		OracleLob lob = reader.GetOracleLob (0);
		Console.WriteLine ("  LENGTH: {0}", lob.Length);
		Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);

		UnicodeEncoding encoding = new UnicodeEncoding ();

		try {
			// read file "cs-parser.cs" into the oracle clob
			using (StreamReader sr = new StreamReader(infilename)) {
				string sbuff = sr.ReadToEnd ();
				byte[] evalue = encoding.GetBytes (sbuff);
				lob.Write (evalue, 0, evalue.Length);
			}
		}
		catch (Exception e) {
			Console.WriteLine("The file could not be read:");
			Console.WriteLine(e.Message);
		}
		lob.Close ();

		Console.WriteLine ("  CLOSING READER...");
			
		reader.Close ();
		transaction.Commit ();
	}

        public void DoWork()
        {
            using (OracleConnection conn = new OracleConnection(GlobalUtils.TopSecret.OracleCS))
            using (OracleCommand command = new OracleCommand())
            {
                try
                {
                    conn.Open();
                    //conn.StatisticsEnabled = true;
                    command.Connection = conn;
                }
                catch (Exception e)
                {
                    Console.Error.WriteLine(e.Message);
                    return;
                }
                try
                {
                    using (OracleTransaction sqlTran = conn.BeginTransaction())
                    {
                        command.Transaction = sqlTran;

                        OracleDataReader reader;

                        List<string> commands = GetCommands(com);
                        foreach (string c in commands)
                        {
                            command.CommandText = c;
                            using (reader = command.ExecuteReader())
                            {
                                ShowResultSet(reader, command);
                                while (reader.NextResult())
                                    ShowResultSet(reader, command);
                            }
                        }

                        //string out_string;
                        //int status = 0;
                        //command.CommandText = "BEGIN DBMS_OUTPUT.GET_LINE (:out_string, :status); END;";
                        //command.CommandType = CommandType.Text;
                        //command.Parameters.Clear();
                        //command.Parameters.Add("out_string", OracleDbType.Varchar2, 32000);
                        //command.Parameters.Add("status", OracleDbType.Double);
                        //command.Parameters[0].Direction = System.Data.ParameterDirection.Output;
                        //command.Parameters[1].Direction = System.Data.ParameterDirection.Output;
                        //command.ExecuteNonQuery();
                        //out_string = command.Parameters[0].Value.ToString();
                        //status = int.Parse(command.Parameters[1].Value.ToString());
                        //if (!string.IsNullOrEmpty(out_string))
                        //{
                        //    Console.WriteLine(System.Web.HttpUtility.HtmlEncode(out_string));
                        //}

                        //var stats = conn.RetrieveStatistics();
                        //using (TextWriter tw = new StreamWriter(path + ".stats"))
                        //{
                        //    tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
                        //                    Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
                        //                    stats["SelectRows"],
                        //                    stats["IduRows"]);
                        //}
                    }
                }
                catch (Exception e)
                {
                    Console.Error.WriteLine(e.Message);
                    if (command != null)
                        command.Cancel();
                }
            }
        }

        private void SafeExecuteCommand(Action<OracleCommand> action)
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                OracleTransaction transaction = null;
                OracleCommand com;

                try
                {
                    connection.Open();
                    OracleSqlUtility.SetSqlUserInfo(connection, _userInfo);

                    transaction = connection.BeginTransaction();
                    com = connection.CreateCommand();
                    com.CommandTimeout = SqlUtility.SqlCommandTimeout;
                    com.Transaction = transaction;
                }
                catch (OracleException ex)
                {
                    if (transaction != null)
                        transaction.Rollback();

                    var csb = new OracleConnectionStringBuilder(_connectionString);
                    string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID);
                    _logger.Error(msg);
                    _logger.Error(ex.ToString());
                    throw new FrameworkException(msg, ex);
                }

                try
                {
                    var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery();
                    if (!string.IsNullOrEmpty(setNationalLanguage))
                    {
                        _logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage);
                        com.CommandText = setNationalLanguage;
                        com.ExecuteNonQuery();
                    }

                    action(com);
                    transaction.Commit();
                }
                catch (OracleException ex)
                {
                    if (com != null && !string.IsNullOrWhiteSpace(com.CommandText))
                        _logger.Error("Unable to execute SQL query:\r\n" + com.CommandText);

                    string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex);
                    if (ex.Number == 911)
                        msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query.";
                    _logger.Error(msg);
                    _logger.Error(ex.ToString());
                    throw new FrameworkException(msg, ex);
                }
                finally
                {
                    TryRollback(transaction);
                }
            }
        }

        public void setupdatabase(OracleConnection conn)
        {
            OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                createTables(conn);
                createSequences(conn);
                createTriggers(conn);
                setupSystemData(conn);

                Console.WriteLine("\nSystem Database Successfully Configured");

                txn.Commit();
                close(conn);
            }
            catch(Exception e)
            {
                txn.Rollback();
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(e.Message);
            }
        }