YugabyteDB Npgsql Smart Driver - Yugabyte Db

<ul class="nav nav-tabs-alt nav-tabs-yb"> <li > <a href="../yb-npgsql-reference/" class="nav-link active"> <i class="icon-postgres" aria-hidden="true"></i> YugabyteDB Npgsql Smart Driver </a> </li> <li > <a href="../postgres-npgsql-reference/" class="nav-link"> <i class="icon-postgres" aria-hidden="true"></i> PostgreSQL Npgsql Driver </a> </li> </ul>

Yugabyte Npgsql smart driver is a .NET driver for YSQL based on PostgreSQL Npgsql driver, with additional connection load balancing features.

For more information on the Yugabyte Npgsql smart driver, see the following:

Download the driver dependency

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

Right-click Dependencies and choose Manage Nuget Packages
Search for NpgsqlYugabyteDB and click Add Package. You may need to click the Include prereleases checkbox.

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

csharp

dotnet add package NpgsqlYugabyteDB

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

Fundamentals

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

Load balancing connection properties

The following connection properties need to be added to enable load balancing:

Load Balance Hosts - enable cluster-aware load balancing by setting this property to true; disabled by default.
Topology Keys - provide comma-separated geo-location values to enable topology-aware load balancing. Geo-locations can be provided as cloud.region.zone. Specify all zones in a region as cloud.region.*. To designate fallback locations for when the primary location is unreachable, specify a priority in the form :n, where n is the order of precedence. For example, cloud1.datacenter1.rack1:1,cloud1.datacenter1.rack2:2.

By default, the driver refreshes the list of nodes every 300 seconds (5 minutes). You can change this value by including the YB Servers Refresh Interval connection parameter.

Use the driver

To use the driver, pass new connection properties for load balancing in the connection URL or properties pool.

To enable uniform load balancing across all servers, you set the Load Balance Hosts property to true in the URL, as per the following example:

csharp

var connStringBuilder = "Host=127.0.0.1,127.0.0.2,127.0.0.3;Port=5433;Database=yugabyte;Username=yugabyte;Password=password;Load Balance Hosts=true;"
NpgsqlConnection conn = new NpgsqlConnection(connStringBuilder)

You can specify multiple hosts in the connection string in case the primary address fails. After the driver establishes the initial connection, it fetches the list of available servers from the universe, and performs load balancing of subsequent connection requests across these servers.

To specify topology keys, you set the Topology Keys property to comma separated values, as per the following example:

var connStringBuilder = "Host=127.0.0.1,127.0.0.2,127.0.0.3;Port=5433;Database=yugabyte;Username=yugabyte;Password=password;Load Balance Hosts=true;Topology Keys=cloud.region.zone"
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.

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", YBNpgsqlTypes.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 YugabyteDB Npgsql smart driver support for SSL is the same as for the upstream driver. For information on using SSL/TLS for your application, refer to the .NET Npgsql driver's Configure SSL/TLS instructions.