CodeProject
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 ORM's, 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 ORM's", "I had bad experiencing working with ORM's 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 it's properties match all the columns in a certain view that I built in the db.
- I created a small utility that will convert my DataTable's 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 entitie's 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 entitie's properties
foreach (PropertyInfo pInfo in pInfos)
{
// Copy the entitie's 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 neither. I decided to show this as presenting a simple solution that helps a lot when it comes to trying to deal with shitty (in my opinion, obviously) circumstances.
Hope this helps, at least some... :)
