using System;
using System.Data;
using Pervasive.Data.SqlClient;

namespace BasicCRUD
{
    class Program
    {
        const string CONNECTIONSTRING = "host=localhost;database name=demodata;port=1583";
        const string TABLENAME = "DeptADOTest";

        static string[] DEFAULTINSERTS = new string[]
        {
            "insert into " + TABLENAME + " values(10, 'ACCOUNTING', 'NEW YORK')",
            "insert into " + TABLENAME + " values(20, 'RESEARCH', 'DALLAS')",
            "insert into " + TABLENAME + " values(30, 'SALES', 'CHICAGO')",
            "insert into " + TABLENAME + " values(40, 'OPERATIONS', 'BOSTON')"
        };

        static void Main(string[] args)
        {
            PsqlConnection connection = new PsqlConnection(CONNECTIONSTRING);
            PsqlCommand command = new PsqlCommand();
            command.Connection = connection;         

            try
            {
                connection.Open();
                Console.WriteLine("Connection opened");

                // Create operation
                Console.WriteLine();
                Console.WriteLine("----------[C] R U D started----------");
                Console.WriteLine();
                Console.WriteLine("Drop table if it already exists...");
                command.CommandText = @"
                DROP TABLE IF EXISTS " + TABLENAME;
                command.ExecuteNonQuery();
                Console.WriteLine();
                Console.WriteLine("Create table " +TABLENAME);
                command.CommandText = @"
                CREATE TABLE " + TABLENAME + @"(
                    SNo         int, 
                    Name        varchar(14),
                    Location    varchar(13), 
                    constraint  pk_psql_" + TABLENAME + @" primary key(SNo))";
                command.ExecuteNonQuery();
                Console.WriteLine("Table created");
                Console.WriteLine();
                Console.WriteLine("----------[C] R U D finished----------");
                Console.WriteLine();

                // Insert data
                Console.WriteLine();
                Console.WriteLine("----------C [R] U D started----------");
                Console.WriteLine();
                for (int counter = 0; counter < DEFAULTINSERTS.Length; counter++)
                {
                    command.CommandText = DEFAULTINSERTS[counter];
                    command.ExecuteNonQuery();
                    Console.WriteLine($"{counter+1} row(s) inserted to the table");
                }

                // Retrieve data
                RetrieveData(command);
                Console.WriteLine();
                Console.WriteLine("----------C [R] U D finished----------");
                Console.WriteLine();

                // Update data
                Console.WriteLine();
                Console.WriteLine("----------C R [U] D started----------");
                Console.WriteLine();
                command.CommandText = "UPDATE " + TABLENAME + " SET Name = 'NEW SALES' WHERE SNo = 20";
                command.ExecuteNonQuery();
                Console.WriteLine("1 row updated");
                RetrieveData(command);
                Console.WriteLine();
                Console.WriteLine("----------C R [U] D finished----------");
                Console.WriteLine();

                // Delete data
                Console.WriteLine();
                Console.WriteLine("----------C R U [D] started----------");
                Console.WriteLine();
                command.CommandText = "DELETE FROM " + TABLENAME + " WHERE SNo = 30";
                command.ExecuteNonQuery();
                Console.WriteLine("1 row deleted");
                RetrieveData(command);
                Console.WriteLine();
                Console.WriteLine("----------C R U [D] finished----------");
                Console.WriteLine();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception occurred - {ex.Message}");
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    command.CommandText = "DROP TABLE " + TABLENAME;
                    command.ExecuteNonQuery();
                    Console.WriteLine("Table dropped");
                    command = null;
                    connection.Close();
                }      
            }
        }

        // Retrieves data from database
        static void RetrieveData(PsqlCommand command)
        {
            command.CommandText = "SELECT * FROM " + TABLENAME;
            PsqlDataReader reader = command.ExecuteReader();

            Console.WriteLine($"Retrieved data from table {TABLENAME}:-");
            while (reader.Read())
            {
                Console.WriteLine($"{reader.GetInt32(0)} - {reader.GetString(1)} - {reader.GetString(2)}");
            }
            reader.Close();
        }
    }
}
