How to Connect to PostgreSQL in .NET with C#

Reliable database connectivity is crucial, as applications need a way to store, manage, insert, and retrieve data to function effectively. PostgreSQL stands out as a favored option for application development because of its open-source nature, rich feature set, reliability, and excellent performance. With the backing of a vast developer community and comprehensive documentation, PostgreSQL ensures that help is always within reach.

Developers who work on .NET applications can integrate PostgreSQL as the backend to gain a reliable, scalable, and cost-effective solution that adapts to evolving needs. In this article, we'll delve into some of the most effective techniques for establishing a connection between a .NET application and PostgreSQL.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is a powerful ADO.NET data provider that facilitates 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, ensure you have:

• Visual Studio 2022: An integrated development environment. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• dotConnect for PostgreSQL: A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
• An archive of the DvdRental database: A sample database from the official PostgreSQL documentation.

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

After 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 the Activation Key.

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

Create a .NET project

To start working with dotConnect for PostgreSQL, you first need to configure a .NET project in Visual Studio. In this guide, we're going to create a simple console application that connects to a PostgreSQL database. The whole process involves initializing the project and installing the required NuGet packages. Follow these steps to get started:

1. Open Visual Studio and click Create a new project.

2. Choose Console App (.NET Core) or Console App (.NET Framework), based on your preference, and then click Next.

3. Name the project, specify the path to its folder, and click Create. In this example, we'll use PostgreSqlTest as the project name.

4. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.

5. In the Browse tab, search for and install the Devart.Data.PostgreSql package.

Optionally, you can run the following command in the terminal:

dotnet add package Devart.Data.PostgreSql

Connect using C#

To connect to a PostgreSQL database, replace this piece of code in Program.cs but specify the relevant values for Server, Port, User Id, Password, Database, Schema, and License Key. So, the code must look as follows.

static void Main(string[] args) {
  // Define the connection string
  string connectionString =
    "Server=<server>;" +
    "Port=<port>;" +
    "User Id=<user_id>;" +
    "Password=<password>;" +
    "Database=<database_name>;" +
    "Schema=public;" +
    "License Key=**********;";

  // Create a new connection object
  using(PgSqlConnection connection = new PgSqlConnection(connectionString)) {
    try {
      connection.Open();

      // Optionally set schema if needed
      var cmd = new PgSqlCommand("SET search_path TO public;", connection);
      cmd.ExecuteNonQuery();

      Console.WriteLine("Connection successful!");
    } catch (Exception ex) {
      Console.WriteLine("An error occurred: " + ex.Message);
    }
  }
}

Based on your requirements, you might need to extend the functionality to include executing queries or managing various types of exceptions. Press F5 or click Start on the toolbar.

Connect using the SSL/TLS connection

To establish an SSL connection to the database using the specified connection string parameters, modify your code in Program.cs to match the following format.

 static void Main(string[] args) {
   // Define the connection string with SSL options
   PgSqlConnectionStringBuilder connectionStringBuilder = new PgSqlConnectionStringBuilder {
       Host = "<host>",
       UserId = "<user_id>",
       Password = "<password>",
       Database = "<database_name>",
       SslMode = SslMode.Require,
       SslOptions = new SslOptions {
         CACert = @"C:\root.crt",
           Cert = @"C:\client.crt",
           Key = @"C:\client.key"
       },
       LicenseKey = "**********"
   };

   // Create a new connection object
   using(PgSqlConnection connection = new PgSqlConnection(connectionStringBuilder.ConnectionString)) {
     try {
       // Open the connection
       connection.Open();
       Console.WriteLine("SSL connection opened successfully.");

       // Check the connection state
       if (connection.State == System.Data.ConnectionState.Open) {
         Console.WriteLine("Connection is open.");
       } else {
         Console.WriteLine("Connection is not open.");
       }
     } catch (Exception ex) {
       Console.WriteLine("An error occurred: " + ex.Message);
     }
   }
 }

