using SwanCSharp.DataAccess;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace DataAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            // Access
            Access();
            // SQL Server
            SQLServer();
        }

        private static void Access()
        {
            try
            {
                // Open Connection
                DataConnection lobjConnection = new DataConnection("DataBase.mdb", "", "", DatabaseManager.Access);

                //INSERT
                string lstrSQLCommand = "INSERT INTO Staff (IDPerson, Name, Lastname, Street, City) ";
                lstrSQLCommand += " VALUES (30, 'John', 'Smith', '5th', 'New York')";
                Boolean lblnInsertOne = lobjConnection.SQLCommandExecute(lstrSQLCommand);

                lstrSQLCommand = "INSERT INTO Staff (IDPerson, Name, Lastname, Street, City) ";
                lstrSQLCommand += " VALUES (40, 'David', 'Johnson', '8th', 'Boston')";
                Boolean lblnInsertTwo = lobjConnection.SQLCommandExecute(lstrSQLCommand);
                if (lblnInsertOne)
                {
                    Console.WriteLine("The first row has been inserted sucessfully");
                }
                if (lblnInsertTwo)
                {
                    Console.WriteLine("The second row has been inserted sucessfully");
                }

                //SELECT
                lstrSQLCommand = "SELECT Name, Lastname FROM Staff";
                DataTable lobjData = lobjConnection.SQLSelectExecute(lstrSQLCommand);

                foreach (DataRow dr in lobjData.Rows)
                {
                    Console.WriteLine("Staff Name: " + dr["Name"].ToString() + " -- Last name: " + dr["Lastname"].ToString());
                }

                //DELETE
                lstrSQLCommand = "DELETE FROM STAFF WHERE IDPERSON=30";
                Boolean lblnDeleteOne = lobjConnection.SQLCommandExecute(lstrSQLCommand);

                lstrSQLCommand = "DELETE FROM STAFF WHERE IDPERSON=40";
                Boolean lblnDeleteTwo = lobjConnection.SQLCommandExecute(lstrSQLCommand);
                if (lblnDeleteOne)
                {
                    Console.WriteLine("The first row has been deleted sucessfully");
                }
                if (lblnDeleteTwo)
                {
                    Console.WriteLine("The second row has been deleted sucessfully");
                }
                // Close connection
                lobjConnection.CloseConnection();
            }
            catch(Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        private static void SQLServer()
        {
            try
            {
                // Open Connection
                DataConnection lobjConnection = new DataConnection("PC-NAME\\INSTANCE SQL", "DataBase", DatabaseManager.SQLServer);

                //INSERT
                string lstrSQLCommand = "INSERT INTO Staff (IDPerson, Name, Lastname, Street, City) ";
                lstrSQLCommand += " VALUES (30, 'John', 'Smith', '5th', 'New York')";
                Boolean lblnInsertOne = lobjConnection.SQLCommandExecute(lstrSQLCommand);

                lstrSQLCommand = "INSERT INTO Staff (IDPerson, Name, Lastname, Street, City) ";
                lstrSQLCommand += " VALUES (40, 'David', 'Johnson', '8th', 'Boston')";
                Boolean lblnInsertTwo = lobjConnection.SQLCommandExecute(lstrSQLCommand);
                if (lblnInsertOne)
                {
                    Console.WriteLine("The first row has been inserted sucessfully");
                }
                if (lblnInsertTwo)
                {
                    Console.WriteLine("The second row has been inserted sucessfully");
                }

                //SELECT
                lstrSQLCommand = "SELECT Name, Lastname FROM Staff";
                DataTable lobjData = lobjConnection.SQLSelectExecute(lstrSQLCommand);

                foreach (DataRow dr in lobjData.Rows)
                {
                    Console.WriteLine("Staff Name: " + dr["Name"].ToString() + " -- Last name: " + dr["Lastname"].ToString());
                }

                //DELETE
                lstrSQLCommand = "DELETE FROM STAFF WHERE IDPERSON=30";
                Boolean lblnDeleteOne = lobjConnection.SQLCommandExecute(lstrSQLCommand);

                lstrSQLCommand = "DELETE FROM STAFF WHERE IDPERSON=40";
                Boolean lblnDeleteTwo = lobjConnection.SQLCommandExecute(lstrSQLCommand);
                if (lblnDeleteOne)
                {
                    Console.WriteLine("The first row has been deleted sucessfully");
                }
                if (lblnDeleteTwo)
                {
                    Console.WriteLine("The second row has been deleted sucessfully");
                }
                // Close connection
                lobjConnection.CloseConnection();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }
    }
}