Blazor - Connect With Oracle Database In Amazon RDS

Introduction

In this article, we will see how to create an Oracle database instance in Amazon RDS (AWS Relational Database Service) and we will connect to the Oracle database from the Blazor application. We will use the Employee details Single Page Application for this purpose.

Prerequisites

AWS Account
Visual Studio 2017 (any edition) with .NET SDK 2.1 or later
Blazor Language Support

Please refer to my C# Corner article to know the details about AWS free tier account creation.

If you are new to the Blazor framework, please refer to my below articles in C# Corner to learn the basics of the Blazor framework.

Create an Oracle Database instance in Amazon RDS

Login to the AWS console

Select Database -> RDS -> Create database -> Oracle

Create database

Choose the Oracle Edition. If you are using the AWS free tier, please choose Only enable options eligible for the RDS Free Usage Tier and click the “Next” button to proceed.

Oracle Edition

Give Oracle DB instance and master username and password details.

Oracle DB

Give the Database options. Here you must provide the default database name and port details. By default, the database name is “ORCL” and the Port is “1521”. You can proceed with all the default values.

Database options

Please choose the “Enable deletion protection” option if you want to avoid the database being deleted accidentally. You can’t delete the database if you select this option. Please click the “Create database” button to proceed.

Enable deletion protection

You will be notified with a message as shown below. You can click “View DB instance details” to get the database creation details.

View DB instance details

You can see that the DB instance status is “creating”. It will take some time to finish the instance creation.

DB instance status

After some time, our database is created successfully. We can change the inbound IP rule. So that database can be accessed from outside AWS.

Inbound IP rule

In my system IP address often changes. So, I will change the rule to Anywhere. So that I can access the Oracle database from any IP address. It is not suggested for the production database.

Edit Inbound rules

Connect Oracle Database with Oracle SQL Developer

We have successfully created Oracle database and modified the inbound IP rule. Now we can connect the Oracle database from Oracle SQL Developer tool. Oracle SQL Developer tool is a fee IDE for Oracle administration developed by Oracle corporation.

You can click the “+” to add a new connection.

Oracle connection

We can give the Oracle instance details to create a new connection. You can get the Hostname from AWS console.

AWS console

We have successfully connected to Oracle database. Now we can create an Employee table

Employee table

CREATE TABLE Employee (
    Id VARCHAR2(50) PRIMARY KEY,
    Name VARCHAR2(50),
    Department VARCHAR2(50),
    Designation VARCHAR2(50),
    Gender VARCHAR2(10)
);

Create Blazor Application

We can create a Blazor application now. I am creating an Employee data Single Page Application.

Open Visual Studio 2017 and choose .NET Core -> ASP.NET Core Web Application template

Visual Studio 2017

Currently there are three types of Blazor templates available. We can choose Blazor (ASP.NET Core hosted) template.

ASP.NET Core hosted

Our solution will be ready in a moment. Please note that there are three projects created in our solution - “Client”, “Server”, and “Shared”.

Blazor oracle server

By default, Blazor created many files in these three projects. We can remove all the unwanted files like “Counter.cshtml”, “FetchData.cshtml”, “SurveyPrompt.cshtml” from Client project and “SampleDataController.cs” file from Server project and delete “WeatherForecast.cs” file from shared project too.

We can create a new “Models” folder in “Shared” project and add Employee class inside this folder.

Employee.cs

namespace BlazorOracle.Shared.Models
{
    public class Employee
    {
        public string Id
        {
            get;
            set;
        }
        public string Name
        {
            get;
            set;
        }
        public string Department
        {
            get;
            set;
        }
        public string Designation
        {
            get;
            set;
        }
        public string Gender
        {
            get;
            set;
        }
    }
}

We will use “Oracle.ManagedDataAccess.Core” NuGet package to connect Oracle database from Blazor application. We can install this package from NuGet manager in “Server” project

NuGet package

We can create a “DataAccess” folder in “Server” project and create “OracleDBContext” class inside this folder. This class contains all the logic for Oracle CRUD operations. We will call all these methods from our API controller later.

Please copy below code and paste to this class.

OracleDBContext.cs

