How to connect PostgreSQL databases in .NET applications

Database connectivity is essential since no application can function properly without a way to store and manage data, allowing for the insertion and retrieval of information when needed. 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.

For developers working on .NET applications, integrating PostgreSQL as the backend offers 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 your .NET application and PostgreSQL.

Requirements

1. Visual Studio 2022: Our IDE of choice will be Visual Studio. If you don't have it on your machine, we will be using the community version, so you can download and install it.

2. dotConnect for PostgreSQL: A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.

3. PostgreSQL server: The database server we will interact with.

4. DvdRental sample database: The sample database from the official PostgreSQL documentation.

Install and configure dotConnect for PostgreSQL

The first thing we'll do is set up a new .NET Application project. Open Visual Studio you installed and click Create New Project. In the Search Template field, type ASP.NET Core Web Application and click the corresponding search result. Give your project a name. For example, ours is going to be called PostgreSQL_ASP.NET. Then select .NET 8 as the target framework and
click Create.

Next, we are going to install dotConnect for PostgreSQL to our project via the NuGet Package Manager in Visual Studio. On the taskbar, click Tools, then navigate to NuGet Package Manager > Manage NuGet Packages for Solution.

The NuGet Package Manager page opens. Click Browse, search for Devart.Data.PostgreSQL, and select it once found. Then, select your project and click Install.

After installing the NuGet package, proceed to activate the trial version or enter your license:

1. Go to dotConnect for PostgreSQL download page and download the dcpostgresql.exe installer.

2. Execute the downloaded file and follow the instructions to install dotConnect for PostgreSQL on your machine.

This process not only installs dotConnect for PostgreSQL but also ensures that all features are activated and available in your project. Also, note that registered users can download the full version from their accounts, not the trial version.

When you run the application by clicking F5 the scaffolded web page appears. To configure your PostgreSQL database, follow the steps to create a test database named dvdrental from Load PostgreSQL Sample Database. After successfully configuring the database, you can move on to the next step. Now, let us set up our Database connection class. Right-click your project, go to Add, and click Class.

After that, right-click and add again, then add the Actor.cs class. Copy this piece of code inside that class:

  public class Actor
  {
      public int ActorId { get; set; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public DateTime LastUpdate { get; set; }
  }

Connect to PostgreSQL

Great now, let's add a method to our DatabaseConnectionClass, copy this piece of code into your class:

public static DataTable GetActorTable()
 {
	try
	{
         // Open a connection to the PostgreSQL database
         string connectionString = "Server=localhost;Port=5432;UserId=postgres;Password=password;Database=dvdrental;Schema=public";
         DataTable table;
         using (var connection = new Devart.Data.PostgreSql.PgSqlConnection(connectionString))
         {
             connection.Open();
             string query = "SELECT * FROM actor LIMIT 10;";
             PgSqlDataAdapter adapter = new Devart.Data.PostgreSql.PgSqlDataAdapter(query, connection);
             DataSet dataSet = new DataSet();
             adapter.Fill(dataSet, "actor");
             table = dataSet.Tables["actor"];
             connection.Close();
         }
         return table;

       }
	catch (Exception ex)
	{
         Console.WriteLine(ex.Message);
         return null;
       }
 }

Don't forget to use Devart.Data.PostgreSql at the top of your file and your actual user and password.

Connect using the SSL/TLS connection

You can also connect to a PostgreSQL database with the help of the SSL/TLS connection. This code will show you how:

public static DataTable GetActorTableUsingSSL()
{
    try
    {
        // Open a SSL connection to the PostgreSQL database
        DataTable table = new DataTable();
        Devart.Data.PostgreSql.PgSqlConnectionStringBuilder pgCSB = new Devart.Data.PostgreSql.PgSqlConnectionStringBuilder();
        pgCSB.Server = "localhost";
        pgCSB.Port = 5432;
        pgCSB.UserId = "postgres";
        pgCSB.Password = "password";
        pgCSB.Schema = "public";
        pgCSB.MaxPoolSize = 150;
        pgCSB.Database = "dvdrental";
        pgCSB.ConnectionTimeout = 30;
        pgCSB.SslMode = SslMode.Require;
        pgCSB.SslCACert = "/root.crt";
        pgCSB.SslKey = "/server.key";
        pgCSB.SslCert = "/server.crt";
        PgSqlConnection myConnection = new PgSqlConnection(pgCSB.ConnectionString);

        PgSqlCommand command = new Devart.Data.PostgreSql.PgSqlCommand("SELECT * FROM actor LIMIT 10;", myConnection);
        myConnection.Open();
        using (PgSqlDataReader reader = command.ExecuteReader())
        {
            table.Load(reader);
        }
        myConnection.Close();
        return table;
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        return null;
    }
}

Here, we have defined the following parameters:

• SslOptions.CACert - the location of your authority certificate.
• SslOptions.Cert - the location of your client certificate.
• SslOptions.Key - the location of your client private key.

This way, the communication between the PostgreSQL client and server becomes secure as you've encrypted it with SSL.

Now, we need to be able to visualize the data we have just returned. In Solution Explorer, click Controllers, select Add New Class, and add the ActorsController class. Inside the PostgreSQL_ASP.NET_ASP.NET.Controllers namespace, modify the class using this code:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
    [HttpGet]
    public IEnumerable GetActors()
    {
        List actorList = new();
        DataTable dataTable = DatabaseConnectionClass.GetActorTable();

        foreach (DataRow row in dataTable.Rows)
        {
            Actor obj = new Actor
            {
                ActorId = Convert.ToInt32(row["actor_id"]),
                FirstName = Convert.ToString(row["first_name"]),
                LastName = Convert.ToString(row["last_name"]),
                LastUpdate = Convert.ToDateTime(row["last_update"])
            };
            actorList.Add(obj);
        }

        return actorList;
    }
}

