Snowflake in EJ2 JavaScript Pivotview control

2 May 20239 minutes to read

This section describes how to retrieve data from Snowflake database using Snowflake Data library and bind it to the Pivot Table via a Web API controller.

Create a Web API service to fetch Snowflake data

1. Open Visual Studio and create an ASP.NET Core Web App project type, naming it MyWebService. To create an ASP.NET Core Web application, follow the document link.

2. To connect a Snowflake Server using the Snowflake.Data.Client in our application, we need to install the Snowflake.Data NuGet package. To do so, open the NuGet package manager of the project solution, search for the package Snowflake.Data and install it.

3. Create a Web API controller (aka, PivotController.cs) file under Controllers folder that helps to establish data communication with the Pivot Table.

4. In the Web API controller (aka, PivotController), SnowflakeDbConnection helps to connect the Snowflake database. Next, using SnowflakeDbDataAdapter you can process the desired Snowflake query string and retrieve data from the database. The Fill method of the SnowflakeDbDataAdapter is used to populate the retrieved data into a DataTable as shown in the following code snippet.

     using Microsoft.AspNetCore.Mvc;
     using Snowflake.Data.Client;
     using Newtonsoft.Json;
     using System.Data;

     namespace MyWebService.Controllers
     {
          [ApiController]
          [Route("[controller]")]
          public class PivotController : ControllerBase
          {
               public static DataTable FetchSnowflakeResult()
               {
                    using (SnowflakeDbConnection snowflakeConnection = new SnowflakeDbConnection())
                    {
                         // Replace with your own connection string.
                         snowflakeConnection.ConnectionString = "<Enter your valid connection string here>";
                         snowflakeConnection.Open();
                         SnowflakeDbDataAdapter adapter = new SnowflakeDbDataAdapter("select * from CALL_CENTER", snowflakeConnection);
                         DataTable dataTable = new DataTable();
                         adapter.Fill(dataTable);
                         snowflakeConnection.Close();
                         return dataTable;
                    }
               }
          }
     }

5. In the Get() method of the PivotController.cs file, the FetchSnowflakeResult method is used to retrieve the Snowflake data as a DataTable, which is then serialized into JSON using JsonConvert.SerializeObject().

     using Microsoft.AspNetCore.Mvc;
     using Snowflake.Data.Client;
     using Newtonsoft.Json;
     using System.Data;

     namespace MyWebService.Controllers
     {
          [ApiController]
          [Route("[controller]")]
          public class PivotController : ControllerBase
          {
               [HttpGet(Name = "GetSnowflakeResult")]
               public object Get()
               {
                    return JsonConvert.SerializeObject(FetchSnowflakeResult());
               }

               public static DataTable FetchSnowflakeResult()
               {
                    using (SnowflakeDbConnection snowflakeConnection = new SnowflakeDbConnection())
                    {
                         // Replace with your own connection string.
                         snowflakeConnection.ConnectionString = "<Enter your valid connection string here>";
                         snowflakeConnection.Open();
                         SnowflakeDbDataAdapter adapter = new SnowflakeDbDataAdapter("select * from CALL_CENTER", snowflakeConnection);
                         DataTable dataTable = new DataTable();
                         adapter.Fill(dataTable);
                         snowflakeConnection.Close();
                         return dataTable;
                    }
               }
          }
     }

6. Run the web application and it will be hosted within the URL https://localhost:44378/.

7. Finally, the retrieved data from Snowflake database which is in the form of JSON can be found in the Web API controller available in the URL link hhttps://localhost:44378/Pivot, as shown in the browser page below.

Connecting the Pivot Table to a Snowflake database using the Web API service

1. Create a simple Javascript Pivot Table by following the “Getting Started” documentation link.

2. Map the hosted Web API’s URL link https://localhost:44378/Pivot to the Pivot Table component in index.js by using the url property under dataSourceSettings.

var pivotObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        url: 'https://localhost:44378/Pivot',
        //Other codes here...
    }
});
pivotObj.appendTo('#PivotView');

3. Frame and set the report based on the data retrieved from the Snowflake database.

var pivotObj = new ej.pivotview.PivotView({
     dataSourceSettings: {
          url: 'https://localhost:44378/Pivot',
          enableSorting: true,
          expandAll: false,
          dataSource: [],
          columns: [
               { name: 'CC_COUNTRY', caption: 'Country' }
          ],
          rows: [
               { name: 'CC_STATE', caption: 'State' },
               { name: 'CC_CITY', caption: 'City' }
          ],
          values: [
               { name: 'CC_COMPANY', caption: 'Company' },
               { name: 'CC_EMPLOYEES', caption: 'Employees' },
               { name: 'CC_TAX_PERCENTAGE', caption: 'Tax percentage' },
          ],
          filters: []
     },
    showFieldList: true,
    width: '100%'
});
pivotObj.appendTo('#PivotView');

When you run the sample, the resulting pivot table will look like this:

Explore our Javascript Pivot Table sample and ASP.NET Core Web Application to extract data from a Snowflake database and bind to the Pivot Table in this GitHub repository.