using BlazorOracle.Shared.Models;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BlazorOracle.Server.DataAccess
{
    public class OracleDBContext
    {
        public string ConnectionString { get; set; }

        public OracleDBContext(string connectionString)
        {
            ConnectionString = connectionString;
        }

        private OracleConnection GetConnection()
        {
            return new OracleConnection(ConnectionString);
        }

        public async Task<List<Employee>> GetAllAsync()
        {
            List<Employee> list = new List<Employee>();

            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Id,Name,Department,Designation,Gender FROM Employee";
                OracleCommand cmd = new OracleCommand(commandText, conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (await reader.ReadAsync())
                    {
                        list.Add(new Employee()
                        {
                            Id = await reader.GetFieldValueAsync<string>(0),
                            Name = await reader.GetFieldValueAsync<string>(1),
                            Department = await reader.GetFieldValueAsync<string>(2),
                            Designation = await reader.GetFieldValueAsync<string>(3),
                            Gender = await reader.GetFieldValueAsync<string>(4),
                        });
                    }
                }
            }
            return list;
        }

        public async Task InsertAsync(Employee employee)
        {
            employee.Id = Guid.NewGuid().ToString();

            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"INSERT INTO Employee (Id,Name,Department,Designation,Gender) VALUES (:Id, :Name, :Department, :Designation, :Gender)";

                OracleCommand cmd = new OracleCommand(commandText, conn);

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = employee.Id,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Name",
                    DbType = DbType.String,
                    Value = employee.Name,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Department",
                    DbType = DbType.String,
                    Value = employee.Department,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Designation",
                    DbType = DbType.String,
                    Value = employee.Designation,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Gender",
                    DbType = DbType.String,
                    Value = employee.Gender,
                });

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task UpdateAsync(Employee employee)
        {
            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"UPDATE Employee SET Name=:Name, Department=:Department, Designation=:Designation, Gender=:Gender  Where Id=:Id";

                OracleCommand cmd = new OracleCommand(commandText, conn);

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Name",
                    DbType = DbType.String,
                    Value = employee.Name,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Department",
                    DbType = DbType.String,
                    Value = employee.Department,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Designation",
                    DbType = DbType.String,
                    Value = employee.Designation,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Gender",
                    DbType = DbType.String,
                    Value = employee.Gender,
                });

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = employee.Id,
                });

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task DeleteAsync(string id)
        {
            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"DELETE FROM Employee Where Id=:Id";

                OracleCommand cmd = new OracleCommand(commandText, conn);

                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = id,
                });

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<Employee> FindOneAsync(string id)
        {
            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Name,Department,Designation,Gender FROM Employee Where Id=:Id";
                OracleCommand cmd = new OracleCommand(commandText, conn);
                cmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = id,
                });

                using (var reader = cmd.ExecuteReader())
                {
                    if (await reader.ReadAsync())
                    {
                        return new Employee()
                        {
                            Id = id,
                            Name = await reader.GetFieldValueAsync<string>(0),
                            Department = await reader.GetFieldValueAsync<string>(1),
                            Designation = await reader.GetFieldValueAsync<string>(2),
                            Gender = await reader.GetFieldValueAsync<string>(3),
                        };
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }
    }
}

We are using “Oracle.ManagedDataAccess.Client” class from “Oracle.ManagedDataAccess.Core” library to establish connection and command in Oracle database.

We can inject this class from Startup class. We will provide the Oracle connection string in this class.

Startup.cs

using BlazorOracle.Server.DataAccess;
using Microsoft.AspNetCore.Blazor.Server;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.ResponseCompression;
using Microsoft.Extensions.DependencyInjection;
using System.Linq;
using System.Net.Mime;

namespace BlazorOracle.Server
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            services.AddResponseCompression(options =>
            {
                options.MimeTypes = ResponseCompressionDefaults.MimeTypes.Concat(new[]
                {
                    MediaTypeNames.Application.Octet,
                    WasmMediaTypeNames.Application.Wasm,
                });
            });

            var connStr = "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sarathlal.cah1w3njil8m.ap-south-1.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA=(SID=ORCL)));User Id=sarath;Password=<your password>;";
            services.Add(new ServiceDescriptor(typeof(OracleDBContext), new OracleDBContext(connStr)));
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            app.UseResponseCompression();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc(routes =>
            {
                routes.MapRoute(name: "default", template: "{controller}/{action}/{id?}");
            });

            app.UseBlazor<Client.Program>();
        }
    }
}

We can create our Web API Controller now.

Web API Controller

Please copy below code and paste to this controller class.

EmployeesController.cs

using BlazorOracle.Server.DataAccess;
using BlazorOracle.Shared.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorOracle.Server.Controllers
{
    [Route("api/[controller]")]
    public class EmployeesController : Controller
    {
        [HttpGet]
        public async Task<IEnumerable<Employee>> GetAsync()
        {
            OracleDBContext context = HttpContext.RequestServices.GetService(typeof(OracleDBContext)) as OracleDBContext;
            return await context.GetAllAsync();
        }

        [HttpGet("{id}")]
        public async Task<Employee> Get(string id)
        {
            OracleDBContext context = HttpContext.RequestServices.GetService(typeof(OracleDBContext)) as OracleDBContext;
            return await context.FindOneAsync(id);
        }

        [HttpPost]
        public async Task Post([FromBody] Employee employee)
        {
            if (ModelState.IsValid)
            {
                OracleDBContext context = HttpContext.RequestServices.GetService(typeof(OracleDBContext)) as OracleDBContext;
                await context.InsertAsync(employee);
            }
        }

        [HttpPut]
        public async Task Put([FromBody] Employee employee)
        {
            if (ModelState.IsValid)
            {
                OracleDBContext context = HttpContext.RequestServices.GetService(typeof(OracleDBContext)) as OracleDBContext;
                await context.UpdateAsync(employee);
            }
        }

        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            OracleDBContext context = HttpContext.RequestServices.GetService(typeof(OracleDBContext)) as OracleDBContext;
            await context.DeleteAsync(id);
        }
    }
}

