Post
Ask Question

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:
  1. namespace CoreCRUDwithOracle.Models {  
  2.     public class Student {  
  3.         public int Id {  
  4.             get;  
  5.             set;  
  6.         }  
  7.         public string Name {  
  8.             get;  
  9.             set;  
  10.         }  
  11.         public string Email {  
  12.             get;  
  13.             set;  
  14.         }  
  15.     }  
  16. }  
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:
  1. {  
  2.     "Logging": {  
  3.         "LogLevel": {  
  4.             "Default": "Information",  
  5.             "Microsoft": "Warning",  
  6.             "Microsoft.Hosting.Lifetime": "Information"  
  7.         }  
  8.     },  
  9.     //"ConnectionStrings": {  
  10.     // "OracleDBConnection": "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourhostName)(PORT=1521)))(CONNECT_DATA=(SID=SEC)));User ID=yourUserId;Password=YourPassword"  
  11.     //},  
  12.     "ConnectionStrings": {  
  13.         "OracleDBConnection": "User Id=yourUserId;Password=yourPassword; Data Source=yourDataSource;"  
  14.     },  
  15.     "AllowedHosts": "*"  
  16. }  
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:
  1. using CoreCRUDwithOracle.Interface;  
  2. using CoreCRUDwithOracle.Services;  
  3. using Microsoft.AspNetCore.Builder;  
  4. using Microsoft.AspNetCore.Hosting;  
  5. using Microsoft.Extensions.Configuration;  
  6. using Microsoft.Extensions.DependencyInjection;  
  7. using Microsoft.Extensions.Hosting;  
  8. namespace CoreCRUDwithOracle {  
  9.     public class Startup {  
  10.         public Startup(IConfiguration configuration) {  
  11.             Configuration = configuration;  
  12.         }  
  13.         public IConfiguration Configuration {  
  14.             get;  
  15.         }  
  16.         // This method gets called by the runtime. Use this method to add services to the container.  
  17.         public void ConfigureServices(IServiceCollection services) {  
  18.             services.AddTransient < IStudentService, StudentService > ();  
  19.             services.AddSingleton < IConfiguration > (Configuration);  
  20.             services.AddMvc().AddRazorPagesOptions(options => {  
  21.                 options.Conventions.AddPageRoute("/Student/Index", "");  
  22.             });  
  23.         }  
  24.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
  25.         public void Configure(IApplicationBuilder app, IWebHostEnvironment env) {  
  26.             if (env.IsDevelopment()) {  
  27.                 app.UseDeveloperExceptionPage();  
  28.             } else {  
  29.                 app.UseExceptionHandler("/Home/Error");  
  30.                 // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.  
  31.                 app.UseHsts();  
  32.             }  
  33.             app.UseHttpsRedirection();  
  34.             app.UseStaticFiles();  
  35.             app.UseRouting();  
  36.             app.UseAuthorization();  
  37.             app.UseEndpoints(endpoints => {  
  38.                 endpoints.MapControllerRoute(name: "default", pattern: "{controller=Student}/{action=Index}/{id?}");  
  39.             });  
  40.         }  
  41.     }  
  42. }  
Step 11
 
Now add code inside of the interface IStudentService
 
Your IStudentService interface file will look like the below code:
  1. using CoreCRUDwithOracle.Models;  
  2. using System.Collections.Generic;  
  3. namespace CoreCRUDwithOracle.Interface {  
  4.     public interface IStudentService {  
  5.         IEnumerable < Student > GetAllStudent();  
  6.         Student GetStudentById(int eid);  
  7.         void AddStudent(Student student);  
  8.         void EditStudent(Student student);  
  9.         void DeleteStudent(Student student);  
  10.     }  
  11. }  
Step 12
 
Now add code inside of the StudentService.
 
