CRUD Operations In ASP.NET Core-3.1 Using Oracle Database

Introduction

In this post, my agenda is to create an ASP.NET Core-3.1 web application to show all of the CRUD operations using Oracle Database and ADO.NET. As of now, there is no support for an entity framework with Oracle in the newer version of .Net Core. I hope this blog will help you to achieve your target!

Step 1 - Create an ASP.NET Core Application

Give the proper name to the application, "CoreCRUDwithOracle"

Select Template as Model-View-Controller

Step 2

Please check this video to complete step by step flow

Install Oracle Manage Data Access for Core "Oracle.ManagedDataAccess.Core"

Right-click on the project
Go to "Manage NuGet Packages..."
Move to Browse Tab, then move to the Search area
Type "Oracle.ManagedDataAccess.Core" in the search box

Here, you will see a list of packages. Choose the first one and move to the right-side panel of the window.

You will see one option as Version: (If you already have not installed it, if you already installed, then you will see Installed and Version)

Click on the install button and follow the steps to install successfully.

Step 3

Add a Services folder in the application

Step 4

Add an Interface folder in the application

Step 5

Add a Student class in the Models folder

Your class will look like the below code:

namespace CoreCRUDwithOracle.Models {
public class Student {
public int Id {
get;
set;
}
public string Name {
get;
set;
}
public string Email {
get;
set;
}
}
}

Step 6

Add IStudentService interface inside the Interface folder

Step 7

Add StudentService class in the Services folder

Step 8

Add StudentController inside of the Controllers folder

Step 9

Now add the Oracle Connection String in the appsettings.json file.

After adding a connection string, the appsettings.json file will look like below:

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
//"ConnectionStrings": {
// "OracleDBConnection": "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourhostName)(PORT=1521)))(CONNECT_DATA=(SID=SEC)));User ID=yourUserId;Password=YourPassword"
//},
"ConnectionStrings": {
"OracleDBConnection": "User Id=yourUserId;Password=yourPassword; Data Source=yourDataSource;"
},
"AllowedHosts": "*"
}

Step 10

Now open the Startup.cs file and add code inside the function "ConfigureServices()"

After adding code, your Startup.cs class file will look like below:

using CoreCRUDwithOracle.Interface;
using CoreCRUDwithOracle.Services;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
namespace CoreCRUDwithOracle {
public class Startup {
public Startup(IConfiguration configuration) {
Configuration = configuration;
}
public IConfiguration Configuration {
get;
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services) {
services.AddTransient < IStudentService, StudentService > ();
services.AddSingleton < IConfiguration > (Configuration);
services.AddMvc().AddRazorPagesOptions(options => {
options.Conventions.AddPageRoute("/Student/Index", "");
});
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env) {
if (env.IsDevelopment()) {
app.UseDeveloperExceptionPage();
} else {
app.UseExceptionHandler("/Home/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints => {
endpoints.MapControllerRoute(name: "default", pattern: "{controller=Student}/{action=Index}/{id?}");
});
}
}
}

Step 11

Now add code inside of the interface IStudentService

Your IStudentService interface file will look like the below code:

using CoreCRUDwithOracle.Models;
using System.Collections.Generic;
namespace CoreCRUDwithOracle.Interface {
public interface IStudentService {
IEnumerable < Student > GetAllStudent();
Student GetStudentById(int eid);
void AddStudent(Student student);
void EditStudent(Student student);
void DeleteStudent(Student student);
}
}

Step 12

Now add code inside of the StudentService.

Your StudentService class file will look like below:

using CoreCRUDwithOracle.Interface;
using CoreCRUDwithOracle.Models;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
namespace CoreCRUDwithOracle.Services {
public class StudentService: IStudentService {
private readonly string _connectionString;
public StudentService(IConfiguration _configuratio) {
_connectionString = _configuratio.GetConnectionString("OracleDBConnection");
}
public IEnumerable < Student > GetAllStudent() {
List < Student > studentList = new List < Student > ();
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = con.CreateCommand()) {
con.Open();
cmd.CommandText = "Select ID, FirstName,LastName,EmailId,Mobile,Course from Student";
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
Student stu = new Student {
Id = Convert.ToInt32(rdr["Id"]),
Name = rdr["Name"].ToString(),
Email = rdr["Email"].ToString()
};
studentList.Add(stu);
}
}
}
return studentList;
}
public Student GetStudentById(int eid) {
Student student = new Student();
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = con.CreateCommand()) {
con.Open();
cmd.CommandText = "Select * from Student where Id=" + eid + "";
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
Student stu = new Student {
Id = Convert.ToInt32(rdr["Id"]),
Name = rdr["Name"].ToString(),
Email = rdr["Email"].ToString()
};
student = stu;
}
}
}
return student;
}
public void AddStudent(Student student) {
try {
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = new OracleCommand()) {
con.Open();
cmd.CommandText = "Insert into Student(Id, Name, Email)Values(" + student.Id + ",'" + student.Name + "','" + student.Email + "'')";
cmd.ExecuteNonQuery();
}
}
} catch {
throw;
}
}
public void EditStudent(Student student) {
try {
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = new OracleCommand()) {
con.Open();
cmd.CommandText = "Update Student Set Name='" + student.Name + "', Email='" + student.Email + "' where Id=" + student.Id + "";
cmd.ExecuteNonQuery();
}
}
} catch {
throw;
}
}
public void DeleteStudent(Student student) {
try {
using(OracleConnection con = new OracleConnection(_connectionString)) {
using(OracleCommand cmd = new OracleCommand()) {
con.Open();
cmd.CommandText = "Delete from Student where Id=" + student.Id + "";
cmd.ExecuteNonQuery();
}
}
} catch {
throw;
}
}
}
}