Press F5 or click Start on the toolbar.

Connect using Server Explorer

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

1. Select Tools > Connect to Database.

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

3. Specify your server credentials and click OK.

Once connected, you can browse tables, execute queries, and manage data directly within Visual Studio.

Connect with EF Core using Entity Developer

If you need to create an EF Core model from the PostgreSQL database, the best approach is to utilize a visual ORM builder — Entity Developer.

1. Right-click the project in the Solution Explorer and select Add > New Item.

2. Name the file and click Add.

3. Go to Installed > C# Items > Data, select Devart EF Core Model, and then click Add.

3. In the Create Model Wizard, select Database First and click Next.

4. Fill in the details of your PostgreSQL database connection, then click Test Connection to verify it.

5. Click OK, then click Next.

6. Select Generate From Database and click Next.

7. Choose the database objects you want to scaffold and click Next.

8. Specify naming rules for the selected entities. It's recommended to keep the default settings this time. Then click Next.

9. On the next page, adjust the model properties and click Next.

10. Next, choose the contents of the model diagram. You can include all entities, split them by schema, or make a custom selection. Click Next.

11. Choose code generation templates for your objects. You can define different parameters you want an object to follow. Let's use the default settings for this tutorial. Click Next.

12. The model is ready now. Finally, click Finish.

The model opens. You can work with the diagram and edit its fields and relations. You can also generate SQL code to alter the database or update it directly from the model.

All steps in this guide are performed in Visual Studio, but if you prefer VS Code, JetBrains Rider, or another similar tool, you can use ED as a standalone application to work with the models efficiently.

To create a simple example of checking a database connection using Entity Framework Core (EF Core) in a C# Console application, where the database connection properties are stored within the DbContext class, follow these steps.

class Program {
  static void Main(string[] args) {
	// Create an instance of the DbContext
	var context = new DvdRentalContext();

	try {
  	// Attempt to open the connection
  	context.Database.OpenConnection();
  	Console.WriteLine("Connection opened successfully.");

  	// Check the connection state
  	if (context.Database.GetDbConnection().State == System.Data.ConnectionState.Open) {
    	Console.WriteLine("Connection is open.");
  	} else {
    	Console.WriteLine("Connection is not open.");
  	}
	} catch (Exception ex) {
  	Console.WriteLine("An error occurred: " + ex.Message);
	} finally {
  	// Close the connection
  	context.Database.CloseConnection();
	}
  }
}

Connect with EF Core using Scaffold-DbContext

1. Firstly, it's required to ensure that EF Core Tools are installed in your development environment. You can install them globally using the .NET CLI. Run this command:

dotnet tool install --global dotnet-ef

2. Install Scaffold-DbContext via the Package Manager Console. Open Visual Studio, which will automatically open the Package Manager Console, and execute the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

3. Once installed, you can use Scaffold-DbContext to generate DbContext and entity classes for the PostgreSQL database. To generate the DbContext class and entity classes in the specified output directory (e.g., Models), run the command in the Package Manager Console:

Scaffold-DbContext "Server=127.0.0.1;Port=5432;UserId=TestUser;Password=TestPassword;Database=dvdrental;Schema=public;LicenseKey=**********" Devart.Data.PostgreSql.EFCore

4. Review the generated code to ensure it matches your database schema.

Video tutorial: How to connect a .NET console application to a PostgreSQL database

Conclusion

This article provides comprehensive guides on connecting .NET applications to the PostgreSQL database using various approaches. We've covered several connection techniques, including secure SSL/TLS connections and implementing Entity Framework Core with Scaffold-DbContext.

With Devart's dotConnect for PostgreSQL, you can significantly enhance your application's efficiency and achieve faster, smoother, and more seamless performance across all tasks. Try out a free trial to experience seamless database connectivity, optimized performance, and robust security for your .NET projects!