Quick Setup for a .NET Lab Environment (Linux)

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

This guide provides the minimal steps required to set up a .NET 10 environment on a Linux machine (RHEL/CentOS/Rocky) and run a high-performance data loading test against a Greenplum Database using the native Npgsql driver.

Resolution

1. Install the .NET SDK

Open your Linux terminal and run the following commands to install the SDK and verify the installation:

# Install the .NET 10 SDK via the package manager
sudo dnf install dotnet-sdk-10.0 -y

# Verify the installation was successful
dotnet --info

2. Initialize the Lab Project

Create a new console application and install the PostgreSQL/Greenplum connection driver:

# Create a new project folder and application
dotnet new console -n GPDBLab

# Navigate into the new folder
cd GPDBLab

# Install the Npgsql driver
dotnet add package Npgsql

3. Add the Test Code

Replace the contents of Program.cs with the following minimal script. This script verifies connectivity and tests Greenplum's high-speed binary COPY feature.

using System;
using Npgsql;
using NpgsqlTypes; // Required for NpgsqlDbType and Binary COPY

namespace GPDBTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // 1. Define the connection string
            // Please replace YourPasswordHere with the actual password for gpadmin
            string connString = "Host=localhost;Port=5436;Username=gpadmin;Password=YourPasswordHere;Database=gpadmin;CommandTimeout=180;";

            Console.WriteLine("=== Greenplum Table Creation and Fast Bulk Insert Test Started ===");

            using (var conn = new NpgsqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    Console.WriteLine(">>> 1. Database connected successfully!");

                    // ---------------------------------------------------------
                    // Step 1: Create a test table (drop it first if it already exists)
                    // ---------------------------------------------------------
                    string createTableSql = @"
                        DROP TABLE IF EXISTS test_metrics_data;
                        CREATE TABLE test_metrics_data (
                            id INT,
                            metric_value FLOAT8
                        ) DISTRIBUTED BY (id);"; // Greenplum specific distribution key

                    using (var cmd = new NpgsqlCommand(createTableSql, conn))
                    {
                        cmd.ExecuteNonQuery();
                        Console.WriteLine(">>> 2. Table 'test_metrics_data' created successfully!");
                    }

                    // ---------------------------------------------------------
                    // Step 2: Use Npgsql COPY (Binary Import) to insert 3000 rows rapidly
                    // ---------------------------------------------------------
                    Console.WriteLine(">>> 3. Inserting 3000 records...");

                    // Generate random numbers for our test data
                    Random rnd = new Random();

                    // BeginBinaryImport opens a high-speed binary stream directly to Greenplum
                    using (var writer = conn.BeginBinaryImport("COPY test_metrics_data (id, metric_value) FROM STDIN (FORMAT BINARY)"))
                    {
                        for (int i = 1; i <= 3000; i++)
                        {
                            writer.StartRow();
                            // Write the ID
                            writer.Write(i, NpgsqlDbType.Integer);
                            // Write a random metric value between 0.0 and 100.0
                            writer.Write(rnd.NextDouble() * 100.0, NpgsqlDbType.Double);
                        }

                        // You MUST call Complete(), otherwise the data will be discarded
                        writer.Complete();
                    }

                    Console.WriteLine(">>> [Success] 3000 records have been bulk inserted successfully!");

                    // ---------------------------------------------------------
                    // Step 3: Calculate and return the average value
                    // ---------------------------------------------------------
                    string avgSql = "SELECT AVG(metric_value) FROM test_metrics_data;";
                    using (var cmd = new NpgsqlCommand(avgSql, conn))
                    {
                        // ExecuteScalar returns a single value.
                        // Using 'object?' fixes the CS8600 warning about possible null references.
                        object? result = cmd.ExecuteScalar();

                        // Check if the result is not null and not DBNull
                        if (result != null && result != DBNull.Value)
                        {
                            double average = Convert.ToDouble(result);
                            Console.WriteLine("-----------------------------------------");
                            Console.WriteLine("[Data Statistics Result]");
                            Console.WriteLine("Total Records: 3000");
                            Console.WriteLine($"Average metric_value: {average:F4}"); // Format to 4 decimal places
                            Console.WriteLine("-----------------------------------------");
                        }
                    }
                }
                catch (PostgresException ex)
                {
                    // Catch database-level errors from Greenplum
                    Console.WriteLine($"\n[Database Error SQLState {ex.SqlState}]: {ex.MessageText}");
                }
                catch (Exception ex)
                {
                    // Catch network or .NET level errors
                    Console.WriteLine($"\n[System Error]: {ex.Message}");
                }
            }

            Console.WriteLine("=== Test Completed ===");
        }
    }
}

4. Run the Lab

Execute your application directly from the terminal:

dotnet run

5. The result is as below:

=== Greenplum Table Creation and Fast Bulk Insert Test Started ===
>>> 1. Database connected successfully!
>>> 2. Table 'test_metrics_data' created successfully!
>>> 3. Inserting 3000 records...
>>> [Success] 3000 records have been bulk inserted successfully!
-----------------------------------------
[Data Statistics Result]
Total Records: 3000
Average metric_value: 49.8604
-----------------------------------------
=== Test Completed ===