Step 13

Add the below code inside the StudentController.

Now add the required ActionMethod and respective views.

After adding the required Action Method, your Controller will look like below:

using System.Collections.Generic;
using CoreCRUDwithOracle.Interface;
using CoreCRUDwithOracle.Models;
using Microsoft.AspNetCore.Mvc;
namespace CoreCRUDwithOracle.Controllers {
public class StudentController: Controller {
IStudentService studentService;
public StudentController(IStudentService _studentService) {
studentService = _studentService;
}
public ActionResult Index() {
IEnumerable < Student > student = studentService.GetAllStudent();
return View(student);
}
public ActionResult Create() {
return View();
}
[HttpPost]
public ActionResult Create(Student student) {
studentService.AddStudent(student);
return RedirectToAction(nameof(Index));
}
public ActionResult Edit(int id) {
Student student = studentService.GetStudentById(id);
return View(student);
}
[HttpPost]
public ActionResult Edit(Student student) {
studentService.EditStudent(student);
return RedirectToAction(nameof(Index));
}
public ActionResult Delete(int id) {
Student student = studentService.GetStudentById(id);
return View(student);
}
[HttpPost]
public ActionResult Delete(Student student) {
studentService.DeleteStudent(student);
return RedirectToAction(nameof(Index));
}
}
}

Here, I'm not adding code for the View. Please follow the steps to add view.

Let's understand how to add a view to the Index Action Method.

Move to the Controller class and go to the Index method
In the Index method section, right-click. You will see a new window. Select the first option "Add View..."
A new popup window will be opened:

View Name will be Index, so no need to change that
Template: Select List from the dropdown
Model Class: Select Student model class from the dropdown you will see after selection like "Student (CoreCRUDwithOracle.Models)"
Now, in the final step, click on the "Add" button to add a view to display a list of students

Your Index "Index.cshtml" view will look like below:

@model IEnumerable
<CoreCRUDwithOracle.Models.Student>
@{
ViewData["Title"] = "Index";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Id)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Email)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Id)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Email)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Id })
</td>
</tr>
}
</tbody>
</table>

Now add the view for Add, Edit and Delete functionality

For Add:

Template: Select Create from the dropdown

For Edit:

Template: Select Edit from the dropdown

For Delete:

Template: Select Delete from the dropdown

Note

Model Class name "Student (CoreCRUDwithOracle.Models)" will be the same for all those functionalities.

In Summary, by using .Net Core 3.0 with Oracle database and ADO.NET we are able to perform Read, Create, Edit and Delete operations.

It is just like we used to do in ASP.NET MVC, but in the ASP.NET Core, we are using dependency injection in the Startup.cs file and a connection string in the appsettings.json file. To connect with the Oracle database, we also used one package, "Oracle.ManagedDataAccess.Core". This is a very important step to connect with the Oracle database.

That's all for this blog, friends. If you found this blog helpful, please like it and provide your feedback. That will help me to provide better content.

Thank you, and good luck!

4 Comments

Join the conversation! Your thoughts help the community grow.

Jatin Bhambhani3y ago
Hey D.K., Thanks for this. Do you know how I can script this startup class in Program class as .Net core 6.0 does not support startup class anymore explicitly?
Nadim Hossain Sonet3y ago
Where is the source code ?
Yuri Melo5y ago
This code snippet doesn't work with me: using(OracleCommand cmd = new OracleCommand()) { con.Open(); cmd.CommandText = "Insert into Student(Id, Name, Email)Values(" + student.Id + ",'" + student.Name + "','" + student.Email + "'')"; cmd.ExecuteNonQuery(); }
SANTOSH DE6y ago
Hi Gautam, do you have the same project using razor pages?