C# PostgreSQL (How It Works For Developers)

Installing Npgsql

Open your newly created project in Visual Studio. Right-click on your project in the Solution Explorer, select "Manage NuGet Packages", and search for the Npgsql package. Install it by clicking on the install button next to the package name. This action adds the Npgsql data provider to your project, allowing your application to communicate with PostgreSQL. You can also install it using the package manager console.

Configuring the Database Connection

The first step in interacting with a PostgreSQL database from C# is to establish a connection. This requires a connection string, which includes details such as the server name, port, user name, and password. Here's a basic template for a PostgreSQL connection string:

string connectionString = "Host=localhost; Port=5432; Username=postgres; Password=yourpassword; Database=mydatabase";

string connectionString = "Host=localhost; Port=5432; Username=postgres; Password=yourpassword; Database=mydatabase";

Replace localhost, yourpassword, and mydatabase with your PostgreSQL server's details.

Defining the Employee Model

We define an Employee entity model that will represent our data in the PostgreSQL database. This model includes properties that correspond to the columns in the database table.

public class Employee
{
    public int Id { get; set; } // Automatically becomes the primary key
    public string LastName { get; set; }
}

public class Employee
{
    public int Id { get; set; } // Automatically becomes the primary key
    public string LastName { get; set; }
}

This code snippet defines a simple Employee class with two properties: Id and LastName. Entity Framework Core uses conventions to infer that the Id serial primary key property should be treated as the primary key.

Configuring the Application's DbContext

The AppDbContext class extends DbContext from Entity Framework Core, acting as a bridge between your C# application and the PostgreSQL database. It includes configuration details such as the connection string and the DbSet properties that represent tables in the database.

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; } // Represents the Employees table

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = "Host=localhost; Port=5432; Username=postgres; Password=your_password; Database=your_database";
        optionsBuilder.UseNpgsql(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().ToTable("Employees");
    }
}

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; } // Represents the Employees table

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = "Host=localhost; Port=5432; Username=postgres; Password=your_password; Database=your_database";
        optionsBuilder.UseNpgsql(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().ToTable("Employees");
    }
}

• DbSet Property: public DbSet Employees { get; set; } declares a set of Employee entities that are mapped to the employee table in the PostgreSQL database.

• OnConfiguring Method: This method configures the DbContext with the necessary database connection string. Replace your_password and your_database with your actual PostgreSQL server details.

• OnModelCreating Method: Here, you can use the Fluent API to further configure entity behaviors. In this example, we specify the table name explicitly, although it's optional if the table name matches the DbSet property name.

Main Program Logic

In the Program class's Main method, we ensure the database is created, seed it with initial data if empty, and then perform a query to retrieve and display employee data.

class Program
{
    static void Main(string[] args)
    {
        using (var context = new AppDbContext())
        {
            context.Database.EnsureCreated();  // Ensure the database and schema are created
            if (!context.Employees.Any())  // Check if the Employees table is empty
            {
                context.Employees.Add(new Employee { LastName = "Software" });
                context.SaveChanges();  // Save changes to the database
            }
            var employees = context.Employees.Where(e => e.LastName == "Software").ToList();
            foreach (var employee in employees)
            {
                Console.WriteLine($"Employee ID: {employee.Id}, Last Name: {employee.LastName}");
            }
        }
    }
}

class Program
{
    static void Main(string[] args)
    {
        using (var context = new AppDbContext())
        {
            context.Database.EnsureCreated();  // Ensure the database and schema are created
            if (!context.Employees.Any())  // Check if the Employees table is empty
            {
                context.Employees.Add(new Employee { LastName = "Software" });
                context.SaveChanges();  // Save changes to the database
            }
            var employees = context.Employees.Where(e => e.LastName == "Software").ToList();
            foreach (var employee in employees)
            {
                Console.WriteLine($"Employee ID: {employee.Id}, Last Name: {employee.LastName}");
            }
        }
    }
}

The above code checks if the database exists and creates it along with the schema if it doesn't. It's a straightforward way to bootstrap a new database during development. This SQL statement checks, that if the Employees table is empty, the program adds a new Employee with the last name "Software" and saves the changes to the database. The program queries the Employees table for entries with the last name "Software" and prints their details to the console.

Output

Here is the console output when you run the program:

And it is the table data in PgAdmin:

Installing IronPDF

Before you can use IronPDF, you must add it to your project. This is easily done via the NuGet Package Manager:

Install-Package IronPdf

Generating a PDF from PostgreSQL Data

In this example, let's generate a simple PDF report that lists employees from our PostgreSQL database. We'll assume you have the AppDbContext and Employee model set up as described in previous sections.

First, ensure you have the IronPDF library installed in your project. Then, you can use the following code to fetch data from the PostgreSQL database and generate a PDF report:

class Program
{
    static void Main(string[] args)
    {
        IronPdf.License.LicenseKey = "Key";
        // Initialize the database context
        using (var context = new AppDbContext())
        {
            // Fetch employees from the database
            var employees = context.Employees.ToList();
            // Generate HTML content for the PDF
            var htmlContent = "<h1>Employee Report</h1>";
            htmlContent += "<table><tr><th>ID</th><th>Last Name</th></tr>";
            foreach (var employee in employees)
            {
                htmlContent += $"<tr><td>{employee.Id}</td><td>{employee.LastName}</td></tr>";
            }
            htmlContent += "</table>";
            // Instantiate the IronPDF HtmlToPdf converter
            var renderer = new ChromePdfRenderer();
            // Generate the PDF document from the HTML content
            var pdf = renderer.RenderHtmlAsPdf(htmlContent);
            // Save the generated PDF to a file
            var outputPath = "f:\\EmployeeReport.pdf";
            pdf.SaveAs(outputPath);
            Console.WriteLine($"PDF report generated: {outputPath}");
        }
    }
}

class Program
{
    static void Main(string[] args)
    {
        IronPdf.License.LicenseKey = "Key";
        // Initialize the database context
        using (var context = new AppDbContext())
        {
            // Fetch employees from the database
            var employees = context.Employees.ToList();
            // Generate HTML content for the PDF
            var htmlContent = "<h1>Employee Report</h1>";
            htmlContent += "<table><tr><th>ID</th><th>Last Name</th></tr>";
            foreach (var employee in employees)
            {
                htmlContent += $"<tr><td>{employee.Id}</td><td>{employee.LastName}</td></tr>";
            }
            htmlContent += "</table>";
            // Instantiate the IronPDF HtmlToPdf converter
            var renderer = new ChromePdfRenderer();
            // Generate the PDF document from the HTML content
            var pdf = renderer.RenderHtmlAsPdf(htmlContent);
            // Save the generated PDF to a file
            var outputPath = "f:\\EmployeeReport.pdf";
            pdf.SaveAs(outputPath);
            Console.WriteLine($"PDF report generated: {outputPath}");
        }
    }
}

Output

When you run the code, this console output will show:

This PDF is generated: