Oracle in JavaScript Pivot Table control
This section describes how to retrieve data from Oracle database using Oracle Managed Data Access library and bind it to the Pivot Table via a Web API controller.
Steps to connect the Oracle database via Web API application
1. Download the ASP.NET Core Web Application from this GitHub repository.
2. The application named as PivotController (server-side) that is downloaded from the above GitHub repository includes the PivotController.cs file under Controllers folder, which is helps to do data communication with Pivot Table.
3. In the Web API controller (aka, PivotController), OracleConnection helps to connect the Oracle database. Next, using OracleCommand and OracleDataAdapter you can process the desired Oracle query string and retrieve data from the database. The Fill method of the OracleDataAdapter is used to populate the retrieved data into a DataTable as shown in the following code snippet.
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System.Data;
namespace PivotController.Controllers
{
[ApiController]
[Route("[controller]")]
public class PivotController : ControllerBase
{
private static DataTable FetchOracleResult()
{
// Replace with your own connection string.
string connectionString = "<Enter your valid connection string here>";
OracleConnection oracleConnection = new OracleConnection(connectionString);
oracleConnection.Open();
OracleCommand command = new OracleCommand("SELECT * FROM EMPLOYEES", oracleConnection);
OracleDataAdapter dataAdapter = new OracleDataAdapter(command);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
oracleConnection.Close();
return dataTable;
}
}
}4. In the Get() method of the PivotController.cs file, the FetchOracleResult() method is used to retrieve the Oracle data, which is then serialized into JSON using JsonConvert.SerializeObject().
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System.Data;
namespace PivotController.Controllers
{
[ApiController]
[Route("[controller]")]
public class PivotController : ControllerBase
{
[HttpGet(Name = "GetOracleResult")]
public object Get()
{
return JsonConvert.SerializeObject(FetchOracleResult());
}
private static DataTable FetchOracleResult()
{
// Replace with your own connection string.
string connectionString = "<Enter your valid connection string here>";
OracleConnection oracleConnection = new OracleConnection(connectionString);
oracleConnection.Open();
OracleCommand command = new OracleCommand("SELECT * FROM EMPLOYEES", oracleConnection);
OracleDataAdapter dataAdapter = new OracleDataAdapter(command);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
oracleConnection.Close();
return dataTable;
}
}
}5. Run the web application (aka, PivotController) and it will be hosted within the URL https://localhost:7029.
6. Finally, the retrieved data from Oracle database which is in the form of JSON can be found in the Web API controller available in the URL link https://localhost:7029/pivot, as shown in the browser page below.
Connecting the Pivot Table to the hosted Web API URL
1. Download the TypeScript Pivot Table sample from this GitHub repository.
2. Next, map the hosted Web API’s URL link https://localhost:7029/pivot to the Pivot Table component in app.ts by using the url property under dataSourceSettings.
import { PivotView, FieldList } from '@syncfusion/ej2-pivotview';
PivotView.Inject(FieldList);
let pivotObj: PivotView = new PivotView({
dataSourceSettings: {
url: 'https://localhost:7029/pivot',
//Other codes here...
}
});
pivotObj.appendTo('#PivotView1');3. Frame and set the report based on the data retrieved from the Oracle database.
import { PivotView, FieldList } from '@syncfusion/ej2-pivotview';
PivotView.Inject(FieldList);
let pivotObj: PivotView = new PivotView({
dataSourceSettings: {
url: 'https://localhost:7029/pivot',
enableSorting: true,
expandAll: false,
dataSource: [],
columns: [
{ name: 'DEPARTMENT_ID', caption: 'Department ID' },
{ name: 'EMPLOYEE_NAME', caption: 'Employee Name' },
],
rows: [
{ name: 'JOB', caption: 'Job' },
{ name: 'SALARY', caption: 'Salary' }
],
values: [
{ name: 'EMPLOYEE_ID', caption: 'Employee ID' },
{ name: 'CC_EMPLOYEES', caption: 'Employees' },
{ name: 'CC_TAX_PERCENTAGE', caption: 'Percentage' },
],
filters: []
},
showFieldList: true,
width: '100%',
height: 290,
gridSettings: { columnWidth: 140 }
});
pivotObj.appendTo('#PivotView');4. Run the sample to get the following result.
