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.

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.

To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.

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.

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.

Entity Developer allows you to visually design and generate EF Core models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.

Follow the detailed illustrated guide to create your database model using Entity Developer. When this process is complete, the model you created 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!