C# Drivers

• PostgreSQL Npgsql

Npgsql is an open source ADO.NET Data Provider for PostgreSQL; it allows programs written in C#, Visual Basic, and F# to access the YugabyteDB server.

Quick start

Learn how to establish a connection to YugabyteDB database and begin CRUD operations using the steps from Build a C# application.

Download the driver dependency

If you are using Visual Studio IDE, add the Npgsql package to your project as follows:

1. Right-click Dependencies and choose Manage Nuget Packages
2. Search for Npgsql and click Add Package

To add the Npgsql package to your project when not using an IDE, use the following dotnet command:

dotnet add package Npgsql

or any of the other methods mentioned on the nuget page for Npgsql.

Fundamentals

Learn how to perform common tasks required for C# application development using the Npgsql driver.

Connect to YugabyteDB database

After setting up the dependencies, implement the C# client application that uses the Npgsql driver to connect to your YugabyteDB cluster and run a query on the sample data.

Import Npgsql and use the NpgsqlConnection class to create the connection object to perform DDLs and DMLs against the database.

The following table describes the connection parameters required to connect to the YugabyteDB database.

The following is an example connection string for connecting to YugabyteDB.

var connStringBuilder = "host=localhost;port=5433;database=yugabyte;user id=yugabyte;password="
NpgsqlConnection conn = new NpgsqlConnection(connStringBuilder);

Create table

Tables can be created in YugabyteDB by passing the CREATE TABLE DDL statement to the NpgsqlCommand class and getting a command object, then calling the ExecuteNonQuery() method using this command object.

CREATE TABLE employee (id int PRIMARY KEY, name varchar, age int, language varchar)

conn.Open();
NpgsqlCommand empCreateCmd = new NpgsqlCommand("CREATE TABLE employee (id int PRIMARY KEY, name varchar, age int, language varchar);", conn);
empCreateCmd.ExecuteNonQuery();

Read and write data

Insert data

To write data into YugabyteDB, execute the INSERT statement using the NpgsqlCommand class, get a command object, and then call the ExecuteNonQuery() method using this command object.

INSERT INTO employee (id, name, age, language) VALUES (1, 'John', 35, 'CSharp');

NpgsqlCommand empInsertCmd = new NpgsqlCommand("INSERT INTO employee (id, name, age, language) VALUES (1, 'John', 35, 'CSharp');", conn);
int numRows = empInsertCmd.ExecuteNonQuery();

Query data

To query data from YugabyteDB tables, execute the SELECT statement using the NpgsqlCommand class, get a command object, and then call the ExecuteReader() function using the object. Loop through the reader to get the list of returned rows.

SELECT * from employee where id=1;

NpgsqlCommand empPrepCmd = new NpgsqlCommand("SELECT name, age, language FROM employee WHERE id = @EmployeeId", conn);
empPrepCmd.Parameters.Add("@EmployeeId", NpgsqlTypes.NpgsqlDbType.Integer);

empPrepCmd.Parameters["@EmployeeId"].Value = 1;
NpgsqlDataReader reader = empPrepCmd.ExecuteReader();

Console.WriteLine("Query returned:\nName\tAge\tLanguage");
while (reader.Read())
{
    Console.WriteLine("{0}\t{1}\t{2}", reader.GetString(0), reader.GetInt32(1), reader.GetString(2));
}

Configure SSL/TLS

For Npgsql versions before 6.0, the client driver supports the following SSL modes:

The .NET Npgsql driver validates certificates differently from other PostgreSQL drivers. When you specify SSL mode require, the driver verifies the certificate by default (like the verify-ca or verify-full modes), and fails for self-signed certificates. You can override this by specifying "Trust Server Certificate=true", in which case it bypasses walking the certificate chain to validate trust and hence works like other drivers' require mode. In this case, the Root-CA certificate is not required to be configured.

For versions 6.0 or later, the client driver supports the following SSL modes:

The Require SSL mode currently requires explicitly setting the TrustServerCertificate=true field.

The following example shows how to build a connection string for connecting to a YugabyteDB cluster using the Require SSL mode.

var connStringBuilder = new NpgsqlConnectionStringBuilder();
    connStringBuilder.Host = "22420e3a-768b-43da-8dcb-xxxxxx.aws.ybdb.io";
    connStringBuilder.Port = 5433;
    connStringBuilder.SslMode = SslMode.Require;
    connStringBuilder.Username = "admin";
    connStringBuilder.Password = "xxxxxx";
    connStringBuilder.Database = "yugabyte";
    connStringBuilder.TrustServerCertificate = true;
    CRUD(connStringBuilder.ConnectionString);

The following example shows how to build a connection string for connecting to a YugabyteDB cluster using the VerifyCA and VerifyFull SSL modes.

var connStringBuilder = new NpgsqlConnectionStringBuilder();
    connStringBuilder.Host = "22420e3a-768b-43da-8dcb-xxxxxx.aws.ybdb.io";
    connStringBuilder.Port = 5433;
    connStringBuilder.SslMode = SslMode.VerifyCA;
    //or connStringBuilder.SslMode = SslMode.VerifyFull;
    connStringBuilder.RootCertificate = "/root.crt";
    connStringBuilder.Username = "admin";
    connStringBuilder.Password = "xxxxxx";
    connStringBuilder.Database = "yugabyte";
    CRUD(connStringBuilder.ConnectionString);

Compatibility matrix

Other usage examples

Sample C# application with SSL