C# Drivers
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:
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 totrue
; disabled by default.Topology Keys
- provide comma-separated geo-location values to enable topology-aware load balancing. Geo-locations can be provided ascloud.region.zone
. Specify all zones in a region ascloud.region.*
. To designate fallback locations for when the primary location is unreachable, specify a priority in the form:n
, wheren
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:
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.
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", 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.