Check out our comprehensive guide on PostgreSQL Connection Strings for a variety of connection scenarios.

When you run your project, click the dropdown for GetActors, select Try it now, and then click Execute. After executing, we can see the list we want:

Great! We have successfully established the connection to our PostgreSQL database.

Connect with EF Core through Entity Developer

Another way to connect to a PostgreSQL database is through Entity Developer. To do it, download a free trial. It is provided for 30 days and offers all the functionality for proper testing under the actual workload. Then download, install, and open Entity Developer. And navigate to File > New Model.

In the model pane, click Entity Model under Devart Entity Framework. In the Entity Developer Model wizard, select Database first and click Next.

The Setup data connection properties window pops up. Fill in the details of your PostgreSQL database connection as follows and ener your own ID and password, which were configured while setting up PostgreSQL.

After clicking Next, select Generate From Database and click Next.

Next, choose the database objects you want to include to the model. You can select all, but since we're using the actor table, we'll select it.

Having clicked Next, you will be asked to set up naming rules. This will define the naming convention you want the property names in the database object to follow. We'll leave it by default and click Next.

Configure the model properties, select the required framework from Target Framework and click Next

After that, choose the model diagram contents. For this tutorial, we'll use All Entities, so select this option and click Next.

On the Code Generation Templates page, choose templates for the model. Here, you can define different parameters you want the object to follow. We'll use the default settings for this tutorial.

Click Next and your model will be fully created.

Connect with EF Core using Scaffold-DbContext

It's also possible to connect our .NET application to a PostgreSQL database via EF (Entity Framework) Core. Let's see how to
implement it.

First, we need to add a separate project to our solution to illustrate the case. Right-click the current PostgreSQL_ASP.NET solution in the Visual Studio Solution Explorer and go to Add > New project.

This additional project, in our case, is called PostgreSQL_EF_Core_ASP.NET, and we added it in the same way as we did at the beginning of this tutorial. Right-click it and set it as a startup project.

First, ensure you have EF Core Tools installed in your development environment. You can install them globally using the .NET CLI from the command line:

dotnet tool install --global dotnet-ef

Next, open NuGet Package Manager for the project in Visual Studio, select Devart.Data.PostgreSQL.EFCore, and click Install.

To install Scaffold-DbContext via the Package Manager Console, open Visual Studio, navigate to Tools > Package Manager Console, and select PostgreSQL_EF_Core_ASP.NET as the project. When the console opens, execute the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

Once installed, we can use Scaffold-DbContext to generate DbContext and entity classes for our PostgreSQL database. Execute the following command from the Package Manager Console:

Scaffold-DbContext "Server=localhost;Port=5432;UserId=postgres;Password=password;Database=dvdrental;Schema=public" Devart.Data.PostgreSql.Entity.EFCore

Remember to provide your relevant credentials for your User ID and password. After that, dbContext will be renamed to DvdrentalContext, and the table entity classes should be generated. You can see them in your Solution Explorer.

Let's use the Actors class again. We'll query this entity table using LINQ. Right-click the PostgreSQL_EF_Core_ASP.NET project, select the option to add a class, and add DatabaseConnectionClass as we did earlier. Then copy this code into the class:

public class DatabaseConnectionClass
 {
     private readonly DvdrentalContext _context;

     public DatabaseConnectionClass()
     {
         _context = new DvdrentalContext();
     }

     public List GetActors()
     {
         return _context.Actors.Take(10).ToList();
     }
 }

We have used Dvdrentalcontext to get the entity of actors and returned it to a list. Just like before, we need a way to visualize our data, so add a new class called ActorsController into the Controllers folder. Modify the class with the code below inside the PostgreSQL_EF_Core_ASP.NET.Controllers namespace:

public class DatabaseConnectionClass
[ApiController]
    [Route("api/[controller]/[action]")]
    public class ActorsController : ControllerBase
    {
        private readonly DatabaseConnectionClass _context;

        public ActorsController()
        {
            _context = new DatabaseConnectionClass();
        }

        [HttpGet]
        public IEnumerable GetActors()
        {
            return _context.GetActors();
        }
    }

Run your application by clicking F5, go to the GetActors endpoint, click Try it out and Execute. You will see your query results in the response body as was shown earlier.

For more information on working with EF Core, refer to How to Use Entity Framework (EF) Core with PostgreSQL, which includes instructions on performing the INSERT, UPDATE, and DELETE operations.

More advantages of dotConnect for PostgreSQL

Simplified Development: Work directly with PostgreSQL from your application without the need for separate libraries.

Effortless ORM Integration: Easily build ORMs using the Entity Developer tool, boost productivity and code maintainability.

Enhanced Web Features: Leverage ASP.NET providers for features like user roles, session management, and membership.

Optimized Performance: Gain maximum speed through PostgreSQL-specific optimizations to ensure your application
runs smoothly.

Detailed Insights: Track database activity for each component with the free dbMonitor tool, identify and fix issues.

Robust Security: Protect your data with various encryption options, secure connections (SSL, SSH), and other means.

Conclusion

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

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