Creating ASP.NET MVC application against Oracle database pt. 1

This is a first in a series of posts, as I will try to build ASP.NET MVC application against Oracle XE database, but this example can be used for any Oracle database greater than 9i.

I will try to use Oracle ODAC.net the latest version that I can get from the Oracle Site.

With a little help from LILkillaBEE I created generic helpers that return IEnumerable<T> on which I can use LINQ and play with it.

First thing first, as I am creating a Contact manager application part by part, the first functionality i want to implement is getting the data from the Oracle XE database and working with it in MVC way. So i have created the table with personal data only in it.

CREATE TABLE PEOPLE
(
 PERSON_ID NUMBER NOT NULL ENABLE,
 NAME VARCHAR2(100) NOT NULL ENABLE,
 SURNAME VARCHAR2(100) NOT NULL ENABLE,
 ADDRESS_LINE1 VARCHAR2(100) NOT NULL ENABLE,
 ADDRESS_LINE2 VARCHAR2(100),
 ZIP_CODE VARCHAR2(100) NOT NULL ENABLE,
 CITY VARCHAR2(100) NOT NULL ENABLE,
 COUNTRY VARCHAR2(100) NOT NULL ENABLE,
 CONSTRAINT PEOPLE_PK PRIMARY KEY (PERSON_ID) ENABLE
 );

CREATE OR REPLACE TRIGGER BI_PEOPLE
before insert on PEOPLE
for each row
begin
select PEOPLE_SEQ.nextval into :NEW.PERSON_ID from dual;
end;

ALTER TRIGGER BI_PEOPLE ENABLE;

After this, i have created the Person class in my project

public class Person
{
  public virtual int PersonID { get; set; }
  public virtual string FirstName { get; set; }
  public virtual string LastName { get; set; }
  public virtual string AddressLine1 { get; set; }
  public virtual string AddressLine2 { get; set; }
  public virtual string ZipCode { get; set; }
  public virtual string City { get; set; }
  public virtual string Country { get; set; }
}

And an interface that I will implement later in my repository

interface IPersonInterface
{
  IEnumerable<Person> GetAll();
  Person GetById(int id);
  void Add(Person person);
  void Delete(int id);
}

EDIT: After considering to finish this tutorial i finally gave up because there is no easy way of doing this and i kept hitting the wall a few times.