Getting Started With dotConnect for PostgreSQL

Below are some resources to help you maximize your experience with dotConnect for PostgreSQL.

How to Connect .NET Console Application to PostgreSQL Database

dotConnect for PostgreSQL is a high-performance ADO.NET data provider with ORM support, offering fast and direct access to PostgreSQL databases for application development.

EF Core, Dapper, NHibernate, LinqConnect, and other ORMs support
SSL, SSH, and proxy servers support
Broad compatibility with various .NET platforms and versions
Full compliance with ADO.NET
Integration with Visual Studio

Code examples

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    string connectionString = "" +
    "Host=127.0.0.1;" +
    "Port=5432;" +
    "User Id=TestUser;" +
    "Password=TestPassword;" +
    "Database=sakila;" +
    "Schema=public;" +
    "LicenseKey=**********";

    using (PgSqlConnection connection = new PgSqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection to PostgreSQL successful!");
      }
      catch (Exception ex)
      {
        Console.WriteLine($"PostgreSQL Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    var builder = new PgSqlConnectionStringBuilder
    {
      Host = "127.0.0.1",
      Port = 5432,
      UserId = "TestUser",
      Password = "TestPassword",
      Database = "sakila",
      Schema = "public", 
      LicenseKey = "**********"
    };

    using (PgSqlConnection connection = new PgSqlConnection(builder.ConnectionString))
    {
      // Set SSL options
      connection.SslOptions.CACert = @"\CACert.crt";
      connection.SslOptions.Cert = @"\Cert.crt";
      connection.SslOptions.Key = @"\Key.key";
      connection.SslOptions.SslMode = SslMode.Require;

      try
      {
        connection.Open();
        Console.WriteLine("SSL connection to PostgreSQL successful!");
      }
      catch (PgSqlException ex)
      {
        Console.WriteLine($"PostgreSQL SSL Error: {ex.Message}");
      }
      catch (Exception ex)
      {
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    var builder = new PgSqlConnectionStringBuilder
    {
      Host = "127.0.0.1",
      Port = 5432,
      UserId = "TestUser",
      Password = "TestPassword",
      Database = "sakila",
      Schema = "public",
      LicenseKey = "**********"
    };

    using (PgSqlConnection connection = new PgSqlConnection(builder.ConnectionString))
    {
      // Configure SSH tunnel
      connection.SshOptions.Host = "sshServer";
      connection.SshOptions.User = "sshUser";
      connection.SshOptions.Password = "sshPassword";
      connection.SshOptions.AuthenticationType = SshAuthenticationType.Password;

      try
      {
        connection.Open();
        Console.WriteLine("SSH connection to PostgreSQL successful!");
      }
      catch (PgSqlException ex)
      {
        Console.WriteLine($"PostgreSQL SSH Error: {ex.Message}");
      }
      catch (Exception ex)
      {
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    var builder = new PgSqlConnectionStringBuilder
    {
      Host = "127.0.0.1",
      Port = 5432,
      UserId = "TestUser",
      Password = "TestPassword",
      Database = "sakila",
      Schema = "public",
      LicenseKey = "**********"
    };

    using (PgSqlConnection connection = new PgSqlConnection(builder.ConnectionString))
    {
      // Configure proxy settings
      connection.ProxyOptions.Host = "10.0.0.1";
      connection.ProxyOptions.Port = 808;
      connection.ProxyOptions.User = "ProxyUser";
      connection.ProxyOptions.Password = "ProxyPassword";

      try
      {
        connection.Open();
        Console.WriteLine("Proxy connection to PostgreSQL successful!");
      }
      catch (PgSqlException ex)
      {
        Console.WriteLine($"PostgreSQL Proxy Error: {ex.Message}");
      }
      catch (Exception ex)
      {
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    string connectionString =
    "Host=127.0.0.1;" +
    "Port=5432;" +
    "UserId=TestUser;" +
    "Password=TestPassword;" +
    "Database=sakila;" +
    "Schema=public;" +
    "LicenseKey=**********";

    using (PgSqlConnection connection = new PgSqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("PostgreSQL connection successful!");

        // Define a query to select data from the actor table
        string query = "SELECT actor_id, first_name, last_name FROM actor";

        // Create a command object
        using (PgSqlCommand command = new PgSqlCommand(query, connection))
        {
          // Execute the query and get a data reader
          using (PgSqlDataReader reader = command.ExecuteReader())
          {
            // Loop through the result set
            while (reader.Read())
            {
              // Access the data using column names
              int actorId = reader.GetInt32(reader.GetOrdinal("actor_id"));
              string firstName = reader.GetString(reader.GetOrdinal("first_name"));
              string lastName = reader.GetString(reader.GetOrdinal("last_name"));

              // Print the data to the console
              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using System;
using Microsoft.EntityFrameworkCore;
using Devart.Data.PostgreSql.EFCore;

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

public class SakilaContext : DbContext
{
  public DbSet<Actor> Actors { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UsePostgreSql(
      "Host=127.0.0.1;Port=5432;UserId=TestUser;Password=TestPassword;Database=sakila;Schema=public;LicenseKey=**********",
      PostgreSqlServerVersion.LatestSupportedServerVersion
    );
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Actor>().ToTable("actor"); // ensure correct table mapping
    modelBuilder.Entity<Actor>().Property(a => a.ActorId).HasColumnName("actor_id");
    modelBuilder.Entity<Actor>().Property(a => a.FirstName).HasColumnName("first_name");
    modelBuilder.Entity<Actor>().Property(a => a.LastName).HasColumnName("last_name");
  }
}

class Program
{
  static void Main()
  {
    using (var context = new SakilaContext())
    {
      try
      {
        // Ensure the database is created (optional for read-only usage)
        context.Database.EnsureCreated();
        Console.WriteLine("Connected to PostgreSQL database successfully!");

        // Query data from the Actors table
        var actors = context.Actors;
        foreach (var actor in actors)
        {
          Console.WriteLine($"Actor ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql.EFCore

using System;
using System.Data;
using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    string connectionString =
    "Host=127.0.0.1;" +
    "Port=5432;" +
    "UserId=TestUser;" +
    "Password=TestPassword;" +
    "Database=sakila;" +
    "Schema=public;" +
    "LicenseKey=**********";

    using (PgSqlConnection connection = new PgSqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("PostgreSQL connection successful!");

        // Define a query to select data from the actor table
        string query = "SELECT actor_id, first_name, last_name FROM actor";

        // Create a command object
        using (PgSqlCommand command = new PgSqlCommand(query, connection))
        {
          // Create a data adapter
          using (PgSqlDataAdapter adapter = new PgSqlDataAdapter(command))
          {
            // Create a DataTable to hold the data
            DataTable dataTable = new DataTable();

            // Fill the DataTable with data from the database
            adapter.Fill(dataTable);

            // Loop through the DataTable and print the data
            foreach (DataRow row in dataTable.Rows)
            {
              int actorId = (int)row["actor_id"];
              string firstName = row["first_name"].ToString();
              string lastName = row["last_name"].ToString();

              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
      string connectionString =
        "Host=127.0.0.1;" +
        "Port=5432;" +
        "UserId=TestUser;" +
        "Password=TestPassword;" +
        "Database=sakila;" +
        "Schema=public;" +
        "LicenseKey=**********";

      using (PgSqlConnection connection = new PgSqlConnection(connectionString))
      {
        try
        {
          connection.Open();
          Console.WriteLine("PostgreSQL connection successful!");

          // Start a transaction
          using (PgSqlTransaction transaction = connection.BeginTransaction())
          {
            try
            {
              // Create a command to insert a new actor
              string insertQuery = "INSERT INTO actor (first_name, last_name) VALUES ('John', 'Doe')";
              using (PgSqlCommand insertCommand = new PgSqlCommand(insertQuery, connection, transaction))
              {
                insertCommand.ExecuteNonQuery();
                Console.WriteLine("Insert command executed.");
              }

              // Create a command to update the inserted actor
              string updateQuery = "UPDATE actor SET last_name = 'Smith' WHERE first_name = 'John' AND last_name = 'Doe'";
              using (PgSqlCommand updateCommand = new PgSqlCommand(updateQuery, connection, transaction))
              {
                updateCommand.ExecuteNonQuery();
                Console.WriteLine("Update command executed.");
              }

              // Commit the transaction
              transaction.Commit();
              Console.WriteLine("Transaction committed successfully.");
            }
            catch (Exception ex)
            {
              // Roll back the transaction if any command fails
              transaction.Rollback();
              Console.WriteLine($"Transaction rolled back due to an error: {ex.Message}");
            }
          }
        }
        catch (Exception ex)
        {
          Console.WriteLine($"Connection error: {ex.Message}");
        }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

using Devart.Data.PostgreSql;

class Program
{
  static void Main()
  {
    string connectionString =
    "Host=127.0.0.1;" +
    "Port=5432;" +
    "UserId=TestUser;" +
    "Password=TestPassword;" +
    "Database=sakila;" +
    "Schema=public;" +
    "LicenseKey=**********";

    using (PgSqlConnection connection = new PgSqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("PostgreSQL connection successful!");

        // Attempt to execute a query that may cause an exception
        string query = "SELECT * FROM non_existent_table";
        using (PgSqlCommand command = new PgSqlCommand(query, connection))
        {
          using (PgSqlDataReader reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              // This will not be reached if the table does not exist
              Console.WriteLine(reader["column_name"]);
            }
          }
        }
      }
      catch (PgSqlException ex)
      {
        // Handle PostgreSQL-specific exception
        Console.WriteLine($"PostgreSQL Error Message: {ex.Message}");
        Console.WriteLine($"SQL State: {ex.SqlState}");
        Console.WriteLine($"Error Code: {ex.ErrorCode}");
        Console.WriteLine($"Stack Trace: {ex.StackTrace}");
      }
      catch (Exception ex)
      {
        // Handle any general .NET exception
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}

PM> Install-Package Devart.Data.PostgreSql

Demo videos

How to Connect .NET Blazor Application to PostgreSQL Database

How to Connect .NET MAUI Application to PostgreSQL Database

How-to articles

.NET PostgreSQL Connection Strings

Learn how to set up connection strings properly when working with dotConnect for PostgreSQL in .NET applications.

Connect to PostgreSQL With EF Core

Find out how to connect a PostgreSQL database to your application using EF Core and ADO.NET.

Connect to PostgreSQL With Dapper

Learn how to connect C# to PostgreSQL using Dapper for fast and efficient data access and management.

Connect to PostgreSQL in .NET

Explore how to establish a connection between .NET applications and PostgreSQL databases using ADO.NET.

Connect to PostgreSQL in Blazor

Learn how to integrate PostgreSQL with .NET Blazor applications using EF Core and ADO.NET.

Connect to PostgreSQL in MAUI

See how to build .NET MAUI applications and perform CRUD operations with PostgreSQL using ADO.NET.

Connect to PostgreSQL with NHibernate

Discover how to connect C# to PostgreSQL using NHibernate seamlessly.

Documentation

Licensing

A detailed technical reference on embedding license information - a required resource for applications developed with dotConnect for PostgreSQL.

DataTable features

A guide to using the PgSqlDataTable component and its features, like server connections, command objects, data storage, and data access.

Parameters

An overview of SQL query parameters and their synchronization, along with practical insights into using parameters in stored procedures to ensure correct performance.

Specific features

An explanation of support for PostgreSQL-specific technologies, including secure connections, load balancing, embedded servers, and more.

Link to full documentation

Getting Started With dotConnect for PostgreSQL

Below are some resources to help you maximize your experience with dotConnect for PostgreSQL.

Code examples

Demo videos

How-to articles

Documentation

Licensing

DataTable features

Parameters

Specific features

dotConnect Universal

FAQ