CRUDDummyAccess1a

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CRUDDummyAccessConsole
{
    class Program
    {
        static OleDbConnection aConnection;
        static void Main(string[] args)
        {
            // 1) establish connection
            aConnection =
                new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\fmi\\Source\\Repos\\abc\\321312321_StoyanCheresharov\\Task1\\CRUDDummyAccessConsole\\CRUDDummyAccessConsole\\data\\Users.accdb");

            Add();
            Update(4);
            Delete(7);
            // 2) prepare the SQL statement
            OleDbCommand aCommand = new OleDbCommand("SELECT * from users", aConnection);
            try
            {
                aConnection.Open();

                OleDbDataReader aReader = aCommand.ExecuteReader();
                Console.WriteLine("This is the returned data from emp_test table");
                while (aReader.Read())
                {
                    // Console.WriteLine(aReader.GetInt32(0).ToString());
                    // Console.WriteLine(aReader.GetString(1));
                    Console.WriteLine("{0} \t {1}", aReader.GetInt32(0).ToString(), aReader.GetString(1));
                }

                aReader.Close();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        public static void Add()
        {
            try
            {
                aConnection.Open();
                OleDbCommand aCommand = new OleDbCommand("INSERT INTO Users (username,`password`,email) VALUES ('rewr','sdsa','my@example.com')", aConnection);
                // !!! SQL Injections
                aCommand.ExecuteNonQuery();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        public static void Update(int ID)
        {
            try
            {
                aConnection.Open();
                // SQL Injections etc.
                // OleDbCommand aCommand = new OleDbCommand("UPDATE users SET username = 'upadated' WHERE ID = " + ID.ToString(), aConnection);
                OleDbCommand aCommand = new OleDbCommand("UPDATE users SET username = 'upadated' WHERE ID = @par1", aConnection);
                // 1) approach with AddWithValue
                // aCommand.Parameters.AddWithValue("@par1", ID);

                // 2) approach with AddRange
                aCommand.Parameters.AddRange(new[] {
                    new OleDbParameter("@par1", ID)
                });
                aCommand.ExecuteNonQuery();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        public static void Delete(int ID)
        {
            try
            {
                aConnection.Open();
                OleDbCommand aCommand = new OleDbCommand("DELETE FROM users WHERE ID = " + ID, aConnection);
                aCommand.ExecuteNonQuery();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }
    }
}
/*
         public static void Update(int ID)
        {
            try
            {
                aConnection.Open();
                //            OleDbCommand aCommand = new OleDbCommand("INSERT INTO Users (ID, username, password, email) VALUES(3, 'Test', 'testpass', 'testemail')", aConnection);
                //- OleDbCommand aCommand = new OleDbCommand("INSERT INTO Users (username, password, email) VALUES('Test', 'testpass', 'testemail')", aConnection);

               // OleDbCommand aCommand = new OleDbCommand("UPDATE Users SET username='rewrUpdated' WHERE ID=3", aConnection);
                OleDbCommand aCommand = new OleDbCommand("UPDATE Users SET username='rewrUpdated' WHERE ID=@par1", aConnection);
                // 1)
                //- aCommand.Parameters.AddWithValue("@par1", ID);
                // or
                // 2)
                aCommand.Parameters.AddRange(new[] {
                    new OleDbParameter("@par1", ID)
                });

                int afectedRows = aCommand.ExecuteNonQuery();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
 */