Connect to PostgreSQL with Dapper using C#

This tutorial shows how to perform data operations in .NET applications using Dapper. With dotConnect for PostgreSQL, you can create Dapper models, build a console application, and execute CRUD operations through a reliable connection class that allows efficient and smooth database interaction.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is a powerful ADO.NET data provider designed for efficient interaction with PostgreSQL databases. It supports dynamic (on-the-fly) connector creation, flexible configuration options, and smooth integration with Visual Studio. With advanced ORM support, including compatibility with Entity Framework and Dapper, it simplifies development and streamlines data access in .NET applications.

Requirements

To follow this guide, make sure the following tools and components are available:

• Visual Studio 2022. This guide uses Visual Studio 2022 as the integrated development environment (IDE). If it's not already installed, you can download the free Community Edition from the official website.
• dotConnect for PostgreSQL. A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
• Entity Developer. A visual ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
• DvdRental sample database. A sample PostgreSQL database available from the official PostgreSQL documentation, used here to demonstrate practical examples.

Download and activate dotConnect for PostgreSQL

30-day free trial version

You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:

• Installer:Download and install dotConnect for PostgreSQL directly on your machine.
• NuGet:Add the Devart.Data.PostgreSQL package to your project via NuGet.

No license key is required for the trial—just install and explore.

Full version

Once you've purchased the full version:

1. Go to the Products section of your Devart profile.

2. Locate your product and either click the product name or hover over it and click Details.

The License details page will open, where you'll find your Activation Key.

To activate dotConnect for PostgreSQL in your application, copy the Activation Key and include it in your connection string as the LicenseKey parameter.

Create a .NET Project

To begin working with Dapper and dotConnect for PostgreSQL, you first need to set up a .NET project in Visual Studio. In this tutorial, you'll create a simple console application that connects to a PostgreSQL database, runs basic operations, and demonstrates the use of Dapper for efficient data access. The setup process involves initializing a new project and installing the required NuGet packages.

Follow these steps to get started:

• 1. Open Visual Studio and select Create a new project.
• 2. Choose Console App (.NET Core) or Console App (.NET Framework), depending on your preference, and click Next.
• 3. Name your project — for this tutorial, we'll use PostgreSqlDapper — then click Create.
• 4. Right-click the project in Solution Explorer and select Manage NuGet Packages.
• 5. In the Browse tab, search for and install the following packages:
• Dapper
• Devart.Data.PostgreSql

Check PostgreSQL database objects

To connect to your PostgreSQL database using Visual Studio's built-in Data Explorer:

1. Go to the Tools menu and select Connect to Database.

2. In the Add Connection dialog, choose PostgreSQL as the data source.

3. Enter your server details and credentials, then click Connect.

Once connected, you can use Data Explorer to browse tables, run queries, and manage data directly from within Visual Studio.

Connect to PostgreSQL and retrieve data

In this section, we'll demonstrate how to establish a connection to your PostgreSQL database from the application, execute a query, and display the results from the Customer table in the console.

1. Create a reusable connection class

namespace DapperPostgresExample {
  public static class DatabaseConfig {
     public static string connectionString = "Host=127.0.0.1;Port=5432;UserId=postgres;Password=postgres;Database=postgres;Schema=public;License Key=**********";
  }
}

2. Create a model class for the Customer table

Next, define a class that maps to the Customer table. Create a file named Customer.cs and insert the following code into it:

namespace DapperPostgresExample {
  public class Customer {
    public int Customer_Id { get; set; }
    public string First_Name { get; set; }
    public string Last_Name { get; set; }
    public string Email { get; set; }
  }
}
  

3. Write the main program logic

Now, modify your Program.cs file to connect to the database and fetch customer records:

static void Main(string[] args) {
  using(IDbConnection db = new PgSqlConnection(DatabaseConfig.ConnectionString)) {
      try {
      db.Open();
      Console.WriteLine("Connection to PostgreSQL successful!");
                    
      // Fetch and display customers
      DisplayCustomers(db);
      } catch (Exception ex) {
      Console.WriteLine($"Connection failed: {ex.Message}");
      }
  }
}
                    
private static void DisplayCustomers(IDbConnection db) {
  Console.WriteLine("\nFetching first 10 customers...");
                    
  string selectQuery = "SELECT customer_id AS Customer_Id, first_name AS First_Name, last_name AS Last_Name, email FROM Customer ORDER BY customer_id LIMIT 10;";
  var customers = db.Query<Customer> (selectQuery);
                    
  foreach(var customer in customers) {
     Console.WriteLine($"{customer.Customer_Id}: {customer.First_Name} {customer.Last_Name} - {customer.Email}");
  }
}

4. Run the application

Build and run your application by pressing F5 or selecting Start from the Visual Studio menu. If everything is set up correctly, the console will display the first 10 records from the Customer table.

Batch insert new records using Dapper

