Oracle (21c) with .Net Core and C#

When considering the enterprise databases, Oracle is acting a huge role. Today let's talk about how to install Oracle locally and access the database with .Net and C#. 

NOTE: You can find the connection string formats in tnsnames.ora 

Install and connect to localhost

Connect and Retrieve data using OracleConnection

We can download the free developer edition of the Oracle called 'Oracle Xpress Edition' for free. So I'm going to use that in this tutorial for install in the local hard drive. 

1. Search for Oracle Xpress Edition in the internet (https://www.oracle.com/ca-en/database/technologies/appdev/xe.html) and follow the instruction to install. 
2. Create a .Net Core project (you can create a console project, web api, etc.).
3. Install Oracle.EntityFrameworkCore by Oracle using NuGet package manager. 
4. Use the following sample code to connect and retrieve data from local database table. 

Credentials

public static string db = "localhost/XEPDB1";

public static string user = "DEMOUSER";

public static string pwd = "DEMOPASSWORD";

private void GetToDoList_Local()

{

string conStringUser = "User Id=" + user + ";Password=" + pwd + ";Data Source=" + db + ";";

using (OracleConnection con = new OracleConnection(conStringUser))

{

using (OracleCommand cmd = con.CreateCommand())

{

try

{

con.Open();

cmd.CommandText = "SELECT description, done FROM todoitem";

OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

if (reader.GetBoolean(1))

Console.WriteLine(reader.GetString(0) + " is done.");

else

Console.WriteLine(reader.GetString(0) + " is NOT done.");

}

reader.Dispose();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

}

}

Connect and Retrieve data using Entity Framework Core

using DbDataManager.DataModels;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
namespace DbDataManager
{
    public class OracleDbContext : DbContext
    {
        public DbSet<ToDoModel> TODOITEM { get; set; }
      

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseOracle(@"User Id=DEMOUSER;Password=DEMOPASSWORD;Data Source=localhost/XEPDB1;");            
        }

        public void AddToToDoList()
        {
            try
            {
                var todo = new ToDoModel
                {
                    DESCRIPTION = "Task 7",
                    CREATION_TS = DateTime.Now,
                    DONE = 1
                };
                var db = new OracleDbContext();
                db.TODOITEM.Add(todo);
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public List<ToDoModel> GetToDoList()
        {
            try
            {
                var todoList = new List<ToDoModel>();
                using (var db = new OracleDbContext())
                {
                    var data = db.TODOITEM;
                    foreach (var todo in data)
                    {
                        todoList.Add(todo);
                    }
                }
                return todoList;
            }
            catch (Exception ex)
            {
                throw;
            }
            
        }
    }
}

Connect to database in AWS with Entity Framework 

using DbDataManager.DataModels;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
namespace DbDataManager
{
    public class OracleDbContext : DbContext
    {
        public static string host = "[Your AWS Oracle Host URL]";
        public static string port = "1521";
        public static string aws_db = "DevDb";
        public static string aws_user = "awsusername";
        public static string aws_pwd = "awspassword";

        public DbSet<ToDoModel> TODOITEM { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseOracle("Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = " + host + ")(PORT = "+ port +"))(CONNECT_DATA = (SID = "+ aws_db  +"))); User Id = " + aws_user + "; Password = "+ aws_pwd +";");

        }
        public void AddToToDoList()
        {
            ...
        }
        public List<ToDoModel> GetToDoList()
        {
            ...
        }
    }
}