Cloud

C# (Dapper - Npgsql)

Dapper is an object-relational mapper (ORM) for .NET that runs SQL directly and maps results to C# objects with minimal setup. This page describes how to use Dapper with Npgsql, a PostgreSQL data provider for .NET, to connect to Redpanda SQL.

Establishing connection

There are two ways to establish a connection through Npgsql:

  • Npgsql’s DataSource class

    var connectionString = "Server=127.0.0.1:5432;Username=user;Password=password;Database=db;";
var dataSource = NpgsqlDataSource.Create(connectionString);
var connection = dataSource.OpenConnection();

  • Creating connection directly

    var connectionString = "Server=127.0.0.1:5432;Username=user;Password=password;Database=db;";
var connection = new NpgsqlConnection(connectionString);
connection.Open();

For more details on connection string options, including SSL configuration, see the Npgsql docs.

Example usage

This example shows basic query execution for the following C# class, once the connection has been established:

public class Customer
{
    public int ClientId { get; set; }
    public double Height { get; set; }
    public string FirstName { get; set; }
}
connection.Execute("CREATE TABLE Customer (ClientId INTEGER, Height DOUBLE, FirstName TEXT)");

var customer = new Customer{ClientId = 1, Height = 3.14, FirstName = "John"};
connection.Execute("INSERT INTO Customer VALUES (@ClientId, @Height, @FirstName)", customer);

var customers = connection.Query<Customer>("SELECT * FROM Customer");
foreach(var c in customers)
{
    Console.WriteLine($"Customer #{c.ClientId}: {c.FirstName} is {c.Height} tall.");
}

The INSERT INTO Customer VALUES (@ClientId, @Height, @FirstName) syntax uses prepared statements internally. Redpanda SQL does not support prepared statements. It translates incoming binary input back into a string, so prepared statements provide no security or performance benefits.

Unsupported functions and structures

The following functions and potentially related structures are either not supported or work incorrectly when combining Redpanda SQL and Dapper-Npgsql: