Oracle Data Binding in ASP.NET Core Pivot Table

28 Aug 202612 minutes to read

This section describes how to retrieve data from Oracle database using Oracle Managed Data Access and bind it to the Pivot Table via 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. Select the Web API project template (for example, ASP.NET Core Web API in Visual Studio 2022) so the project is configured with controllers and Swagger by default.
2. Follow the official Microsoft documentation for detailed instructions on creating an ASP.NET Core Web application.
3. Before proceeding, ensure that an Oracle Database instance is running locally (or reachable on the network) and that the EMPLOYEES table exists with sample data. The connection string and table name used later in this walkthrough assume the example dataset from the walkthrough’s GitHub sample.

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. Use Oracle.ManagedDataAccess.Core version 21.x (or later) to match this walkthrough; pin the version if you want reproducible builds. Note: the legacy Oracle.ManagedDataAccess package targets .NET Framework only; for ASP.NET Core, always use Oracle.ManagedDataAccess.Core.

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.Core;
     using Newtonsoft.Json;
     using Oracle.ManagedDataAccess.Client;
     using System.Data;

     namespace MyWebService.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;
               }
          }
     }

Step 5: Serialize Data to JSON

In the PivotController.cs file, define a Get method that calls FetchOracleResult to retrieve data from the Oracle database as a DataTable. Then, use JsonConvert.SerializeObject from the Newtonsoft.Json library to convert the DataTable into JSON format. This JSON data will be used by the Pivot Table component.

Ensure the Newtonsoft.Json NuGet package (version 13.x or later) is installed in your project before using JsonConvert. The Get method serializes the DataTable into a JSON string before ASP.NET Core’s pipeline returns it as the response body. Note: returning a JsonConvert.SerializeObject of a DataTable produces a JSON array of row objects whose column values are mapped from the underlying Oracle column types.

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

     namespace MyWebService.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;
               }
          }
     }

Step 6: Run the Web API Service

1. In Visual Studio, set MyWebService as the startup project and press F5 (or run dotnet run from the project folder). The actual listening ports are read from launchSettings.json; both HTTP and HTTPS endpoints are printed in the console.
2. The application will be hosted at https://localhost:44346/ (the port number may vary based on your configuration). Note the exact URL printed by the runtime so you can reference it from the ASP.NET Core project.

Step 7: Access the JSON Data

1. Access the Web API endpoint at https://localhost:44346/Pivot to view the JSON data retrieved from the Oracle database.
2. The browser displays the JSON data, as shown in the image below.

Connecting the Pivot Table to an Oracle Database Using the Web API Service

This section explains how to connect the Pivot Table component to an Oracle database by retrieving data from the Web API service created in the previous section. Ensure that the Web API service from the previous section is still running before proceeding.

Step 1: Set up the ASP.NET Core Pivot Table

1. Set up a basic ASP.NET Core Pivot Table by following the Getting Started documentation.
2. Install the Syncfusion ASP.NET Core Tag Helper package by running dotnet add package Syncfusion.EJ2.AspNet.Core (the package is registered automatically and the _ViewImports.cshtml file is updated to import the Tag Helpers).
3. Register the Syncfusion license key in Startup.cs (or Program.cs for .NET 6+) as described in the Syncfusion Getting Started documentation.
4. Add the required EJ2 client-side references (for example, ej2.min.js, ej2-pivotview.min.js, and the matching theme CSS) in ~/Views/Shared/_Layout.cshtml as described in the Getting Started documentation.

Step 2: Configure the Web API URL in the Pivot Table

1. In the ~/Views/Home/Index.cshtml file, map the Web API URL (https://localhost:44346/Pivot) to the Pivot Table using the url property within the e-datasourcesettings.
2. Below is the sample code to configure the Pivot Table to fetch data from the Web API:

<ejs-pivotview id="PivotView" height="300" showFieldList="true">
    <e-datasourcesettings Url="https://localhost:44346/pivot" expandAll="false" enableSorting="true">
     //Other codes here...
    </e-datasourcesettings>
</ejs-pivotview>

Step 3: Define the Pivot Table Report

1. Configure the Pivot Table report in the ~/Views/Home/Index.cshtml file to structure the data retrieved from the Oracle database.
2. Add fields to the rows, columns, values, and filters properties of e-datasourcesettings to define the report structure, specifying how data fields are organized and aggregated in the Pivot Table.
3. Enable the field list by setting the showFieldList property to true on the PivotView component (not on the data source settings) and including the FieldList module in the services section. This allows users to dynamically add or rearrange fields across the columns, rows, and values axes using an interactive user interface. Note: enableSorting is a property of the PivotView component; the sample above demonstrates the equivalent Tag Helper usage on the data source settings.

Here’s the updated sample code with the report configuration and field list support:

<ejs-pivotview id="PivotView" height="300" showFieldList="true">
    <e-datasourcesettings Url="https://localhost:44346/Pivot" expandAll="false" enableSorting="true">
        <e-rows>
            <e-field name="JOB" caption="Job"></e-field>
            <e-field name="SALARY" caption="Salary"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="DEPARTMENT_ID" caption="Department ID"></e-field>
            <e-field name="EMPLOYEE_NAME" caption="Employee Name"></e-field>
        </e-columns>
        <e-values>
            <e-field name="EMPLOYEE_ID" caption="Employee ID"></e-field>
            <e-field name="CC_EMPLOYEES" caption="Employees"></e-field>
            <e-field name="CC_TAX_PERCENTAGE" caption="Percentage"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>

Step 4: Run and Verify the Pivot Table

1. Run the ASP.NET Core application.
2. The Pivot Table will display the data fetched from the Oracle database via the Web API, structured according to the defined report.
3. The resulting Pivot Table will look like this:

Additional Resources

Explore a complete example of the ASP.NET Core Pivot Table integrated with an ASP.NET Core Web Application to fetch data from an Oracle database in this GitHub repository.