Click here to Skip to main content
15,313,290 members
Articles / All Topics
Technical Blog
Posted 14 Feb 2011

Tagged as


11 bookmarked

Working with Entities instead of DataTable Objects...

Rate me:
Please Sign up or sign in to vote.
4.64/5 (8 votes)
14 Feb 2011CPOL3 min read
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;
                    // Convert the db type into the T we have in our Nullable<T> type
                    val = Convert.ChangeType
			(val, Nullable.GetUnderlyingType(pInfo.PropertyType));
                // 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>();

    // 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... :)


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Gilly Barr
Web Developer
Israel Israel
Started programming e-commerce sites with PHP & MySQL at the age of 14. Worked for me well for about 5 years.

Transfered to C# &, while serving in the IDF.
Worked on the 'Core Performance' Team at (Sears Israel)
Currently working at

Check out my blog!
or my twitter

Comments and Discussions

Praisegood one, thanks Pin
AmitMukherjee18-Nov-15 19:21
MemberAmitMukherjee18-Nov-15 19:21 
QuestionNeed some guide line Pin
Tridip Bhattacharjee25-Aug-14 1:42
professionalTridip Bhattacharjee25-Aug-14 1:42 
GeneralMy vote of 3 Pin
shalindra201113-Feb-14 2:34
Membershalindra201113-Feb-14 2:34 
QuestionDon't you think this method is slower Pin
shalindra201113-Feb-14 2:32
Membershalindra201113-Feb-14 2:32 
AnswerRe: Don't you think this method is slower Pin
Gilly Barr13-Feb-14 23:22
MemberGilly Barr13-Feb-14 23:22 
GeneralRe: Don't you think this method is slower Pin
shalindra201118-Feb-14 13:58
Membershalindra201118-Feb-14 13:58 
GeneralRe: Don't you think this method is slower Pin
Gilly Barr18-Feb-14 18:55
MemberGilly Barr18-Feb-14 18:55 
GeneralRe: Don't you think this method is slower Pin
shalindra201119-Feb-14 23:54
Membershalindra201119-Feb-14 23:54 
QuestionRe: Don't you think this method is slower Pin
stixoffire21-Jul-15 10:00
Memberstixoffire21-Jul-15 10:00 
GeneralMy vote of 5 Pin
AR_Libertarian18-Jul-13 4:30
MemberAR_Libertarian18-Jul-13 4:30 
QuestionNice learning Pin
wsc09189-Aug-12 15:04
Memberwsc09189-Aug-12 15:04 
Generalregarding ur article Working with Entities instead of DataTable Objects... Pin
MURARI0012-May-11 23:31
MemberMURARI0012-May-11 23:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.