1. IronPDF
2. IronPDF Blog
3. .NET Help
4. csharp-postgresql

Published April 4, 2024

C# PostgreSQL (How It Works For Developers)

Welcome to this tutorial designed for beginners interested in integrating C# applications with PostgreSQL. PostgreSQL is among the most used relational databases worldwide, known for its reliability and compatibility with a vast array of programming environments, including C#. This guide will walk you through the basics of connecting a C# application to a PostgreSQL database, executing SQL statement queries, and handling data. We'll use tools like Visual Studio, NuGet Package Manager, and the Npgsql data provider to create a simple project that communicates with a PostgreSQL server. We'll also learn about the IronPDF library with the integration of PostgreSQL.

Setting Up Your Environment

Before diving into coding, ensure you have Visual Studio installed on your computer. Visual Studio is a popular integrated development environment (IDE) that supports C# among other programming languages. For database management, install PostgreSQL on your local machine or set up a PostgreSQL database in a cloud environment like Azure Database.

After setting up Visual Studio and your PostgreSQL server, create a new C# project. You can do this by opening Visual Studio, going to the File menu, selecting New, and then Project. Choose a Console App (.NET Core) as your project type to keep things simple.

Integrating PostgreSQL with C#

To connect your C# application to a PostgreSQL database, you need the Npgsql data provider. Npgsql acts as a bridge between C# applications and PostgreSQL databases, enabling your code to execute SQL commands and manage data.

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

Dim connectionString As String = "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; }
}

Public Class Employee
	Public Property Id() As Integer ' -  Automatically becomes the primary key
	Public Property LastName() As String
End Class

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

Public Class AppDbContext
	Inherits DbContext

	Public Property Employees() As DbSet(Of Employee) ' -  Represents the Employees table
	Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
		Dim connectionString As String = "Host=localhost; Port=5432; Username=postgres; Password=your_password; Database=your_database"
		optionsBuilder.UseNpgsql(connectionString)
	End Sub
	Protected Overrides Sub OnModelCreating(ByVal modelBuilder As ModelBuilder)
		modelBuilder.Entity(Of Employee)().ToTable("Employees")
	End Sub
End Class

DbSet Property: public DbSetEmployees { 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 configure entity behaviors further. 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();
            if (!context.Employees.Any())
            {
                context.Employees.Add(new Employee { LastName = "Software" });
                context.SaveChanges();
            }
            var employees = context.Employees.Where(e => e.LastName == "Doe").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();
            if (!context.Employees.Any())
            {
                context.Employees.Add(new Employee { LastName = "Software" });
                context.SaveChanges();
            }
            var employees = context.Employees.Where(e => e.LastName == "Doe").ToList();
            foreach (var employee in employees)
            {
                Console.WriteLine($"Employee ID: {employee.Id}, Last Name: {employee.LastName}");
            }
        }
    }
}

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Using context = New AppDbContext()
			context.Database.EnsureCreated()
			If Not context.Employees.Any() Then
				context.Employees.Add(New Employee With {.LastName = "Software"})
				context.SaveChanges()
			End If
			Dim employees = context.Employees.Where(Function(e) e.LastName = "Doe").ToList()
			For Each employee In employees
				Console.WriteLine($"Employee ID: {employee.Id}, Last Name: {employee.LastName}")
			Next employee
		End Using
	End Sub
End Class

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. We can add a SQL query to drop table in Postgres database. We can also add a .NET data provider for our database.

Output

Here is the console output when you run the program:

And it is the table data in PgAdmin:

Introduction to IronPDF

IronPDF is a comprehensive library for C# that enables developers to create, edit, and manipulate PDF documents within .NET applications. This powerful tool simplifies generating PDFs from HTML, URLs, and images. It also provides essential PDF operations such as editing text, and images, and adding security features like encryption and digital signatures. IronPDF stands out for its ease of use, allowing developers to implement complex PDF functionalities with minimal code.

Integrating IronPDF with a PostgreSQL database can be incredibly useful in scenarios where you need to generate PDF reports or documents based on dynamic data stored in your database. This could range from generating invoices, reports, customer statements, and more, directly from the data persisted in a PostgreSQL database.

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

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		IronPdf.License.LicenseKey = "Key"
		' Initialize the database context
		Using context = New AppDbContext()
			' Fetch employees from the database
			Dim employees = context.Employees.ToList()
			' Generate HTML content for the PDF
			Dim htmlContent = "<h1>Employee Report</h1>"
			htmlContent &= "<table><tr><th>ID</th><th>Last Name</th></tr>"
			For Each employee In employees
				htmlContent &= $"<tr><td>{employee.Id}</td><td>{employee.LastName}</td></tr>"
			Next employee
			htmlContent &= "</table>"
			' Instantiate the IronPDF HtmlToPdf converter
			Dim renderer = New ChromePdfRenderer()
			' Generate the PDF document from the HTML content
			Dim pdf = renderer.RenderHtmlAsPdf(htmlContent)
			' Save the generated PDF to a file
			Dim outputPath = "f:\EmployeeReport.pdf"
			pdf.SaveAs(outputPath)
			Console.WriteLine($"PDF report generated: {outputPath}")
		End Using
	End Sub
End Class

Output

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

This PDF is generated:

Conclusion

You've just taken a significant first step into the world of database management with C# and PostgreSQL. By following the instructions in this tutorial, you've learned how to set up a project in Visual Studio, install the necessary packages, and execute basic database operations. As you become more familiar with these concepts, you'll discover the power and flexibility of combining C# with one of the most important relational database systems. Keep experimenting with different queries and entity configurations to deepen your understanding of how C# interacts with PostgreSQL.

IronPDF provides a free trial that allows developers to explore its features and capabilities without any initial investment. This trial is particularly useful for evaluating how well IronPDF meets your project's requirements for generating, editing, and converting PDF documents in .NET applications. After the trial period or for production use, acquiring a license is necessary. Licensing for IronPDF starts at $749, offering a range of features and support options suitable for different development needs.