CRUDDummyAccessConsole2b

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

namespace CRUDDummyAccessConsole2b
{
    class Program
    {
        static OleDbConnection aConnection;
        static void Main(string[] args)
        {
            // 1. connect to the DB

           // 1.1. Escape the \ with another \
            aConnection =
                new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\fmi\\Source\\Repos\\fmiedd\\111121321321_StudentStoayn\\CRUDDummyAccessConsole2b\\CRUDDummyAccessConsole2b\\data\\Users.accdb");
          // or
          // 1.2. Instead of escaping add @ up front of the string
//            aConnection =
//                new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\fmi\Source\Repos\fmiedd\111121321321_StudentStoayn\CRUDDummyAccessConsole2b\CRUDDummyAccessConsole2b\data\Users.accdb");

            Add();
            Update(4);
            Delete(7);

            // 2. Prepare the SQL statement
            OleDbCommand aCommand = new OleDbCommand("SELECT * from users", aConnection);

            // Retriev
            try
            {
                aConnection.Open();
                OleDbDataReader aReader = aCommand.ExecuteReader();
                Console.WriteLine("This is the returned data from users 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));
                    // or
                    // Console.WriteLine("{0} \t {1}", aReader[0], aReader[1]);
                }
                aReader.Close();
                aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        // C- Create
        public static void Add()
        {
            try
            {
                aConnection.Open();
                // SQL Injections ; DROP TABLE users;
                // Escape the symbols in the values with '
                // (e.g O'Harra escape like 'O''Harra'
                // You don'r have to care for escaping if you do parametrisation e.g. @par1 or
                // 1. Wrap the fields that look like keywords with `
                OleDbCommand aCommand = new OleDbCommand("INSERT INTO users (username, `password`, email) VALUES ('insertedUsernameO''Harra', 'insertedPassword', '[email protected]')", aConnection);
               // or
               // 2. Wrap the fields that look like keywords with []
//                OleDbCommand aCommand = new OleDbCommand("INSERT INTO users (username, [password], email) VALUES ('insertedUsername', 'insertedPassword', '[email protected]')", aConnection);

                int numberOfRows = aCommand.ExecuteNonQuery();
                aConnection.Close();
                Console.WriteLine("Number of records affected {0} from Insert", numberOfRows);
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        // Update
        public static void Update(int ID)
        {
            try
            {
                aConnection.Open();
                // OleDbCommand aCommand = new OleDbCommand("UPDATE users SET username = 'updatedUsername' WHERE ID = " + ID, aConnection);
                OleDbCommand aCommand = new OleDbCommand("UPDATE users SET username = 'updatedUsername' 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)
                });
                int numberOfRows = aCommand.ExecuteNonQuery();
                aConnection.Close();
                Console.WriteLine("Number of records affected {0} from Update", numberOfRows);
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

        // D - Delete
        public static void Delete(int ID)
        {
            try
            {
                aConnection.Open();
                OleDbCommand aCommand = new OleDbCommand("DELETE FROM users WHERE ID = @par1", aConnection);
                // aCommand.Parameters.AddWithValue("@par1", ID);
                aCommand.Parameters.AddRange(new[] {
                    new OleDbParameter("@par1", ID)
                });
                int numberOfRows = aCommand.ExecuteNonQuery();
                aConnection.Close();
                Console.WriteLine("Number of records affected {0} from Delete", numberOfRows);
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }
    }
}