MongoDB in EJ2 JavaScript Pivotview control

4 Jul 20239 minutes to read

This section describes how to consume data from MongoDB database using MongoDB Driver and MongoDB Bson libraries and bind it to the Pivot Table via a Web API controller.

Create a Web API service to fetch MongoDB 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 MongoDB Server using the MongoDB.Driver and MongoDB.Bson in our application, we need to install the MongoDB.Driver NuGet package. To do so, open the NuGet package manager of the project solution, search for the package MongoDB.Driver 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), MongoClient helps to connect the MongoDB database. Next, using the GetDatabase and GetCollection methods, you can retrieve data from the database. The Find method of the IMongoDatabase is used to populate the retrieved data into a List, as shown in the following code snippet.

    using Microsoft.AspNetCore.Mvc;
    using Newtonsoft.Json;
    using MongoDB.Driver;
    using MongoDB.Bson;

    namespace MyWebService.Controllers
    {
        [ApiController]
        [Route("[controller]")]
        public class PivotController : ControllerBase
        {
            private static List<ProductDetails> FetchMongoDbResult()
            {
                // Replace with your own connection string.
                string connectionString = "<Enter your valid connection string here>";
                MongoClient client = new MongoClient(connectionString);
                IMongoDatabase database = client.GetDatabase("sample_training");
                var collection = database.GetCollection<ProductDetails>("ProductDetails");
                return collection.Find(new BsonDocument()).ToList();
            }
            public class ProductDetails
            {
                public ObjectId Id { get; set; }
                public int Sold { get; set; }
                public double Amount { get; set; }
                public string? Country { get; set; }
                public string? Products { get; set; }
                public string? Year { get; set; }
                public string? Quarter { get; set; }
            }
        }
    }

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

    using Microsoft.AspNetCore.Mvc;
    using Newtonsoft.Json;
    using MongoDB.Bson;
    using MongoDB.Driver;

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

            private static List<ProductDetails> FetchMongoDbResult()
            {
                // Replace with your own connection string.
                string connectionString = "<Enter your valid connection string here>";
                MongoClient client = new MongoClient(connectionString);
                IMongoDatabase database = client.GetDatabase("sample_training");
                var collection = database.GetCollection<ProductDetails>("ProductDetails");
                return collection.Find(new BsonDocument()).ToList();
            }
            public class ProductDetails
            {
                public ObjectId Id { get; set; }
                public int Sold { get; set; }
                public double Amount { get; set; }
                public string? Country { get; set; }
                public string? Products { get; set; }
                public string? Year { get; set; }
                public string? Quarter { get; set; }
            }
        }
    }

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

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

Connecting the Pivot Table to a MongoDB 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:44346/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:44346/Pivot',
        //Other codes here...
    },
    showFieldList: true,
    width: '100%'
});
pivotObj.appendTo('#PivotView');

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

var pivotObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        url: 'https://localhost:44346/Pivot',
        enableSorting: true,
        columns: [
            { name: 'Year' }
        ],
        values: [
            { name: 'Sold', caption: "Units Sold"},
            { name: 'Amount', caption: "Sold Amount"}
        ],
        rows: [
            { name: 'Country' },
            { name: 'Products' }
        ]
    },
    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 MongoDB database and bind to the Pivot Table in this GitHub repository.