Your StudentService class file will look like below:
  1. using CoreCRUDwithOracle.Interface;  
  2. using CoreCRUDwithOracle.Models;  
  3. using Microsoft.Extensions.Configuration;  
  4. using Oracle.ManagedDataAccess.Client;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. namespace CoreCRUDwithOracle.Services {  
  8.     public class StudentService: IStudentService {  
  9.         private readonly string _connectionString;  
  10.         public StudentService(IConfiguration _configuratio) {  
  11.             _connectionString = _configuratio.GetConnectionString("OracleDBConnection");  
  12.         }  
  13.         public IEnumerable < Student > GetAllStudent() {  
  14.             List < Student > studentList = new List < Student > ();  
  15.             using(OracleConnection con = new OracleConnection(_connectionString)) {  
  16.                 using(OracleCommand cmd = con.CreateCommand()) {  
  17.                     con.Open();  
  18.                     cmd.CommandText = "Select ID, FirstName,LastName,EmailId,Mobile,Course from Student";  
  19.                     OracleDataReader rdr = cmd.ExecuteReader();  
  20.                     while (rdr.Read()) {  
  21.                         Student stu = new Student {  
  22.                             Id = Convert.ToInt32(rdr["Id"]),  
  23.                                 Name = rdr["Name"].ToString(),  
  24.                                 Email = rdr["Email"].ToString()  
  25.                         };  
  26.                         studentList.Add(stu);  
  27.                     }  
  28.                 }  
  29.             }  
  30.             return studentList;  
  31.         }  
  32.         public Student GetStudentById(int eid) {  
  33.             Student student = new Student();  
  34.             using(OracleConnection con = new OracleConnection(_connectionString)) {  
  35.                 using(OracleCommand cmd = con.CreateCommand()) {  
  36.                     con.Open();  
  37.                     cmd.CommandText = "Select * from Student where Id=" + eid + "";  
  38.                     OracleDataReader rdr = cmd.ExecuteReader();  
  39.                     while (rdr.Read()) {  
  40.                         Student stu = new Student {  
  41.                             Id = Convert.ToInt32(rdr["Id"]),  
  42.                                 Name = rdr["Name"].ToString(),  
  43.                                 Email = rdr["Email"].ToString()  
  44.                         };  
  45.                         student = stu;  
  46.                     }  
  47.                 }  
  48.             }  
  49.             return student;  
  50.         }  
  51.         public void AddStudent(Student student) {  
  52.             try {  
  53.                 using(OracleConnection con = new OracleConnection(_connectionString)) {  
  54.                     using(OracleCommand cmd = new OracleCommand()) {  
  55.                         con.Open();  
  56.                         cmd.CommandText = "Insert into Student(Id, Name, Email)Values(" + student.Id + ",'" + student.Name + "','" + student.Email + "'')";  
  57.                         cmd.ExecuteNonQuery();  
  58.                     }  
  59.                 }  
  60.             } catch {  
  61.                 throw;  
  62.             }  
  63.         }  
  64.         public void EditStudent(Student student) {  
  65.             try {  
  66.                 using(OracleConnection con = new OracleConnection(_connectionString)) {  
  67.                     using(OracleCommand cmd = new OracleCommand()) {  
  68.                         con.Open();  
  69.                         cmd.CommandText = "Update Student Set Name='" + student.Name + "', Email='" + student.Email + "' where Id=" + student.Id + "";  
  70.                         cmd.ExecuteNonQuery();  
  71.                     }  
  72.                 }  
  73.             } catch {  
  74.                 throw;  
  75.             }  
  76.         }  
  77.         public void DeleteStudent(Student student) {  
  78.             try {  
  79.                 using(OracleConnection con = new OracleConnection(_connectionString)) {  
  80.                     using(OracleCommand cmd = new OracleCommand()) {  
  81.                         con.Open();  
  82.                         cmd.CommandText = "Delete from Student where Id=" + student.Id + "";  
  83.                         cmd.ExecuteNonQuery();  
  84.                     }  
  85.                 }  
  86.             } catch {  
  87.                 throw;  
  88.             }  
  89.         }  
  90.     }  
  91. }  
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:
  1. using System.Collections.Generic;  
  2. using CoreCRUDwithOracle.Interface;  
  3. using CoreCRUDwithOracle.Models;  
  4. using Microsoft.AspNetCore.Mvc;  
  5. namespace CoreCRUDwithOracle.Controllers {  
  6.     public class StudentController: Controller {  
  7.         IStudentService studentService;  
  8.         public StudentController(IStudentService _studentService) {  
  9.             studentService = _studentService;  
  10.         }  
  11.         public ActionResult Index() {  
  12.             IEnumerable < Student > student = studentService.GetAllStudent();  
  13.             return View(student);  
  14.         }  
  15.         public ActionResult Create() {  
  16.                 return View();  
  17.             }  
  18.             [HttpPost]  
  19.         public ActionResult Create(Student student) {  
  20.             studentService.AddStudent(student);  
  21.             return RedirectToAction(nameof(Index));  
  22.         }  
  23.         public ActionResult Edit(int id) {  
  24.                 Student student = studentService.GetStudentById(id);  
  25.                 return View(student);  
  26.             }  
  27.             [HttpPost]  
  28.         public ActionResult Edit(Student student) {  
  29.             studentService.EditStudent(student);  
  30.             return RedirectToAction(nameof(Index));  
  31.         }  
  32.         public ActionResult Delete(int id) {  
  33.                 Student student = studentService.GetStudentById(id);  
  34.                 return View(student);  
  35.             }  
  36.             [HttpPost]  
  37.         public ActionResult Delete(Student student) {  
  38.             studentService.DeleteStudent(student);  
  39.             return RedirectToAction(nameof(Index));  
  40.         }  
  41.     }  
  42. }  
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:
  1. @model IEnumerable  
  2. <CoreCRUDwithOracle.Models.Student>  
  3.   
  4.   
  5. @{  
  6.   
  7. ViewData["Title"] = "Index";  
  8.   
  9. }  
  10.   
  11.   
  12.   
  13.     <h1>Index</h1>  
  14.     <p>  
  15.         <a asp-action="Create">Create New</a>  
  16.     </p>  
  17.     <table class="table">  
  18.         <thead>  
  19.             <tr>  
  20.                 <th>  
  21.   
  22. @Html.DisplayNameFor(model => model.Id)  
  23.   
  24. </th>  
  25.                 <th>  
  26.   
  27. @Html.DisplayNameFor(model => model.Name)  
  28.   
  29. </th>  
  30.                 <th>  
  31.   
  32. @Html.DisplayNameFor(model => model.Email)  
  33.   
  34. </th>  
  35.                 <th></th>  
  36.             </tr>  
  37.         </thead>  
  38.         <tbody>  
  39.   
  40. @foreach (var item in Model) {  
  41.   
  42.   
  43.             <tr>  
  44.                 <td>  
  45.   
  46. @Html.DisplayFor(modelItem => item.Id)  
  47.   
  48. </td>  
  49.                 <td>  
  50.   
  51. @Html.DisplayFor(modelItem => item.Name)  
  52.   
  53. </td>  
  54.                 <td>  
  55.   
  56. @Html.DisplayFor(modelItem => item.Email)  
  57.   
  58. </td>  
  59.                 <td>  
  60.   
  61. @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |  
  62.   
  63. @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |  
  64.   
  65. @Html.ActionLink("Delete", "Delete", new { id=item.Id })  
  66.   
  67. </td>  
  68.             </tr>  
  69.   
  70. }  
  71.   
  72.   
  73.         </tbody>  
  74.     </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!
Next Recommended Reading Database Connections In ASP.NET Core 2.0