OdbcTest

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

// Set the Properties -> Build to x64 bit
// The ODBC driver is 64 bit

namespace OdbcTest
{
    public class Person // : IId
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string FamilyName { get; set; }
        public DateTime BirthDate { get; set; }
        public string Address { get; set; }

        public override string ToString()
        {
            return string.Format("{0},{1},{2},{3},{4}",
                Id, Name,
                FamilyName,
                BirthDate.ToString("yyyy-MM-dd"),
                Address);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {

            var _connection = new OdbcConnection(@"DSN=OrmEdd;uid=ormedd;pwd=ormedd;DATABASE=ormedd");
            List<Person> persons = new List<Person>();

            try
            {
                #region Preparation can be outside of try
                IDbCommand command = _connection.CreateCommand();
                command.Connection = _connection;
                command.CommandText = @"SELECT * FROM `Persons`";
                #endregion

                _connection.Open();
                //Perform DB operation here i.e. any CRUD operation
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(Hydrate(reader));
                    }
                }
            }
            catch (Exception e)
            {
                // Log
                throw e;
            }
            finally
            {
                _connection.Close();
            }

            foreach(Person person in persons)
            {
                Console.WriteLine(person);
            }

        }


        private static Person Hydrate(IDataReader reader)
        {
            Person person = new Person();
            // It was working for GetAll with Id
            person.Id = (int)reader["ID"]; //  Id System.InvalidOperationException: No data exists for the row/column.
            person.Name = (string)reader["Name"];
            person.FamilyName = (string)reader["FamilyName"];
            // I don't need this System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.String'.
            // person.BirthDate = DateTime.Parse((string)reader["BirthDate"]); // we don't need cast
            person.BirthDate = (DateTime)reader["BirthDate"]; // but we still need a cast
            person.Address = (string)reader["Address"];
            return person;
        }

    }
}