---
title: "C# Drivers"
url: "https://docs.yugabyte.com/stable/develop/drivers-orms/csharp/postgres-npgsql-reference/"
---

# C# Drivers

C# PostgreSQL Npgsql Driver for YSQL

- [YugabyteDB Npgsql Smart Driver](/stable/develop/drivers-orms/csharp/yb-npgsql-reference/)
- [PostgreSQL Npgsql Driver](/stable/develop/drivers-orms/csharp/postgres-npgsql-reference/)

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. Npgsql is based on [libpq](#libpq "libpq") and supports the [SCRAM-SHA-256 authentication method](/stable/secure/authentication/password-authentication/#scram-sha-256 "SCRAM-SHA-256 authentication method").

For details on Npgsql, refer to the [Npgsql documentation](https://www.npgsql.org/doc/ "Npgsql documentation").

For building a sample C# application with Npgsql, see [Connect an application](/stable/develop/drivers-orms/csharp/postgres-npgsql/ "Connect an 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:

```csharp
dotnet add package Npgsql
```

or any of the other methods mentioned on the [nuget page](https://www.nuget.org/packages/Npgsql/ "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 for connecting to the YugabyteDB database.

| Parameters | Description                          | Default   |
|------------|--------------------------------------|-----------|
| Host       | Host name of the YugabyteDB instance | localhost |
| Port       | Listen port for YSQL                 | 5433      |
| Database   | Database name                        | yugabyte  |
| Username   | User connecting to the database      | yugabyte  |
| Password   | Password for the user                | yugabyte  |

The following is a basic example connection string for connecting to YugabyteDB.

```csharp
var connStringBuilder = "Host=localhost;Port=5433;Database=yugabyte;Username=yugabyte;Password=password"
NpgsqlConnection conn = new NpgsqlConnection(connStringBuilder);
```

Warning

On every new connection, the Npgsql driver also makes [extra system table queries to map types](https://github.com/npgsql/npgsql/issues/1486 "extra system table queries to map types"), which adds significant overhead. It is recommended that you turn this behavior off to significantly reduce connection open execution time.

Set the following option in your connection string builder:

```csharp
connStringBuilder.ServerCompatibilityMode = ServerCompatibilityMode.NoTypeLoading;
```

Alternatively, you can add the following to your connection string:

```csharp
Server Compatibility Mode=NoTypeLoading;
```

### 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.

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

```csharp
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.

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

```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.

```sql
SELECT * from employee where id=1;
```

```csharp
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

The following table describes the additional parameters the .NET Npgsql driver requires as part of the connection string when using SSL.

| Npgsql Parameter       | Description                                   |
|------------------------|-----------------------------------------------|
| SslMode                | SSL Mode                                      |
| RootCertificate        | Path to the root certificate on your computer |
| TrustServerCertificate | For use with the Require SSL mode             |

Npgsql supports SSL modes in different ways depending on the driver version, as shown in the following table.

SSL mode Versions before 6.0 Version 6.0 or later 

Disable Supported (default) Supported 

Allow Not Supported Supported 

Prefer Supported Supported (default) 

Require Supported  
For self-signed certificates, set `TrustServerCertificate` to true Supported  
Set `TrustServerCertificate` to true 

VerifyCA Not Supported - use Require Supported 

VerifyFull Not Supported - use Require Supported

The .NET Npgsql driver validates certificates differently from other PostgreSQL drivers as follows:

- Prior to version 6.0, when you specify SSL mode `Require`, you also need to specify `RootCertificate`, and the driver verifies the certificate by default (like the verify CA or verify full modes on other drivers), and fails for self-signed certificates.
  
  To use self-signed certificates, specify `TrustServerCertificate=true`, which bypasses walking the certificate chain to validate trust and hence works like other drivers' require mode. In this case, you don't need to specify the `RootCertificate`.
- For version 6.0 and later, the `Require` SSL mode requires explicitly setting the `TrustServerCertificate` field to true.

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

```csharp
var connStringBuilder = new NpgsqlConnectionStringBuilder();
    connStringBuilder.Host = "22420e3a-768b-43da-8dcb-xxxxxx.aws.yugabyte.cloud";
    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.

```csharp
var connStringBuilder = new NpgsqlConnectionStringBuilder();
    connStringBuilder.Host = "22420e3a-768b-43da-8dcb-xxxxxx.aws.yugabyte.cloud";
    connStringBuilder.Port = 5433;
    connStringBuilder.SslMode = SslMode.VerifyCA;
    //or connStringBuilder.SslMode = SslMode.VerifyFull;
    connStringBuilder.RootCertificate = "/root.crt"; //Provide full path to your root CA.
    connStringBuilder.Username = "admin";
    connStringBuilder.Password = "xxxxxx";
    connStringBuilder.Database = "yugabyte";
    CRUD(connStringBuilder.ConnectionString);
```

For more information on TLS/SSL support, see [Security and Encryption](https://www.npgsql.org/doc/security.html?tabs=tabid-1 "Security and Encryption") in the Npgsql documentation.

## Compatibility matrix

| Driver Version | YugabyteDB Version | Support |
|----------------|--------------------|---------|
| 6.0.3          | 2.11 (preview)     | full    |
| 6.0.3          | 2.8 (stable)       | full    |
| 6.0.3          | 2.6                | full    |