Build a C# application

YugabyteDB Managed requires SSL

Are you using YugabyteDB Managed? Install the prerequisites, then go to the Use C# with SSL section.

Prerequisites

This tutorial assumes that you have installed the following:

  • YugabyteDB, created a universe, and are able to interact with it using the YSQL shell (ysqlsh). If not, follow the steps in Quick start.
  • Visual Studio.
  • .NET SDK 6.0 or later.

Warning

On every new connection, the Npgsql driver also makes extra system table queries to map types, which adds significant overhead. To turn off this behavior, set the following option in your connection string builder:

connStringBuilder.ServerCompatibilityMode = ServerCompatibilityMode.NoTypeLoading;

Create a sample C# application

To create the sample C# application, do the following:

  1. In Visual Studio, create a new C# application, and choose Console Application as template. Follow the instructions to save the project.

  2. Add the Npgsql package to your project as follows:

    • Right-click on Dependencies and click Manage Nuget Packages.
    • Search for Npgsql and click Add Package.

  3. Copy the following code to your Program.cs file:

using System;
using Npgsql;

namespace Yugabyte_CSharp_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
           var connStringBuilder = "host=localhost;port=5433;database=yugabyte;user id=yugabyte;password="
           NpgsqlConnection conn = new NpgsqlConnection(connStringBuilder)

            try
            {
                conn.Open();

                NpgsqlCommand empCreateCmd = new NpgsqlCommand("CREATE TABLE employee (id int PRIMARY KEY, name varchar, age int, language varchar);", conn);
                empCreateCmd.ExecuteNonQuery();
                Console.WriteLine("Created table Employee");

                NpgsqlCommand empInsertCmd = new NpgsqlCommand("INSERT INTO employee (id, name, age, language) VALUES (1, 'John', 35, 'CSharp');", conn);
                int numRows = empInsertCmd.ExecuteNonQuery();
                Console.WriteLine("Inserted data (1, 'John', 35, '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));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failure: " + ex.Message);
            }
            finally
            {
                if (conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}

Run the C# application

To run the application, choose Run -> Start Without Debugging.

You should see the following output:

Created table Employee
Inserted data (1, 'John', 35, 'CSharp')
Query returned:
Name  Age  Language
John  35   CSharp

Use C# with SSL

Refer to Configure SSL/TLS for information on supported SSL modes and examples for setting up your connection strings.

Create a sample C# application with SSL

To create the sample C# application, do the following:

  1. In Visual Studio, create a new C# application, and choose Console Application as the template. Follow the instructions to save the project.

  2. Add the Npgsql package to your project as follows:

    • Right-click on Dependencies and click Manage Nuget Packages.
    • Search for Npgsql and click Add Package.

  3. Copy the following code to your Program.cs file, and replace the values in the connStringBuilder object as appropriate for your cluster.

using System;
using Npgsql;

namespace Yugabyte_CSharp_Demo
{
   class Program
   {
       static void Main(string[] args)
       {
          var connStringBuilder = new NpgsqlConnectionStringBuilder();
           connStringBuilder.Host = "22420e3a-768b-43da-8dcb-xxxxxx.aws.ybdb.io";
           connStringBuilder.Port = 5433;
           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);
       }
       static void CRUD(string connString)
       {
            NpgsqlConnection conn = new NpgsqlConnection(connString);
           try
           {
               conn.Open();

               NpgsqlCommand empDropCmd = new NpgsqlCommand("DROP TABLE if exists employee;", conn);
               empDropCmd.ExecuteNonQuery();
               Console.WriteLine("Dropped table Employee");

               NpgsqlCommand empCreateCmd = new NpgsqlCommand("CREATE TABLE employee (id int PRIMARY KEY, name varchar, age int, language varchar);", conn);
               empCreateCmd.ExecuteNonQuery();
               Console.WriteLine("Created table Employee");

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

               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));
               }
           }
           catch (Exception ex)
           {
               Console.WriteLine("Failure: " + ex.Message);
           }
           finally
           {
               if (conn.State != System.Data.ConnectionState.Closed)
               {
                   conn.Close();
               }
           }
       }
   }
}

Run the C# SSL application

To run the application, choose Run -> Start Without Debugging.

You should see the following output:

Created table Employee
Inserted data (1, 'John', 35, 'CSharp + SSL')
Query returned:
Name  Age  Language
John  35   CSharp + SSL