Dear visitor, despite the COVID-19 outbreak, our team continues operating at full speed.
Also, here is the form where you can apply for a special discount and we will contact you with possible options. Stay safe and continue achieving your business goals.
This tutorial describes how to connect to a PostgreSQL database with the sample .NET Core server implementing the custom data source API.
To run the sample server, you will need .NET Core. Get it here if it’s not already installed on your machine.
Note that you need at least .NET Core 3.0. To check which versions of .NET Core are installed on your computer, run the following commands in the console:
dotnet --list-sdks
dotnet --list-runtimes
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 my-proj
cd my-proj
By default, the server uses the data stored in JSON format. To make the server use data from a database, the "DataSourceName" configuration in server-dotnetcore/appsettings.json should be the following:
"DataSource": {
"DataSourceName": "database",
...
},
Specify the type of database you want to use by adding the "DatabaseType" configuration to the server-dotnetcore/appsettings.json file. In this case, the configuration’s value should be "postgresql":
"DataSource": {
"DataSourceName": "database",
"DatabaseType": "postgresql",
...
},
To enable the server to fetch data from your database, you have to provide the connection string to the database. The connection string should be added to server-dotnetcore/appsettings.json as follows:
"DataSource": {
"DataSourceName": "database",
"DatabaseType": "postgresql",
"ConnectionStrings": {
"DefaultConnection": "User ID=postgres;Password=password;Host=localhost;Port=5432;Database=database_name;"
},
...
},
In the server-dotnetcore/appsettings.json file, create an index for the subset of data you want Flexmonster to visualize. It should be done as follows:
"DataSource": {
"DataSourceName": "database",
"DatabaseType": "postgresql",
"ConnectionStrings": {
"DefaultConnection": "User ID=postgres;Password=password;Host=localhost;Port=5432;Database=database_name;"
},
"Indexes": {
"your-index": "SELECT * FROM yourTableName"
}
},
In this key:value pair, key is your index’s name, and value is a query for the data. You can choose any name for your index.
As soon as you start the sample .NET Core server, it automatically executes the query specified in the "Indexes" property and preloads the received data. Thus, when Flexmonster requests for 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.
By default, data from a database is reloaded every 60 minutes. You can change the refresh time in the "DataRefreshTime" configuration in server-dotnetcore/appsettings.json. For example:
"DataStorageOptions": {
"DataRefreshTime": "1800000"
}
Now the data will be reloaded every 30 minutes.
Note that refresh time is set in milliseconds.
On the client side (see client/index.html), the report should be configured as follows:
new Flexmonster({
container: "#pivot",
report: {
dataSource: {
type: "api",
url: "http://localhost:3400/api/cube",
index: "your-index"
}
}
});
Note 1: index must match the name of the index defined in step 5 (e.g. "your-index").
Note 2: when Flexmonster requests for the data, the sample .NET Core server sends the response and then caches it. In case the component sends the same request once again, the server responds with the data from its cache. The cache is cleared only when the server is restarted, though the cache has a memory limit: when the limit is reached, and the new response can’t be cached, the .NET Core server deletes one of the previously cached responses from the cache.
To start the .NET Core server, run the following commands in a console:
cd server-dotnetcore
dotnet restore
dotnet run
You may be interested in the following articles: