Oracle in Angular Pivot Table

6 Aug 202610 minutes to read

This guide explains how to retrieve data from an Oracle database using the Oracle Managed Data Access library and bind it to the Pivot Table through a Web API controller.

Creating a Web API Service to Fetch Oracle Data

Follow these steps to create a Web API service that retrieves data from an Oracle database and prepares it for the Pivot Table.

Step 1: Create an ASP.NET Core Web Application

1. Open Visual Studio and create a new ASP.NET Core Web App project named MyWebService.
2. Follow the official Microsoft documentation for detailed instructions on creating an ASP.NET Core Web application.

Step 2: Install the Oracle NuGet Package

To enable Oracle database connectivity:

1. Open the NuGet Package Manager in your project solution and search for Oracle.ManagedDataAccess.Core.
2. Install the Oracle.ManagedDataAccess.Core package to add Oracle support.

Step 3: Create a Web API Controller

1. Under the Controllers folder, create a new Web API controller named PivotController.cs.
2. This controller facilitates data communication between the Oracle database and the Pivot Table.

Step 4: Connect to Oracle and Retrieve Data

In the PivotController.cs file, use the Oracle Managed Data Access library to connect to an Oracle database and retrieve data for the Pivot Table.

1. Establish Connection: Use OracleConnection with a valid connection string (e.g., Data Source=localhost;User Id=myuser;Password=mypassword;) to connect to the Oracle database.
2. Query and Fetch Data: Execute a SQL query (e.g., SELECT * FROM EMPLOYEES) using OracleCommand to retrieve data for the Pivot Table.
3. Structure the Data: Use OracleDataAdapter’s Fill method to populate query results into a DataTable for JSON serialization.

using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace MyWebService.Controllers
{