.NET Core with Oracle Database

Question

Hi members,

I want to connect an Oracle Database to .Net core MVC v3.1or5. I want to call Oracle View and populate in the Grid view . Pls explain how to do step by step.Appreciatable your needful help.

Sachin Singh · 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 GetAll() { List empList = new List (); 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.

Sachin Singh · Answer

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# Learner · Answer

Thank you very much again.For the individual questions => individual answers are acceptable.

C# Learner · Answer

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.