We have added all CRUD actions in this controller class.

We can go to “Client” project and modify the “NavMenu.cshtml” Razor View. This Razor file is used to create menu bar.

NavMenu.cshtml

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Blazor Oracle App</a>
    <button class="navbar-toggler" onclick=@ToggleNavMenu>
        <span class="navbar-toggler-icon"></span>
    </button>
</div>

<div class=@(collapseNavMenu ? "collapse" : null) onclick=@ToggleNavMenu>
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match=NavLinkMatch.All>
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="/listemployees">
                <span class="oi oi-plus" aria-hidden="true"></span> Employee Details
            </NavLink>
        </li>
    </ul>
</div>

@functions {
    bool collapseNavMenu = true;

    void ToggleNavMenu()
    {
        collapseNavMenu = !collapseNavMenu;
    }
}

We can add a new Razor View now. We will use this file to list the employee details.

Create Razor view

Please copy below code and paste to this Razor view file.

ListEmployees.cshtml

@using BlazorOracle.Shared.Models

@page "/listemployees"
@inject HttpClient Http

<h1>Employee Details</h1>
<p>
    <a href="/addemployee">Create New Employee</a>
</p>
@if (employees == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Department</th>
                <th>Designation</th>
                <th>Gender</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var employee in employees)
            {
                <tr>
                    <td>@employee.Name</td>
                    <td>@employee.Department</td>
                    <td>@employee.Designation</td>
                    <td>@employee.Gender</td>
                    <td>
                        <a href='/editemployee/@employee.Id'>Edit</a>
                        <a href='/deleteemployee/@employee.Id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}

@functions {
    Employee[] employees;

    protected override async Task OnInitAsync()
    {
        employees = await Http.GetJsonAsync<Employee[]>("/api/employees");
    }
}

You can add three more Razor View files in the same way.

AddEmployee.cshtml

@using BlazorOracle.Shared.Models

@page "/addemployee"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Create Employee</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-default" onclick="@(async () => await CreateEmployee())" value="Save" />
                <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>

@functions {
    Employee employee = new Employee();

    protected async Task CreateEmployee()
    {
        await Http.SendJsonAsync(HttpMethod.Post, "/api/employees", employee);
        UriHelper.NavigateTo("/listemployees");
    }

    void Cancel()
    {
        UriHelper.NavigateTo("/listemployees");
    }
}

EditEmployee.cshtml

@using BlazorOracle.Shared.Models

@page "/editemployee/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Edit</h2>
<h4>Employee</h4>
<hr />

<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-default" onclick="@(async () => await UpdateEmployee())" value="Save" />
            <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>

@functions {

[Parameter]
string id { get; set; }

Employee employee = new Employee();

protected override async Task OnInitAsync()
{
    employee = await Http.GetJsonAsync<Employee>("/api/employees/" + id);
}

protected async Task UpdateEmployee()
{
    await Http.SendJsonAsync(HttpMethod.Put, "api/employees", employee);
    UriHelper.NavigateTo("/listemployees");
}

void Cancel()
{
    UriHelper.NavigateTo("/listemployees");
}
}

DeleteEmployee.cshtml

@using BlazorOracle.Shared.Models

@page "/deleteemployee/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Delete</h2>
<p>Are you sure you want to delete this Employee with Id : <b> @id</b></p>
<br />

<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@employee.Name</td>
        </tr>
        <tr>
            <td>Department</td>
            <td>@employee.Department</td>
        </tr>
        <tr>
            <td>Designation</td>
            <td>@employee.Designation</td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@employee.Gender</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" onclick="@(async () => await Delete())" class="btn btn-default" />
        <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
    </div>
</div>

@functions {

[Parameter]
string id { get; set; }

Employee employee = new Employee();

protected override async Task OnInitAsync()
{
    employee = await Http.GetJsonAsync<Employee>("/api/employees/" + id);
}

protected async Task Delete()
{
    await Http.DeleteAsync("api/employees/" + id);
    UriHelper.NavigateTo("/listemployees");
}

void Cancel()
{
    UriHelper.NavigateTo("/listemployees");
}
}

We have successfully completed the coding part. We can run the application.

Application running

We can click the Employee Details link the in left menu and open Employee details page. Currently there are no Employee data available.

We can click the Create new employee link and add employee information and press “Save” button to save data.

Create employee

We can add one more employee data. Now we have two employee records available.

Employee Details

We can modify one employee data by clicking “Edit” link

Edit employee

I have modified the employee designation. We can delete the employee data

Delete

It will show the selected employee information before deletion. You can confirm the deletion by clicking the “Delete” button.

We have seen all CRUD operations for Employee data with this application.

Conclusion

In this article we have created an Oracle database instance in AWS RDS (Relational Database Service) and we connected Oracle database using Oracle SQL Developer tool. We have created a Single Page Blazor application in Visual Studio 2017. We have connected Blazor with Oracle database using “Oracle.ManagedDataAccess.Client” library in “Oracle.ManagedDataAccess.Core” NuGet package. We have seen all the CRUD actions in Blazor application using Employee data.

We can see more Blazor features in upcoming articles.