Reporting with PostgreSQL in a .NET 5 application for Debian 10

Many need a solution that will generate reports for Linux systems, and support working with SQL-like databases and not only. We have such a solution FastReport.Core. This library allows you to create reports under different Linux distributions and it can connect to different databases. In this article, we will look at how to implement this on a Debian 10 distribution using PostgreSQL.

First, install PostgreSQL on Debian 10. You can find a detailed installation at the following link. Let's check the Postgres performance by going to the terminal. Switch to the postgres account with the following command:

$ sudo -i -u postgres

After that, write the following to access the postgres shell:

$ psql

Now we have access to the postgres command line, and additionally, we have checked that it works.

Add a password for postgres user instead of 'password123':

ALTER USER postgres WITH PASSWORD ‘password123’

Let's create a test table:

CREATE TABLE test (city varchar(80), temp_lo int, temp_hi int);

Fill it with data:

INSERT INTO test VALUES ('Chicago',30,40);

Done, now check the table for data with the following command. The result is shown in the figure below.

SELECT * FROM test;

To create an application on .NET 5.0, we need to install DotNet itself. Go to the page with detailed installation and look closely at all the necessary points. For more convenient work, you can download VS code at this link. Then download the deb package and install it on your computer.

Download the C# and Nuget Gallery plugins in VS code. The latter is needed for easy search and correct installation of nuget packages.

We create a project in VS code, for this, we press ctrl + J to open the console inside VS code. Then enter this command:

dotnet new console

After creating the project, we need to download and install the necessary libraries. Upon completion of the previous steps, open the Nuget Gallery in a test project.

Find FastReport.Core and install it. Be sure to check the box next to Prerelease, as this package is a demo version, otherwise, the package will not be displayed.

The connector is not a demo version, so you don’t have to check the Prerelease box. It is enough to find FastReport.Data.Postgres in the search bar and install it in the same way as the previous package.

After installing all the necessary components, open Program.cs in our project and paste the following code into the main method:

using System;
using FastReport;
using FastReport.Data;
using FastReport.Utils;
using FastReport.Export.Pdf;
 
static void Main(string[] args)
{
//Creating a connection to PostgreSQL
 
RegisteredObjects.AddConnection(typeof(PostgresDataConnection));
PostgresDataConnection connection = new PostgresDataConnection();
connection.ConnectionString = "Host=localhost;Username=postgres;Password=1234;Database=postgres";
connection.CreateAllTables();
 
//Creating a report and connecting the database and table to the report
 
Report report = new Report();
report.Dictionary.Connections.Add(connection);
connection.Enabled = true;
 
foreach(TableDataSource table in connection.Tables)
{
if(table.Name == "public_test")
{
table.Enabled = true;
}
}
 
ReportPage page = new ReportPage();
report.Pages.Add(page);
page.CreateUniqueName();
 
DataBand dataBand = new DataBand();
page.Bands.Add(dataBand);
dataBand.CreateUniqueName();
 
//Assigning DataBend to our table
 
dataBand.DataSource = report.GetDataSource("public_test");
dataBand.Height = Units.Centimeters * 0.5f;
 
TextObject City = new TextObject();
City.CreateUniqueName();
City.Bounds = new System.Drawing.RectangleF(0,0,100,100);
City.Parent = dataBand;
 
//Assigning values to a text object from a DB field
 
City.Text = "[public_test.city]";
 
TextObject temp_lo = new TextObject();
temp_lo.CreateUniqueName();
temp_lo.Bounds = new System.Drawing.RectangleF(150,0,100,100);
temp_lo.Parent = dataBand;
temp_lo.Text = "[public_test.temp_lo]";
 
TextObject temp_hi = new TextObject();
temp_hi.CreateUniqueName();
temp_hi.Bounds = new System.Drawing.RectangleF(300,0,100,100);
temp_hi.Parent = dataBand;
temp_hi.Text = "[public_test.temp_hi]";
 
report.Prepare();
 
PDFExport pDF = new PDFExport();
pDF.Export(report,"test.pdf");
 
}

Then let’s compile and run our project. It will generate the following PDF report file:

The conclusion will be very simple. Connecting a database to your application is not difficult, just today, we created a report using data from a PostgreSQL database in a Debian 10 distribution.