One of our customers required their Telerik Reporting application to connect with their Postgres SQL (for effect, no not Microsoft SQL Server but the open source Postgres SQL).
While at the outset it may appear to be almost impossible but this task is very easy to implement with Telerik Reporting.
We accomplish this with using ADO.NET driver for Postgres SQL. The same approach can be used for other databases like MySQL.
In this post, we will discuss how to get Telerik Reporting working on .NET Core 3.1.
The first thing is to understand that Telerik Reporting is a framework that has three distinct and independent pieces:
- Telerik Report Definition
- Telerik Reporting Host Application
- Telerik Report Viewer
First you would want to create the Telerik Report Definition (a trdp file). To create this, we use the Telerik Reporting Designer. By default, there is nothing that supports Postgres SQL in there. So, here is the first step – Download the NgpSql driver (the MSI installer).
Once done, this will add a new datasource to the SQL Data Source of the designer:
Click on the SQL Data Source, and add a new data connection. In the dropdown, please select the new available provider “Ngpsql Data Provider”:
Following this you will need to provide the connection string. The Postgres connection string is of the following format:
Host=<server name>;Database=<db name>;Username=<username>;Password=<password>
Provide the relevant SQL statement and complete creating the data connection. The rest of the remaining steps are the same as creating a regular Telerik Report definition.
Make sure that the data is as preview in the report.
The second step, is to configure the hosting application. Since we are now working with .NET core, you can start with the .NET Core WebAPI application template and add relevant nuget packages to the same. Detailed instructions are available here: https://docs.telerik.com/reporting/telerik-reporting-rest-service-aspnetcore-mvc-core3
This will make the host application provide the Telerik Reporting Service. You can check if this the reporting has been correctly setup by browsing to the URL: http://localhost/api/reports/formats
The extra step in the host application is to include an additional nuget package in the host application: Ngpsql
The next change in the host application is changing the connection string and its provider. Specify your connection string in the appSettings.json as follows:
Pay special attention to the providerName above.
Congratulations you are done!!
The third and final piece, the Telerik Report Viewer doesn’t require any changes.
Your host application (in my case a simple HTML 5 application) can now simply render the report from the host application.