//---------------------------------------------------------------------
// File: OracleDatabaseHelperEx.cs
// 
// Summary: 
//
// Copyright (c) Hammersmith & Fulham Bridge Partnership. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
// KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
// PURPOSE.
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OracleClient;

namespace BizUnit.Extensions.Utilities
{
  /// <summary>
  /// Static Helper for executing SQL statements against Oracle
  /// Requires the ODP.NET provider
  /// </summary>
  public class OracleDatabaseHelperEx
  {
        #region constructor(s)
        /// <summary>
        /// Constructor for class, default constructor is private to prevent instances being
        /// created as the class only has static methods
        /// </summary>
        public OracleDatabaseHelperEx()
    {
    }
        #endregion

        #region Static Methods
        /// <summary>
        /// Excecutes the SQL statement against the database and returns a DataSet with the results
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>DataSet with the results of the executed command</returns>
        public DataSet ExecuteSqlCommand( string connectionString, string sqlCommand )
        {
            DataSet ds = new DataSet() ;
      try
      {
        using ( OracleConnection connection = new OracleConnection( connectionString ) )
        {
          OracleDataAdapter adapter = new OracleDataAdapter( sqlCommand, connection ) ;
          adapter.Fill( ds ) ;
        }   // connection
      }
      catch (Exception)
      {
        throw ;
      }
            return ds ;
        }

        /// <summary>
        /// Executes the SQL statement and returns the first column of the first row in the resultset returned by the query.
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>The contents of the first column of the first row in the resultset</returns>
        public int ExecuteScalar( string connectionString, string sqlCommand )
        {
            OracleConnection connection = null ;
            object col = 0 ;

            try 
            {
                connection = new OracleConnection( connectionString ) ;
                OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
                command.Connection.Open() ;
                col = command.ExecuteScalar() ;
            }
            catch ( Exception )
            {
        throw;
            }
            finally 
            {
                connection.Close() ;
            }

            return Convert.ToInt32( col ) ;
        }

        /// <summary>
        /// Executes the SQL statement
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        public void ExecuteNonQuery( string connectionString, string sqlCommand )
        {
            OracleConnection connection = null ;

            try 
            {
                connection = new OracleConnection( connectionString ) ;
                OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
                command.Connection.Open() ;
                command.ExecuteNonQuery() ;
            }
            catch ( Exception )
            {
        throw;
            }
            finally 
            {
                connection.Close() ;
            }
        }
        #endregion
  }
}

   
     

   
 
using System;
using System.Data;
using System.Data.OracleClient;

class OracleConnect
{
   static void Main() 
   {
      string connString = @"server = myServer;uid = scott;password = tiger;";

      OracleConnection conn = new OracleConnection(connString);

      try 
      {
         conn.Open();
         Console.WriteLine("Connection opened.");

         Console.WriteLine("Connection Properties:");   
         Console.WriteLine("	Connection String: {0}", conn.ConnectionString);    
         Console.WriteLine("	ServerVersion: {0}", conn.ServerVersion);
         Console.WriteLine("	State: {0}", conn.State);
      } catch (OracleException e) {
         Console.WriteLine("Error: " + e);  
      } finally {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}



           
         
     

   
 

using System;
using System.Data;
using System.Data.OleDb;

class OleDbConnectionOracle
{
  public static void Main()
  {
    string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
    OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);

    OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

    myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369";

    myOleDbConnection.Open();

    OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

    myOleDbDataReader.Read();

    Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]);
    Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]);
    Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]);

    myOleDbDataReader.Close();
    myOleDbConnection.Close();
  }
}
           
         
     

   
 

using System;
using System.Data;
using System.Data.OleDb;

class OleDbConnectionOracle
{
  public static void Main()
  {
    string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
    OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);

    OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

    myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369";

    myOleDbConnection.Open();

    OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

    myOleDbDataReader.Read();

    Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]);
    Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]);
    Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]);

    myOleDbDataReader.Close();
    myOleDbConnection.Close();
  }
}
           
         
     

   


using System;
using System.Data;
using System.Data.OleDb;

public class Transact {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the database");
   OleDbCommand cmd = con.CreateCommand();

   OleDbTransaction trans = con.BeginTransaction();
   cmd.Transaction = trans;
   cmd.CommandText ="INSERT INTO Employee VALUES (12,'CD','wwe',10)";
   cmd.ExecuteNonQuery();
   trans.Commit();


   cmd.CommandText = "SELECT First_name FROM Employee";
   OleDbDataReader reader = cmd.ExecuteReader();

   while(reader.Read()) 
     Console.WriteLine("{0}",
               reader.GetString(0));
   reader.Close();


   con.Close();
 }
}
           
          

   


using System;
using System.Data;
using System.Data.OleDb;

public class Transact {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the database");
   OleDbCommand cmd = con.CreateCommand();

   OleDbTransaction trans = con.BeginTransaction();
   cmd.Transaction = trans;
   cmd.CommandText ="INSERT INTO Employee VALUES (09,'qwe','ewq',123)";
   cmd.ExecuteNonQuery();

   cmd.CommandText = "SELECT First_name FROM Employee";
   OleDbDataReader reader = cmd.ExecuteReader();

   while(reader.Read()) 
     Console.WriteLine("{0}",
               reader.GetString(0));
   reader.Close();

   trans.Rollback(); 
 
   cmd.CommandText = "SELECT First_name FROM Employee";
   reader = cmd.ExecuteReader();
   Console.WriteLine("Rolled back insert and update -- table unchanged");
   Console.WriteLine("First_name");
   while(reader.Read()) 
     Console.WriteLine("{0}",
               reader.GetString(0));
   reader.Close();


   con.Close();
 }
}
           
          

   


using System;
using System.Data;
using System.Data.OleDb;

public class Transact {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the database");
   OleDbCommand cmd = con.CreateCommand();

   OleDbTransaction trans = con.BeginTransaction();
   cmd.Transaction = trans;
   cmd.CommandText = "UPDATE Employee SET Salary = 1342 "
                        + "WHERE ID > 1";
   cmd.ExecuteNonQuery();

   cmd.CommandText = "SELECT First_name FROM Employee";
   OleDbDataReader reader = cmd.ExecuteReader();

   while(reader.Read()) 
     Console.WriteLine("{0}",
               reader.GetString(0));
   reader.Close();

   con.Close();
 }
}