npgsql example postgres

gistfile1.txt

using System;
using System.Collections;
using Npgsql;

namespace Program
{
    class Program
    {

        static async Task Main(string[] args) // without async task main, cannot use await


        {
            var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=localhost;Username=postgres;Password=postgres;Database=postgres");           
            await using var dataSource = dataSourceBuilder.Build();
            await using (var command = dataSource.CreateCommand(@"
create table if not exists foo (col1 int, ""COL2"" int);
insert into foo select x,x from generate_series(1, 10) x;
create table if not exists bar as (select * from foo);
"
))
            {
                await command.ExecuteNonQueryAsync();
            }

            await using (var cmd = dataSource.CreateCommand(@"select col1,""COL2"" from bar;"))
            await using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    Console.WriteLine( "" + reader.GetInt64(0) +""+ reader.GetInt64(1) +"")  ;
                }
            }

        }
        /*
        static async Task Main(string[] args)
        {
            var connectionString = "Host=localhost;Username=postgres;Password=postgres;Database=postgres";
            await using var dataSource = NpgsqlDataSource.Create(connectionString);

            // Insert some data
            await using (var cmd = dataSource.CreateCommand("INSERT INTO demo (name) VALUES ($1)"))
            {
                cmd.Parameters.AddWithValue("Hello world");
                await cmd.ExecuteNonQueryAsync();
            }

            // Retrieve all rows
            await using (var cmd = dataSource.CreateCommand("SELECT name FROM demo")) 
            await using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    Console.WriteLine(reader.GetString(0));
                }
            }
        }
        */

        /*
        static async Task Main(string[] args)
        {
            Task<int> result = LongProcess();

            ShortProcess();

            var val = await result; // wait untile get the return value

            Console.WriteLine("Result: {0}", val);

            Console.ReadKey();
        }

        static async Task<int> LongProcess()
        {
            Console.WriteLine("LongProcess Started");

            await Task.Delay(4000); // hold execution for 4 seconds

            Console.WriteLine("LongProcess Completed");

            return 10;
        }

        static void ShortProcess()
        {
            Console.WriteLine("ShortProcess Started");

            //do something here

            Console.WriteLine("ShortProcess Completed");
        }

        */

        /*
        static void Main(string[] args)
        {
            var connectionString = "Host=localhost;Username=postgres;Password=postgres;Database=postgres;Port=5432;Timeout=10;Command Timeout=10";
            using (var connection = new NpgsqlConnection(connectionString))
            {
                connection.Open();

                var databaseTable = "numtable";

                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + databaseTable +
                        " (id SERIAL PRIMARY KEY, number INTEGER NOT NULL UNIQUE)";
                    cmd.ExecuteNonQuery();
                }

                var arrayNumbers = new ArrayList { 17, 27, 37, 43, 10, 88, 16, 23, 88 };
                foreach (var number in arrayNumbers)
                {
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = connection;
                        cmd.CommandText = "INSERT INTO " + databaseTable +
                                          " (number) VALUES (" + number + ") ON CONFLICT (number) DO NOTHING";
                        cmd.ExecuteNonQuery();
                    }
                }

                using (var cmd = new NpgsqlCommand("SELECT * FROM " + databaseTable, connection))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        Console.WriteLine("|---------------|---------------|");
                        Console.WriteLine("|       id      |     number    |");
                        Console.WriteLine("|---------------|---------------|");
                        while (reader.Read())
                        {
                            Console.Write("|\t{0}\t|\t{1}\t|\n", reader[0], reader[1]);
                            Console.WriteLine("|---------------|---------------|");
                        }
                    }
                }

                using (var cmd = new NpgsqlCommand("SELECT 1,2 from pg_sleep(20)", connection))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        Console.WriteLine("|---------------|---------------|");
                        Console.WriteLine("|       id      |     number    |");
                        Console.WriteLine("|---------------|---------------|");
                        while (reader.Read())
                        {
                            Console.Write("|\t{0}\t|\t{1}\t|\n", reader[0], reader[1]);
                            Console.WriteLine("|---------------|---------------|");
                        }
                    }
                }
                connection.Close();
            }
        
        }
        */
    }
}

using System;
using System.Collections;
using Npgsql;

using System.Threading;
using NpgsqlTypes;
using System.Net;
using System.Data;
using System.Xml;
using Newtonsoft.Json;
using System.Security.Cryptography;
using System.Reflection.Metadata;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;


namespace Program
{

    class MyJson
    {
        public int A { get; set; }
        public int B { get; set; }
    }

    class MyRecord
    {
        public int A { get; set; }
        public MyJson B { get; set; }
        public List<int> C { get; set; }
        public List<String> D { get; set; }
        public DateTime E { get; set; }
        public DateTime F { get; set; }

    }

    public class Program
    {

        public static async Task Main(string[] args)
        {

            var myPoco1 = new MyJson { A = 8, B = 9 };

            var connectionString = "Host=localhost;Username=postgres;Password=postgres;Database=postgres";
            var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
            dataSourceBuilder.EnableDynamicJson();
            //await using var dataSource = NpgsqlDataSource.Create(connectionString);

            await using var dataSource = dataSourceBuilder.Build();

            await using (var cmd = dataSource.CreateCommand("DROP TABLE IF EXISTS teachers"))
            {
                
                await cmd.ExecuteNonQueryAsync();
            }

            var sql = """
create table teachers (
col1 integer,
col2 jsonb,
col3 int[],
col4 text[],
col5 timestamptz,
col6 date);
""";

            await using (var cmd = dataSource.CreateCommand(sql))
            {               
                await cmd.ExecuteNonQueryAsync();
            }

            sql = "INSERT INTO teachers(col1, col2, col3, col4, col5, col6) VALUES($1,$2,$3,$4,$5,$6)";

            var json = """
                { "ok": 123 }
                """;

            await using (var cmd = dataSource.CreateCommand(sql))
            {
                cmd.Parameters.AddWithValue(1);
                cmd.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Jsonb, myPoco1 ?? (object)DBNull.Value);
                cmd.Parameters.AddWithValue(new int[2] { 1, 2 });
                cmd.Parameters.AddWithValue(new string[2] { "ok", "bye" });
                cmd.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Now);
                cmd.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Date, DateTime.Now);
                await cmd.ExecuteNonQueryAsync();
            }

            MyRecord r = new MyRecord();
            sql = "select col1,col2,col3,col4,col5,col6 from teachers;";
            await using (var cmd = dataSource.CreateCommand(sql))
            {
                await using var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    r.A = reader.GetFieldValue<Int32>(0);
                    r.B = reader.GetFieldValue<MyJson>(1);
                    r.C = reader.GetFieldValue<List<int>>(2);
                    r.D = reader.GetFieldValue<List<String>>(3);
                    r.E = reader.GetFieldValue<DateTime>(4);
                    r.F = reader.GetFieldValue<DateTime>(5);
                    Console.WriteLine(string.Join(" ", r.C.Where(c => c > 0).ToList()));
                }
            }
             

        }

    }
}