TableDataGateway

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

namespace CRUDDummyAccessConsole3a
{
    class Users
    {
       // IDbConnection aConnection; // the best
        // DbConnection aConnection; // second best
        OleDbConnection aConnection;

        string table = "users";

        public Users(OleDbConnection aConnection)
        {
            this.aConnection = aConnection;
        }

        public OleDbDataReader FetchAll()
        {
            OleDbDataReader aReader = null;
            OleDbCommand aCommand = new OleDbCommand("SELECT * FROM users", aConnection);
            try
            {
                aConnection.Open();
                aReader = aCommand.ExecuteReader();
                Console.WriteLine("This is the returned data from users table");
                // aReader.Close();
                // aConnection.Close(); // moved to finally always gets executed
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error {0}", e.Errors[0].Message);
            }
            finally
            {
                aReader.Close();
                aConnection.Close();
            }
            return aReader;
        }

        public int Create() // Insert
        {
            int numberOfRows = 0;
            try
            {
                aConnection.Open();
                // SQL Injection
                OleDbCommand aCommand = new OleDbCommand("INSERT INTO users (username, `password`, email) VALUES ('О''Харра','insertedPass', '[email protected]')", aConnection);
                numberOfRows = aCommand.ExecuteNonQuery();
                // Console.WriteLine("The number of rows during Insert were {0}", numberOfRows);
                // aConnection.Close(); // moved to finally
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error {0}", e.Errors[0].Message);
            }
            finally
            {
                aConnection.Close();
            }
            return numberOfRows;
        }

        public int Update(int id, string username, string password, string email)
        {
            int numberOfRows = 0;
            try
            {
                aConnection.Open();
                // SQL Injection
                // 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);
                // or
                // 2.
                aCommand.Parameters.AddRange(new[] {
                    new OleDbParameter("@par1", id)
//                     new OleDbParameter("@par2", username),
                });
                numberOfRows = aCommand.ExecuteNonQuery();
                // Console.WriteLine("The number of rows during Update were {0}", numberOfRows);
                // aConnection.Close(); // moved to finally
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error {0}", e.Errors[0].Message);
            }
            finally
            {
                aConnection.Close();
            }
            return numberOfRows;
        }

        public int Delete(int id)
        {
            int numberOfRows = 0;
            try
            {
                aConnection.Open();
                // SQL Injection
                // OleDbCommand aCommand = new OleDbCommand("UPDATE users SET username = 'updatedUsername' WHERE ID = " + ID, aConnection);
                OleDbCommand aCommand = new OleDbCommand("DELETE FROM users WHERE ID = @par1", aConnection);
                // 1. approach with AddWithValue
                aCommand.Parameters.AddWithValue("@par1", id);
                // or
                // 2.
                //                aCommand.Parameters.AddRange(new[] {
                //                    new OleDbParameter("@par1", ID)
                //                     new OleDbParameter("@par2", username),
                //                });
                numberOfRows = aCommand.ExecuteNonQuery();
                Console.WriteLine("The number of rows during Delete were {0}", numberOfRows);
                // aConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error {0}", e.Errors[0].Message);
            }
            finally
            {
                aConnection.Close(); // moved to finally
            }
            return numberOfRows;
        }

        public OleDbDataReader fetchOneById(int id)
        {
            OleDbDataReader aReader = null;
            OleDbCommand aCommand = new OleDbCommand("SELECT * FROM users WHERE id = @par1", aConnection);
            aCommand.Parameters.AddWithValue("@par1", 1);
            try
            {
                aConnection.Open();
                aReader = aCommand.ExecuteReader();
                Console.WriteLine("This is the returned data from users table");
                // aReader.Close();
                // aConnection.Close(); // move do finally
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error {0}", e.Errors[0].Message);
            }
            finally
            {
                aReader.Close();
                aConnection.Close();
            }
            return aReader;
        }

    }
}