·  C# – DB – Oracle – Connect

 

- Info:

  - This tutorial shows how to use C# to connect to Oracle DB.

 

- With TNSNAMES.ORA:

  - This example shows how to connect to Oracle DB by using connection string as defined in TNSNAMES.ORA.

  - C# uses Environment Variable ORACLE_HOME to locate TNSNAMES.ORA:

    ORACLE_HOME=D:\Installed\Programming\Oracle9iClient

  - Create C# Console Application Test.cs.

 

    Test.cs

    using System;   

    using System.Data.OracleClient;

   

    class Test {

   

      static void Main(string[] args) {

        

        //CONNECT.---------------------------------------------------------------------

        OracleConnection conn   = new OracleConnection("Data Source=XE; User Id=myUser; Password=myPassword;");

                         conn.Open();

   

        //CLOSE CONNECTION.------------------------------------------------------------

        conn.Close  ();

        conn.Dispose();

       

      }

   

    }

   

    tnsnames.ora      (D:\Installed\Programming\Oracle9iClient\network\ADMIN)

    XE =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = vori)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = XE)

        )

      )

 

- Without TNSNAMES.ORA:

  - This example shows how to connect to Oracle DB by including connection string in the code itself.

  - Therefore this solution is not using TNSNAMES.ORA.

  - Create C# Console Application Test.cs.

 

    Test.cs

    using System;   

    using System.Data.OracleClient;

   

    class Test {

   

      static void Main(string[] args) {

        

        //DEFINE DB.-------------------------------------------------------------------

        string dataSource = @"Data Source =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = vori)(PORT = 1521))

                                  (CONNECT_DATA =

                                    (SERVER = DEDICATED)

                                    (SERVICE_NAME = XE)

                                  )

                                )";

            

        //CONNECT.---------------------------------------------------------------------

        OracleConnection conn   = new OracleConnection(dataSource+"; User ID=myUser; Password=myPassword");

                         conn.Open();

   

        //CLOSE CONNECTION.------------------------------------------------------------

        conn.Close  ();

        conn.Dispose();

       

      }

   

    }

 
- With try/catch:

  - This example shows how to connect to Oracle DB using try/catch block to deal with Exceptions.

  - This example uses TNSNAMES.ORA.

  - Create C# Console Application Test.cs.

 

    Test.cs

    using System;   

    using System.Data.OracleClient;

   

    class Test {

   

      static void Main(string[] args) {

        

        //CONNECT.---------------------------------------------------------------------

        OracleConnection conn = new OracleConnection("Data Source=XE; User Id=myUser; Password=myPassword;");

   

        try{

          conn.Open();

        }

        catch(Exception e){

          Console.WriteLine(e.Message);

        }

        finally{

          conn.Close  ();

          conn.Dispose();

        }

   

      }

   

    }

     

- With using statemant:

  - This example shows how to connect to Oracle DB using using statemant.

  - Compiler transformes using statemant into try/finally block in which the parameter of the using block is

    disposed of as long as it implements the IDisposable interface, as shown in the second Test.cs.

  - Exceptions are propagated to the caller function and only after that finally block is executed.

  - This example uses TNSNAMES.ORA.

  - Create C# Console Application Test.cs.

 

    Test.cs

    using System;   

    using System.Data.OracleClient;

   

    class Test {

   

      static void Main(string[] args) {

        

        //CONNECT.---------------------------------------------------------------------

        using (OracleConnection conn = new OracleConnection("Data Source=XE; User Id=myUserw; Password=myPassword;") ) {

          conn.Open();

        }

   

      }

   

    }

     

    Test.cs

    using System;   

    using System.Data.OracleClient;

   

    class Test {

   

      static void Main(string[] args) {

        

        //CONNECT.---------------------------------------------------------------------

        OracleConnection conn = new OracleConnection("Data Source=XE; User Id=myUseir;     Password=myPassword;");

   

        try{

          conn.Open();

        }

        finally{

          conn.Dispose();

        }

   

      }

   

    }