CustomerTable

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using PaymentClasses;


namespace Databases
{
    public class CustomerTable
    {
        private const string HOST = "calvin.humber.ca";
        private const string SID = "grok";
        internal const string PASSWORD = "oracle";
        private const string USER_ID = "N01317568";

        private static readonly string strCONNECTIONSTRING = string.Format("DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" +
                                                            "(HOST={0})(PORT=1521))(CONNECT_DATA=(SID={1}))); " +
                                                            "PASSWORD={2}; USER ID={3}", HOST, SID, PASSWORD, USER_ID);
        private OracleConnection oracleConnection;
        private OracleCommand oracleCommand;
        private OracleDataReader oracleDataReader;

        public CustomerTable()
        {
            try
            {
                oracleConnection = new OracleConnection(strCONNECTIONSTRING);
                oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;
                oracleConnection.Open();
            }
            catch (Exception objException)
            {
                throw objException;
            }
        }




        public Customer GetCustomer(string lname)
        {
            string strSQL = "SELECT * from customers Where LASTNAME = " + lname;
            Customer cust = new Customer();
            try
            {
                oracleCommand.CommandText = strSQL;
                oracleDataReader = oracleCommand.ExecuteReader();
                if (oracleDataReader.Read())
                {
                    cust.FirstName = Convert.ToString(oracleDataReader["FIRSTNAME"]);
                    cust.LastName = Convert.ToString(oracleDataReader["LASTNAME"]);
                    cust.Address = Convert.ToString(oracleDataReader["ADDRESS"]);
                }
                else
                {
                    Exception myException = new Exception("Sorry, that record id doesn't exist on the database");
                    throw myException;
                }
            }
            catch (Exception objException)
            {
                throw objException;
            }
            return cust;
        }
    }
}