This example demonstrates how to insert multiple records into a PostgreSQL database using Dapper within a single transaction. This approach improves performance and ensures that the entire operation is atomic — meaning either all records are inserted, or none if an error occurs.

Update Main method

Replace your existing Main method with the following code to include the batch insert and result display.

static void Main(string[] args) {
  using(IDbConnection db = new PgSqlConnection(DatabaseConfig.connectionString)) {
      // Insert 10 new rows
      InsertNewCustomers(db, 10);
                  
      // Fetch and display the newly inserted customers
      string selectQuery = "SELECT customer_id, first_name, last_name, email FROM customer ORDER BY customer_id DESC LIMIT 10";
      var newCustomers = db.Query<Customer> (selectQuery).ToList();
                  
      Console.WriteLine("Newly Inserted Customers:");
      foreach(var customer in newCustomers) {
      Console.WriteLine($"Customer ID: {customer.Customer_Id}, Name: {customer.First_Name} {customer.Last_Name}, Email: {customer.Email}");
      }
  }
}
                  
static void InsertNewCustomers(IDbConnection db, int count) {
  string insertQuery = @"
  INSERT INTO customer(first_name, last_name, email, store_id, address_id, active, create_date)
  VALUES(:First_Name,:Last_Name,:Email, 1, 1, 1, NOW())
  ";
                  
  for (int i = 0; i < count; i++) {
      db.Execute(insertQuery, new {
      First_Name = $"NewFirstName{i + 1}",
      Last_Name = $"NewLastName{i + 1}",
      Email = $"newemail{i + 1}@@example.com"
      });
  }
}         

Output example

When you run the application, you should see something like this.

Update PostgreSQL data using Dapper

In this example, we'll batch update customers whose customer_id values are between 590 and 599. We'll modify their first and last names, then display the updated records in the console.

Add the following code to your Program.cs file.

static void Main(string[] args) {
  using(IDbConnection db = new PgSqlConnection(DatabaseConfig.connectionString)) {
      // Update customers with Customer_Id between 600 and 609
      string updateQuery = @"
      UPDATE customer
      SET first_name =:FirstName,
      last_name =:LastName
      WHERE customer_id BETWEEN 600 AND 609 ";
                
      db.Execute(updateQuery, new {
      FirstName = "UpdatedFirstName",
      LastName = "UpdatedLastName"
      });
                
      // Fetch and display the updated customers
      string selectQuery = "SELECT customer_id, first_name, last_name, email FROM customer WHERE customer_id BETWEEN 600 AND 609";
                  var customers = db.Query<Customer> (selectQuery).ToList();
                
      Console.WriteLine("Updated Customers:");
      foreach(var customer in customers) {
      Console.WriteLine($"Customer ID: {customer.Customer_Id}, Name: {customer.First_Name} {customer.Last_Name}, Email: {customer.Email}");
      }
  }
}                            

Build and run the application to insert sample data and view the results.

Delete PostgreSQL data using Dapper

In this example, we'll delete multiple customer records with customer_id values between 590 and 599. If your database includes related records (such as rentals or payments), ensure they are removed first.

static void Main(string[] args) {
  using(IDbConnection db = new PgSqlConnection(DatabaseConfig.connectionString)) {
      // Delete rows with customer_id between 610 and 619
      int deletedCount = DeleteCustomers(db, 610, 619);
            
      // Display the result of the deletion
      Console.WriteLine($"Batch delete successful! {deletedCount} customers deleted.");
            
      // Fetch and display the remaining customers
      string selectQuery = "SELECT customer_id, first_name, last_name, email FROM customer ORDER BY customer_id DESC LIMIT 10";
      var remainingCustomers = db.Query<Customer> (selectQuery).ToList();
            
      Console.WriteLine("\nRemaining Customers After Deletion:");
      foreach(var customer in remainingCustomers) {
      Console.WriteLine($"Customer ID: {customer.Customer_Id}, Name: {customer.First_Name} {customer.Last_Name}, Email: {customer.Email}");
      }
  }
}
            
static int DeleteCustomers(IDbConnection db, int startId, int endId) {
  string deleteQuery = @"
  DELETE FROM customer
  WHERE customer_id BETWEEN :StartId AND :EndId ";
            
  return db.Execute(deleteQuery, new {
    StartId = startId,
    EndId = endId
  });
}                           

The retrieved data is displayed in the console, showing the ID, first name, last name, and email of each remaining customer.

Conclusion

In this tutorial, we explored how integrating Dapper with PostgreSQL using dotConnect for PostgreSQL simplifies data operations in .NET applications. The powerful features of dotConnect, combined with Dapper's lightweight and efficient design, provide a fast and flexible approach to managing PostgreSQL database interactions. Together, these tools enable you to build scalable, maintainable, and high-performance .NET solutions with seamless PostgreSQL connectivity.

Frequently Asked Questions