Working with Entities instead of DataTable Objects...

I started working on a big new project at work, with a couple of other programmers. This project involves a really big ERD, meaning there are a bunch of entities in the DB, with a lot of relationships between them.

I personally am very fond of working with ORMs, and I am especially familiar with NHibernate which is great in my opinion, and would really work fine in this scenario.
Unfortunately though, some people involved in the project didn't want to work with NHibernate, or any other ORM, with the excuse of "some people aren't familiar with ORMs", "I had bad experience working with ORMs in the past", yada, yada, yada...

I'm guessing a lot of you are familiar with this kind of frustration at the work place, with corporate politics and people that aren't keen on learning new technologies.
Instead of getting all frustrated about it this time and trying to fight over a lost cause, I decided to make the best of it...

Obviously, this project, like all the others, is on a very tight schedule.
...So writing up my own ORM, without calling it an "ORM" is out of the question! :-P
I decided to do the least that will help.

Here's my solution:

• I built the ERD in the db. In this case, it's Oracle 11g.
• Then I built a lot of different views so that I will see all the data like the Entities I would've used in an ORM.
• I created a simple DAL, using plain ADO.NET, that has the ability to execute stored procedures, and return DataTable objects (super-straight-forward here).
• I created a class for every entity I will need to work with. Each entities class is built in such a way that all its properties match all the columns in a certain view that I built in the db.
• I created a small utility that will convert my DataTables into the entities I built, and then I can work with all the data like I would with objects and not DataTables.

The method that converts a single DataRow into the chosen entity uses reflection (obviously), and looks like this:

public static T ConvertToEntity<T>(this DataRow tableRow) where T : new()
{
    // Create a new type of the entity I want
    Type t = typeof(T);
    T returnObject = new T();

    foreach (DataColumn col in tableRow.Table.Columns)
    {
        string colName = col.ColumnName;

        // Look for the object's property with the columns name, ignore case
        PropertyInfo pInfo = t.GetProperty(colName.ToLower(),
            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

        // did we find the property ?
        if (pInfo != null)
        {
            object val = tableRow[colName];

            // is this a Nullable<> type
            bool IsNullable = (Nullable.GetUnderlyingType(pInfo.PropertyType) != null);
            if (IsNullable)
            {
                if (val is System.DBNull)
                {
                    val = null;
                }
                else
                {
                    // Convert the db type into the T we have in our Nullable<T> type
                    val = Convert.ChangeType
			(val, Nullable.GetUnderlyingType(pInfo.PropertyType));
                }
            }
            else
            {
                // Convert the db type into the type of the property in our entity
                val = Convert.ChangeType(val, pInfo.PropertyType);
            }
            // Set the value of the property with the value from the db
            pInfo.SetValue(returnObject, val, null);
        }
    }

    // return the entity object with values
    return returnObject;
}

In order to use this method on a DataTable as well, we just need to iterate on the rows and insert them into a list. I did it like this:

public static List<T> ConvertToList<T>(this DataTable table) where T : new()
{
    Type t = typeof(T);

    // Create a list of the entities we want to return
    List<T> returnObject = new List<T>();

    // Iterate through the DataTable's rows
    foreach (DataRow dr in table.Rows)
    {
        // Convert each row into an entity object and add to the list
        T newRow = dr.ConvertToEntity<T>();
        returnObject.Add(newRow);
    }

    // Return the finished list
    return returnObject;
}

Both of these are extension methods. A great use of them in my opinion.
You just need to stick these into a static class, and it gives you the ability to invoke this method on any DataTable you like throughout your project and getting back any type of object you like.

DataTable dt = Dal.GetCompanies();
List<Entities.Company> companyList = dt.ConvertToList<Entities.Company>();

Now, when my DAL returns me a DataTable, I can easily convert it to a list, and work with that as if I were with regular objects.
In my case, most of the project is supposed to end up to be a couple of web services, that select the data from the db, do a bunch of manipulations, and return it in a big XML. So using this concept in this specific case helps me out a lot, since after manipulating the data, I just need to serialize it as XML, and send it as a web service response.

If I needed to insert it back to the DB though, It would be pretty easy to create a method to convert entities back to DataTable objects.
Probably something like this:

public static DataTable ConvertToDataTable(this object obj)
{
    // Retrieve the entities property info of all the properties
    PropertyInfo[] pInfos = obj.GetType().GetProperties();

    // Create the new DataTable
    var table = new DataTable();

    // Iterate on all the entities' properties
    foreach (PropertyInfo pInfo in pInfos)
    {
        // Create a column in the DataTable for the property
        table.Columns.Add(pInfo.Name, pInfo.GetType());
    }

    // Create a new row of values for this entity
    DataRow row = table.NewRow();
    // Iterate again on all the entities' properties
    foreach (PropertyInfo pInfo in pInfos)
    {
        // Copy the entities' property value into the DataRow
        row[pInfo.Name] = pInfo.GetValue(obj, null);
    }

    // Return the finished DataTable
    return table;
}

Some final thoughts on this...
This obviously isn't the best solution to this case, and obviously isn't something ground-breaking either. I decided to show this as presenting a simple solution that helps a lot when it comes to trying to deal with sh***y (in my opinion, obviously) circumstances.

Hope this helps, at least some... :)