.NET Core with Oracle Database

You have 2 options, you can use EF Core with provider Oracle.EntityFrameworkCore or you can use ADO.NET with the provider Oracle.ManagedDataAccess.Core. I am going with the second option.

step 1. install the provider using the package manager console

Install-Package Oracle.ManagedDataAccess.Core

step 2. set the connection string, open appsetting.json, and specify your connection string

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"ConnectionStrings": {
"OracleDBConnection": "User Id=yourUserId;Password=yourPassword; Data Source=yourDataSource;"
},
"AllowedHosts": "*"
}

step 3. Design the model

public class Employee{
public int Id {
get;
set;
}
public string Name {
get;
set;
}
public string Email {
get;
set;
}
}

step 4. Write ADO.NET Code and do your work. Open the controller and follow the below code

public class HomeController: Controller
{
private readonly string _connectionString;
public HomeController(IConfiguration _configuration) {
_connectionString = _configuration.GetConnectionString("OracleDBConnection");
}
public IEnumerable < Employee> GetAll() {
List < Employee> empList = new List < Employee> ();
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = con.CreateCommand()) {
con.Open();
cmd.CommandText = "Select ID, FirstName,LastName,EmailId,Mobile,Course from Employee";
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
Employee emp= new Employee{
Id = Convert.ToInt32(rdr["Id"]),
Name = rdr["Name"].ToString(),
Email = rdr["Email"].ToString()
};
empList.Add(emp);
}
}
}
return empList;
}

step 6. Loop through the list in the view and display data in table

@model IEnumerable
class="table">
@Html.DisplayNameFor(model => model.Id)
@Html.DisplayNameFor(model => model.Name)
@Html.DisplayNameFor(model => model.Email)
@foreach (var item in Model) {
@Html.DisplayFor(modelItem => item.Id)
@Html.DisplayFor(modelItem => item.Name)
@Html.DisplayFor(modelItem => item.Email)
@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Id })
}

Thanks.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
You have 2 options, you can use EF Core with provider Oracle.EntityFrameworkCore or you can use ADO.NET with the provider Oracle.ManagedDataAccess.Core. I am going with the second option.

step 1. install the provider using the package manager console
Install-Package Oracle.ManagedDataAccess.Core
step 2. set the connection string, open appsetting.json, and specify your connection string
{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },

    "ConnectionStrings": {
        "OracleDBConnection": "User Id=yourUserId;Password=yourPassword; Data Source=yourDataSource;"
    },
    "AllowedHosts": "*"
}
step 3. Design the model
public class Employee{
        public int Id {
            get;
            set;
        }
        public string Name {
            get;
            set;
        }
        public string Email {
            get;
            set;
        }
    }
step 4. Write ADO.NET Code and do your work. Open the controller and follow the below code
public class HomeController: Controller
{

private readonly string _connectionString;
        public HomeController(IConfiguration _configuration) {
            _connectionString = _configuration.GetConnectionString("OracleDBConnection");
        }
        public IEnumerable < Employee> GetAll() {
            List < Employee> empList = new List < Employee> ();
            using(OracleConnection con = new OracleConnection(_connectionString)) {
                using(OracleCommand cmd = con.CreateCommand()) {
                    con.Open();
                    cmd.CommandText = "Select ID, FirstName,LastName,EmailId,Mobile,Course from Employee";
                    OracleDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read()) {
                        Employee emp= new Employee{
                            Id = Convert.ToInt32(rdr["Id"]),
                                Name = rdr["Name"].ToString(),
                                Email = rdr["Email"].ToString()
                        };
                        empList.Add(emp);
                    }
                }
            }
            return empList;
        }
step 6. Loop through the list in the view and display data in table
@model IEnumerable

class="table">




@Html.DisplayNameFor(model => model.Id)




@Html.DisplayNameFor(model => model.Name)




@Html.DisplayNameFor(model => model.Email)







@foreach (var item in Model) {





@Html.DisplayFor(modelItem => item.Id)




@Html.DisplayFor(modelItem => item.Name)




@Html.DisplayFor(modelItem => item.Email)




@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |

@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |

@Html.ActionLink("Delete", "Delete", new { id=item.Id })




}




Thanks.
C# Learner5y ago
Thank you very much again.For the individual questions => individual answers are acceptable.
Sachin Singh5y ago
With EF Core, if you follow the code first approach then you create domain entities anyway, which can be used as models and you won't need to create an explicit model.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity(
            eb =>
            {
                eb.HasNoKey();
                eb.ToView("view_Employees");
                eb.Property(v => v.Name).HasColumnName("Name");
            });
}
var res= db.Employees.ToList(); // will fetch data from view
C# Learner5y ago
Many Thanks, Sachin to your valuable information,

Would you please tell if there is any option like Entity framework core TableAdapter designer which we usually use in Winforms,MVC, etc. same can we use here in .NET Core MVC Project?
For easy configuration (//docs.microsoft.com/en-us/visualstudio/data-tools/create-and-configure-tableadapters?view=vs-2019)

If i have 20 views to use do i need to create 20 models in code ?Is there any fast method to perform the job with less writing code?

please share your knowledge on this subject.