Getting Started With dotConnect for Oracle

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    try
    {
      OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
      oraCSB.Server = "127.0.0.1";
      oraCSB.Port = 1521;
      oraCSB.ServiceName = "orcl";
      oraCSB.UserId = "TestUser";
      oraCSB.Password = "TestPassword";
      oraCSB.Direct = true;
      oraCSB.LicenseKey = "**********";

      using (OracleConnection connection = new OracleConnection(oraCSB.ConnectionString))
      {
        connection.Open();
         Console.WriteLine("Connection to Oracle successful!");
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine($"Connection failed: {ex.Message}");
    }
  }
}
                            

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    string connectionString = "" +
      "Direct=True;" +
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Service Name=orcl;" +
      "SslKey=/server.key;" +
      "SslCert=/server.crt;" +
      "SslVerification=False;" +
      "License Key=**********";

    using (OracleConnection connection = new OracleConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("SSL connection to Oracle successful!");
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    try
    {
      string connectionString =
        "Direct=True;" +
        "Host=ssh://127.0.0.1;" +
        "Port=1521;" +
        "Service Name=orcl;" +
        "User Id=TestUser;" +
        "Password=TestPassword;" +
        "License Key=**********";

      using (OracleConnection connection = new OracleConnection(connectionString))
      {
        // SSH tunnel setup
        connection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
        connection.SshOptions.Host = "OracleSSH";
        connection.SshOptions.Port = 22;
        connection.SshOptions.User = "sshUser";
        connection.SshOptions.Password = "sshPassword";
        connection.Open();
        Console.WriteLine("SSH connection to Oracle established successfully!");
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine($"Connection failed: {ex.Message}");
    }
  }
}

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    try
    {
      string connectionString =
        "Server=127.0.0.1;" +
        "Port=1521;" +
        "Service Name=orcl;" +
        "User Id=TestUser;" +
        "Password=TestPassword;" +
        "Direct=True;" +
        "License Key=**********";

      using (OracleConnection connection = new OracleConnection(connectionString))
      {
        connection.Open();
        connection.Unicode = true;
        Console.WriteLine("Connection with Unicode support established successfully!");
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine($"Connection failed: {ex.Message}");
    }
  }
}

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "Service Name=orcl;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Direct=True;" +
      "License Key=**********";

    using (OracleConnection connection = new OracleConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection to Oracle successful!");

        // Define a query to select data from the ACTOR table
        string query = "SELECT ACTOR_ID, FIRST_NAME, LAST_NAME FROM ACTOR";

        // Create a command object
        using (OracleCommand command = new OracleCommand(query, connection))
        {
          // Execute the query and get a data reader
          using (OracleDataReader reader = command.ExecuteReader())
          {
            // Loop through the result set
            while (reader.Read())
            {
              // Access the data using column names or indices
              int actorId = reader.GetInt32(reader.GetOrdinal("ACTOR_ID"));
              string firstName = reader.GetString(reader.GetOrdinal("FIRST_NAME"));
              string lastName = reader.GetString(reader.GetOrdinal("LAST_NAME"));

              // Print the data to the console
              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Microsoft.EntityFrameworkCore;
using Devart.Data.Oracle.EFCore;

public class Actor
{
  public int ActorId { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class SakilaContext : DbContext
{
  public DbSet<Actor> Actors { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseOracle(
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "Service Name=orcl;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Direct=True;" +
      "License Key=**********"
    );
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Actor>().ToTable("ACTOR");
    modelBuilder.Entity<Actor>().HasKey(a => a.ActorId);
    modelBuilder.Entity<Actor>().Property(a => a.ActorId).HasColumnName("ACTOR_ID");
    modelBuilder.Entity<Actor>().Property(a => a.FirstName).HasColumnName("FIRST_NAME");
    modelBuilder.Entity<Actor>().Property(a => a.LastName).HasColumnName("LAST_NAME");
  }
}

class Program
{
  static void Main()
  {
    using (var context = new SakilaContext())
    {
      try
      {
        // Optional: Check connection and ensure mapping works
        Console.WriteLine("Connected to Oracle database via EF Core successfully!");

        var actors = context.Actors.Take(10);
        foreach (var actor in actors)
        {
          Console.WriteLine($"Actor ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "Service Name=orcl;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Direct=True;" +
      "License Key=**********";

    using (OracleConnection connection = new OracleConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection to Oracle successful!");

        // Define a query to select data from the ACTOR table
        string query = "SELECT ACTOR_ID, FIRST_NAME, LAST_NAME FROM ACTOR";

        // Create a command object
        using (OracleCommand command = new OracleCommand(query, connection))
        {
          // Create a data adapter
          using (OracleDataAdapter adapter = new OracleDataAdapter(command))
          {
            // Create a DataTable to hold the data
            DataTable dataTable = new DataTable();

            // Fill the DataTable with data from the database
            adapter.Fill(dataTable);

            // Loop through the DataTable and print the data
            foreach (DataRow row in dataTable.Rows)
            {
              int actorId = Convert.ToInt32(row["ACTOR_ID"]);
              string firstName = row["FIRST_NAME"].ToString();
              string lastName = row["LAST_NAME"].ToString();

              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

                            

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "Service Name=orcl;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Direct=True;" +
      "License Key=**********";

    using (OracleConnection connection = new OracleConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection to Oracle successful!");

        // Start a transaction
        using (OracleTransaction transaction = connection.BeginTransaction())
        {
          try
          {
            // Insert a new actor
            string insertQuery = "INSERT INTO ACTOR (FIRST_NAME, LAST_NAME) VALUES ('John', 'Doe')";
            using (OracleCommand insertCommand = new OracleCommand(insertQuery, connection, transaction))
            {
              insertCommand.ExecuteNonQuery();
              Console.WriteLine("Insert command executed.");
            }

            // Update the actor's last name
            string updateQuery = "UPDATE ACTOR SET LAST_NAME = 'Smith' WHERE FIRST_NAME = 'John' AND LAST_NAME = 'Doe'";
            using (OracleCommand updateCommand = new OracleCommand(updateQuery, connection, transaction))
            {
              updateCommand.ExecuteNonQuery();
              Console.WriteLine("Update command executed.");
            }

            // Commit the transaction
            transaction.Commit();
            Console.WriteLine("Transaction committed successfully.");
          }
          catch (Exception ex)
          {
            // Roll back the transaction on error
            transaction.Rollback();
            Console.WriteLine($"Transaction rolled back due to an error: {ex.Message}");
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Connection error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.Oracle;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=1521;" +
      "Service Name=orcl;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Direct=True;" +
      "License Key=**********";

    using (OracleConnection connection = new OracleConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection to Oracle successful!");

        // Attempt to execute a query that may cause an exception
        string query = "SELECT * FROM NON_EXISTENT_TABLE";
        using (OracleCommand command = new OracleCommand(query, connection))
        {
          using (OracleDataReader reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              // This code won't be reached if the table doesn't exist
              Console.WriteLine(reader["COLUMN_NAME"]);
            }
          }
        }
      }
      catch (OracleException ex)
      {
        // Handle Oracle-specific exception
        Console.WriteLine($"Oracle Error: {ex.Message}");
        Console.WriteLine($"Error Code: {ex.Code}");
        Console.WriteLine($"Error Source: {ex.Source}");
        Console.WriteLine($"Stack Trace: {ex.StackTrace}");
      }
      catch (Exception ex)
      {
        // Handle general .NET exceptions
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}