A quick overview of a sample .NET Core server

For a quick start in using the custom data source API, we have prepared a sample .NET Core server that implements it. The sample .NET Core server allows loading data from CSV, JSON, as well as from several databases.

Additionally, you can check out our sample Node.js server, which also implements the custom data source API.

Prerequisites

• .NET Core 6.0 or later

Step 1. Download the sample .NET Core server

To get our sample project, download it as ZIP or clone it with the following commands:

git clone https://github.com/flexmonster/api-data-source
cd api-data-source 

The sample .NET Core server is in the server-dotnetcore/ folder. Raw data is stored in JSON and CSV formats in the server-dotnetcore/data/ folder.

Learn more about available server configurations.

Step 2. Run the sample .NET Core server

To start the server, run the following commands in a console:

cd server-dotnetcore
dotnet restore
dotnet run

All requests from Flexmonster Pivot Table are handled by the http://localhost:3400/api/cube endpoint. This is the default configuration that you can modify.

As soon as you start the sample .NET Core server, it automatically preloads the data specified in the Indexes property. Thus, when Flexmonster Pivot requests the data, the server responds with the already preloaded data. To connect to your data, read the following section: Connect to the data source.

Note The preloaded data is kept in the server’s RAM, so the number of indexes you can specify is limited by the amount of RAM available to the server.

Step 3. Configure the report

On the client side, the report should be configured as follows:

new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  report: {
    dataSource: {
      type: "api",
      // A url of our sample server
      url: "http://localhost:3400/api/cube",
      index: "fm-product-sales"
    }
  }
});

Note The index must match the name of the index defined when configuring the data source (e.g., "fm-product-sales").

Available configurations

The sample .NET Core server can be configured in the appsettings.json file, which contains the following properties:

{
  DataSources: DataSourceConfigObject[],
  DataStorageOptions: DataStorageOptionsObject
}

DataSourceConfigObject

This object allows configuring a data source. It has the following properties:

{
  Type: string,
  DatabaseType: string,
  ConnectionString: string,
  Indexes: object
}

IndexObject

This object describes a specific dataset. It has the following properties:

{
  Path: string,  
  Delimiter: string,
  Query: string
}

DataStorageOptionsObject

This object allows configuring options for data storage. It has the following properties:

{
  DataRefreshTime: number
}

Connect to the data source

The sample .NET Core server configurations vary depending on the data source type: JSON, CSV, or database.

Connecting to JSON

The sample .NET Core server supports only a specific JSON format – an array of objects, where each object is an unordered set of "key": "value" pairs

[
  {
    "Color": "green",
    "Country": "Canada",
    "State": "Ontario",
    "City": "Toronto",
    "Price": 174,
    "Quantity": 22
  },
  // ...
]

Note You can create your own implementation for other JSON formats.

To connect to a JSON data source with the sample .NET Core server, specify the Type and Indexes properties in the appsettings.json file. For example:

"DataSources": [
  {
    "Type": "json",
    "Indexes": {
      "index_json": {
        "Path": "./data/data.json"
      }
    }
  }
], 

"index_json" is a dataset identifier. It will be used to configure the data source on the client side. Additional indexes can be specified like this:

{
  "Type": "json",
  "Indexes": {
    "index_json": {
      "Path": "./data/data.json"
    },
    "another_index_json": {
      "Path": "./data/another_data.json"
    }
  }
}

Connecting to CSV

To connect to a CSV data source with the sample .NET Core server, specify the Type and Indexes properties in the appsettings.json file. For example:

"DataSources": [
  {
    "Type": "csv",
    "Indexes": {
      "index_csv": {
        "Path": "./data/data.csv"
      }
    }
  }
], 

"index_csv" is a dataset identifier. It will be used to configure the data source on the client side. Additional indexes can be specified like this:

{
  "Type": "csv",
  "Indexes": {
    "index_csv": {
      "Path": "./data/data.csv"
    },
    "another_index_csv": {
      "Path": "./data/another_data.csv"
    }  
  }
}

If CSV fields are not separated by "," but by another character, the Delimiter parameter should be specified:

"index_csv": {
  "Path": "./data/data.csv",
  "Delimiter": ";"
} 

Connecting to databases

The sample .NET Core server supports MySQL, Microsoft SQL Server, PostgreSQL, Oracle, and Microsoft Azure SQL databases.

To connect to a database with the sample .NET Core server, specify the Type, DatabaseType, ConnectionString, and Indexes properties in the appsettings.json file. For example:

{
  "DataSources": [
    {
      "Type": "database",
      "DatabaseType": "mysql"
      "ConnectionString":
        "Server=localhost;Port=3306;Uid=root;Pwd=password;Database=database_name",
      "Indexes": {
        "index_database": {
          "Query": "SELECT * FROM tablename"
        }
      }
    }
  ]
} 

"index_database" is a dataset identifier. It will be used to configure the data source on the client side.

ConnectionString is a connection string for the database. Here are some example connection strings for each supported database type:

• MySQL: "Server=localhost;Port=3306;Uid=;Pwd=;Database= "
• Microsoft SQL Server: "Server=(localdb)\\MSSQLLocalDB;Uid=;Pwd=;Database= "
• PostgreSQL: "Server=localhost;Port=5432;Uid=;Pwd=;Database= "
• Oracle: "Data Source=ORCL;User Id=;Password=;"
• Microsoft Azure SQL: Server=tcp:myserver.database.windows.net,1433;Database= ;User ID=;Password=;Trusted_Connection=False;Encrypt=True; (to connect to Microsoft Azure SQL, set the "DatabaseType" to "mssql")

About response caching

When Flexmonster Pivot requests data, the sample .NET Core server caches a response and then sends it. If the component sends the same request again, the server responds with the data from its cache.

The server’s cache has a limit. When the cache does not have enough space for a new response, the .NET Core server deletes one of the previously cached responses.

The server clears the cache when restarted.

What's next?

You may be interested in the following articles:

• Implementing the custom data source API on your server
• Supporting more aggregation functions
• Implementing filters