using Pervasive.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;

namespace BulkCopy
{
    class Program
    {
        #region Connection Details

        const string SOURCE_CONNECTION_STRING = "host=localhost;port=1583;database=DEMODATA";
        const string DESTINATION_CONNECTION_STRING = "host=localhost;port=1583;database=DEMODATA";

        #endregion Connection Details

        #region Table Details

        private const string SOURCE_TABLE_NAME = "DEPT";

        private const string DESTINATION_TABLE_NAME = "NEWDEPT";

        private const string SOURCE_TABLE_CREATE = "CREATE TABLE " + SOURCE_TABLE_NAME + "(deptno int,dname varchar(14),loc varchar(13),constraint pk_dept primary key(deptno))";

        private const string DESTINATION_TABLE_CREATE = "CREATE TABLE " + DESTINATION_TABLE_NAME + "(newdeptno int,newdname varchar(14),newloc varchar(13),constraint pk_newdept primary key(newdeptno))";

        internal static List<string> SOURCE_TABLE_INSERTS = new List<string>()
        {
            "insert into " + SOURCE_TABLE_NAME + " values(10, 'ACCOUNTING', 'NEW YORK')",
            "insert into " + SOURCE_TABLE_NAME + " values(20, 'RESEARCH', 'DALLAS')",
            "insert into " + SOURCE_TABLE_NAME + " values(30, 'SALES', 'CHICAGO')",
            "insert into " + SOURCE_TABLE_NAME + " values(40, 'OPERATIONS', 'BOSTON')"
        };

        private const string DROP_TABLE = "DROP TABLE ";

        private const string SELECT_TOP3_FROM_SOURCE = "SELECT TOP 3 * FROM " + SOURCE_TABLE_NAME;

        private const string SELECT_ALL = "SELECT * FROM ";

        #endregion Table Details

        static void Main(string[] args)
        {
            PsqlConnection sourceConnection = new PsqlConnection(SOURCE_CONNECTION_STRING);
            PsqlConnection destinationConnection = new PsqlConnection(DESTINATION_CONNECTION_STRING);
            PsqlDataReader sourceReader = null;

            try
            {
                sourceConnection.Open();
                Console.WriteLine("Source connection opened" + Environment.NewLine);

                destinationConnection.Open();
                Console.WriteLine("Destination connection opened" + Environment.NewLine);

                ExecuteNonQueryOnCommand(sourceConnection, SOURCE_TABLE_CREATE);
                Console.WriteLine("Table is created on the source server" + Environment.NewLine);

                ExecuteNonQueryOnCommand(destinationConnection, DESTINATION_TABLE_CREATE);
                Console.WriteLine("Table is created on the destination server" + Environment.NewLine);

                Console.WriteLine("Inserting data to source table");
                Console.WriteLine("-------------------------------");
                for (int counter = 0; counter < SOURCE_TABLE_INSERTS.Count; counter ++)
                {
                    ExecuteNonQueryOnCommand(sourceConnection, SOURCE_TABLE_INSERTS[counter]);
                    Console.WriteLine("\t" + (counter + 1) + " row(s) inserted");
                }
                Console.WriteLine("-------------------------------");
                Console.WriteLine("Data insertion completed on source server" + Environment.NewLine);

                Console.WriteLine("Selecting all records from source table " + SOURCE_TABLE_NAME);
                SelectAllData(sourceConnection, SOURCE_TABLE_NAME);
                Console.WriteLine("Selected all records from source table " + SOURCE_TABLE_NAME + Environment.NewLine);

                PsqlCommand sourceCommand = sourceConnection.CreateCommand();
                Console.WriteLine("Command object is created for source connection" + Environment.NewLine);

                sourceCommand.CommandText = SELECT_TOP3_FROM_SOURCE;
                Console.WriteLine("Command is created to select top 3 data from source table" + Environment.NewLine);

                sourceReader = sourceCommand.ExecuteReader();
                Console.WriteLine("Reader is executed on the source table" + Environment.NewLine);

                PsqlBulkCopy bulkCopy = new PsqlBulkCopy(destinationConnection)
                {
                    DestinationTableName = DESTINATION_TABLE_NAME
                };
                Console.WriteLine("Bulk copy object is created on the destination connection" + Environment.NewLine);

                bulkCopy.WriteToServer(sourceReader);
                Console.WriteLine("Data is written to destination server using bulk copy" + Environment.NewLine);

                sourceReader.Close();
                Console.WriteLine("Reader on source is closed" + Environment.NewLine);

                sourceCommand.Dispose();
                Console.WriteLine("Source command is disposed" + Environment.NewLine);
                
                Console.WriteLine("Selecting all records from destination table " + DESTINATION_TABLE_NAME);
                SelectAllData(destinationConnection, DESTINATION_TABLE_NAME);
                Console.WriteLine("Selected all records from destination table " + DESTINATION_TABLE_NAME + Environment.NewLine);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception occurred - " + ex.Message);
            }
            finally
            {
                if (null != sourceReader && !sourceReader.IsClosed)
                {
                    sourceReader.Close();
                }

                if (null != sourceConnection && sourceConnection.State == ConnectionState.Open)
                {
                    ExecuteNonQueryOnCommand(sourceConnection, DROP_TABLE + SOURCE_TABLE_NAME);
                    Console.WriteLine("Table '" + SOURCE_TABLE_NAME + "' is dropped from source server" + Environment.NewLine);
                    sourceConnection.Close();
                    Console.WriteLine("Source connection is closed" + Environment.NewLine);
                }
                if (null != destinationConnection && destinationConnection.State == ConnectionState.Open)
                {
                    ExecuteNonQueryOnCommand(destinationConnection, DROP_TABLE + DESTINATION_TABLE_NAME);
                    Console.WriteLine("Table '" + DESTINATION_TABLE_NAME + "' is dropped from destination server" + Environment.NewLine);
                    destinationConnection.Close();
                    Console.WriteLine("Destination connection is closed" + Environment.NewLine);
                }      
            }
        }

        private static void SelectAllData(PsqlConnection connection, string tableName)
        {
            PsqlCommand command = null;
            try
            {
                command = connection.CreateCommand();
                command.CommandText = SELECT_ALL + tableName;
                PsqlDataReader reader = command.ExecuteReader();

                int columnCount = reader.GetSchemaTable().Rows.Count;
                Console.WriteLine("-------------------------------");
                while (reader.Read())
                {
                    for (int counter = 0; counter < columnCount; counter++)
                    {
                        Console.Write((counter == 0 ? string.Empty : " - ") + reader.GetString(counter));
                    }
                    Console.Write(Environment.NewLine);
                }
                Console.WriteLine("-------------------------------");

                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception occurred while selecting all records from table '" + tableName + "' - " + ex.Message);
            }
            finally
            {
                command.Dispose();
                command = null;
            }
        }

        private static void ExecuteNonQueryOnCommand(PsqlConnection connection, string commandText)
        {
            PsqlCommand command = null;
            try
            {
                command = connection.CreateCommand();
                command.CommandText = commandText;
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception occurred while executing query '" + commandText + "' - " + ex.Message);
            }
            finally
            {
                command.Dispose();
                command = null;
            }
        }
    }
}
