Get Free Trial
  1. API reference
Connecting to Data Source
  1. Supported data sources
  2. Connecting to other data sources
Configuring the component
  • Documentation for older versions
  • Table of contents

    A quick overview of a sample .NET Core server

    For a quick start in using the Flexmonster custom data source API (our custom communication protocol), 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.

    Prerequisites

    To run the sample .NET Core server, you need Microsoft .NET Core 3.0 or 3.1. Get it here if it’s not already installed on your machine.

    To start working with the sample .NET Core server, follow these guides:

    Download the sample .NET Core server

    To try our sample .NET Core server, download the .zip archive with the sample project or clone it from GitHub with the following command:

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

    The sample .NET Core server is in the server-dotnetcore/ folder.

    All requests from Flexmonster Pivot Table are handled by the http://localhost:3400/api/cube endpoint. Raw data is stored in JSON and CSV formats in the data/ folder.

    Available configurations

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

    Property/Type Description
    DataSources
    DataSourceConfigObject[]
    Configures the data sources.
    DataStorageOptions
    DataStorageOptionsObject
    optional Configures the options for data storage.

    DataSourceConfigObject

    Property/Type Description
    Type
    String
    The type of the data source: "json""csv", or "database".
    DatabaseType
    String
    optional The type of the database: "mysql""mssql""postgresql", or "oracle". Only for the "database" data source type.
    ConnectionString
    String
    optional A connection string for the database. Only for the "database" data source type.
    Indexes
    Object
    Contains a list of datasets. Each dataset is represented by a "key": "value" pair, where "key" is the dataset name, and "value" is an IndexObject.

    IndexObject

    Property/Type Description
    Path
    String
    optional The path to the file with data. Only for "json" and "csv" data source types.
    Query
    String
    optional The query to execute (e.g., "SELECT * FROM tablename"). Only for the "database" data source type.
    Delimiter
    String
    optional Defines the fields separator to split each CSV row. Only for the "csv" data source type.
    Default value: ",".

    DataStorageOptionsObject

    Property/Type Description
    DataRefreshTime
    Number
    optional Defines how often the data is reloaded from a file or a database. The refresh time is set in minutes. If the DataRefreshTime is not specified, the data will not be reloaded.

    Connect to the data source

    The sample .NET Core server configurations vary depending on the data source type. See the following guides to connect the sample .NET Core server to a data source:

    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. Here is an example:

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

    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"
           }
       }
    }

    To start the sample .NET Core Server, refer to the Run the sample .NET Core server guide.

    To see how the connection with the sample .NET Core server is configured in the component, refer to the Configure the report section.

    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": ";"
    }

    To start the sample .NET Core Server, refer to the Run the sample .NET Core server guide.

    To see how the connection with the sample .NET Core server is configured in the component, refer to the Configure the report section.

    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")

    To start the sample .NET Core Server, refer to the Run the sample .NET Core server guide.

    To see how the connection with the sample .NET Core server is configured in the component, refer to the Configure the report section.

    Run the sample .NET Core server

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

    cd server-dotnetcore
    dotnet restore
    dotnet run

    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.

    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.

    Configure the report

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

    new Flexmonster({
        container: "pivotContainer",
        report: {
            dataSource: {
                type: "api",
                url: "http://localhost:3400/api/cube",
                index: "index_json"
            }
        }
    });

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

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

    The cache is fully cleared only when the server is restarted, although it has a memory limit: when the limit is reached, and a new response can’t be cached, the .NET Core server deletes one of the previously cached responses from the cache.

    What’s next?

    You may be interested in the following articles: