Transforming DataTable to List<T> in C#

Question

I've already searched through StackOverflow (and other websites) about transforming a DataTable to List with reflection in C#.

My results until now are pretty good: I can reflect 200k lines in 3.5 seconds (0.5 seconds in hardcoded mode).

But my entities (the classes that represent my data, but I think you already know that) follow this pattern:

My database have columns like this (I don't actually do this, but you'll get the idea):

Table: Clients
Columns:
    ClientID, ClientName, ClientPhone, CityID[FK]

I'm using SqlConnection (MySqlConnection), so I have to hardcode my entities and transform the database result in a list of this entity. Like:

Select *, cit.* from Clients cli
Inner join Cities cit on (cit.CityID == cli.CityID)
Inner join Countries cou on (cou.CountryID == cit.CountID)

I don't know if this SQL is correct, but I think you got the idea. This should return some fields like this:

ClientID, ClientName, ClientPhone, CityID, CityName, CountryID, CountryName

Shoud result a List<Client>.

Here's the problem: I have 2 inner joins and I represent this data in my entities like this (I like the expression "like this"):

public class Client
{
    public int ClientID { get; set; }
    public string ClientName { get; set; }
    public string ClientPhone { get; set; }
    public City ClientCity { get; set; }
}

public class City
{
    public int CityID { get; set; }
    public string CityName { get; set; }
    public Country CityCountry { get; set; }
}

public class Country
{
    public int ContryID { get; set; }
    public string CountryName { get; set; }
}

So, if I have a Client object, I would get its country name by the expression client.ClientCity.CityCountry.CountryName. I call it a 3-level property acessor.

And I want to reflect it properly. Here is the main method to transform the DataTable into a List. My native language is Portuguese, but I tried to translate my comments to match my description above.

The idea of this code is: I try to find in the main class the column I have to set. If I don't find it, I search the property in the properties that are objects. Like CityName inside ClientCity inside Client. This code is a mess.

 public List<T> ToList<T>(DataTable dt) where T : new()
    {

        Type type= typeof(T);
        ReflectionHelper h = new ReflectionHelper(type);
        insertPropInfo(tipo);  //a pre-reflection work, I cache some delegates, etc..
        List<T> list = new List<T>();
        DataTableReader dtr = dt.CreateDataReader();
        while (dtr.Read())
        {
            T obj = new T();
            for (int i = 0; i < dtr.FieldCount; i++)
            {
                GetObject(ref obj, tipo, dtr.GetName(i), dtr.GetValue(i));
            }
            list.Add(obj);
        }

        return lista;

    }
        //ref T obj: the object I create before calling this method
        //Type classType: the type of the object (say, Client)
        //string colName: this is the Database Column i'm trying to fill. Like ClientID or CityName or CountryName.
        //colLineData: the data I want to put in the colName.

    public void GetObject<T>(ref T obj, Type classType, string colName, object colLineData) where T : new()
    {


        //I do some caching to reflect just once, and after the first iteration, I think all the reflection I need is already done.
        foreach (PropertyInfo info in _classPropInfos[classType])
        {
            //If the current PropertyInfo is a valuetype (like int, int64) or string, and so on
            if (info.PropertyType.IsValueType || info.PropertyType == typeof(string))
            {
                //I think string.Equals is a little faster, but i had not much difference using "string" == "string"
                if (info.Name.Equals(colName)) //did I found the property?

                    if (info.PropertyType != typeof(char)) //I have to convert the type if this is a Char. MySql returns char as string.
                    {
                        _delegateSetters[info](obj, colLineData); //if it isn't a char, just set it.
                    }
                    else
                    {
                        _delegateSetters[info](obj, Convert.ChangeType(colLineData, typeof(char)));
                    }
                break;
            }
            else //BUT, if the property is a class, like ClientCity:
            {
                //I reflect the City class, if it isn't reflected yet:
                if (!_classPropInfos.ContainsKey(info.PropertyType))
                {
                    insertPropInfo(info.PropertyType);
                }
                //now I search for the property:
                Boolean foundProperty = false;
                object instance = _delegateGetters[info](obj); //Get the existing instance of ClientCity, so I can fill the CityID and CityName in the same object.

                foreach (PropertyInfo subInfo in _classPropInfos[info.PropertyType])
                {
                    if (subInfo.Name.Equals(colName))//did I found the property?
                    {
                        if (instance == null)
                        {
                            //This will happen if i'm trying to set the first property of the class, like CityID. I have to instanciate it, so in the next iteration it won't be null, and will have it's CityID filled.
                            instance = _initializers[info.PropertyType]();//A very fast object initializer. I'm worried about the Dictionary lookups, but i have no other idea about how to cache it.
                        }
                        _delegateSetters[subInfo](instance, colLineData);//set the data. This method is very fast. Search about lambda getters & setters using System.Linq.Expression.
                        foundProperty = true;
                        break;//I break the loops when I find the property, so it wont iterate anymore.
                    }

                }
                if (foundProperty)//if I found the property in the code above, I set the instance of ClientCity to the Client object.
                {
                    _delegateSetters[info](obj, instance);
                    break;
                }
            }
        }
    }

There is a problem with this code: I can reach the CityID and CityName, and fill it. But CountryID and CountryName wont. Because this code can do a 2-level reflection, I need some recursive-approach to fill many levels I need. I tried to do this BUT i got so many stack overflows and null reference exceptions I almost gave up.

This code would make it much easier to fetch database rows, Did you already find some library or anything that does what I want? If not, how could I achieve a n-level reflection to make a proper List from a DataTable?

Answer 1

This is based on you getting a dataset with the 3 tables and creating the proper DataRelation. On your particular case(200k lines) i dont know how it will perform but shouldnt be that bad :).

Your calling code could be something like this:

List<Clients> clients = Test.CreateListFromTable<Clients>(ds.Tables["Clients"]);

Remember as i said its based in you fettching the dataset and creating the relations. Next here is the class with the methods in question(ClientsToCity and CityToCountry are the names of the datarelations,you can place your own):

    public class Test
    {
        // function that set the given object from the given data row
        public static void SetItemFromRow<T>(T item, DataRow row) where T : new()
        {
            foreach (DataColumn c in row.Table.Columns)
            {
                PropertyInfo prop = item.GetType().GetProperty(c.ColumnName);

                if (prop != null && row[c] != DBNull.Value)
                {
                    prop.SetValue(item, row[c], null);
                }
                else
                {
                    if (c.ColumnName == "CityID")
                    {
                        object obj = Activator.CreateInstance(typeof(City));

                        SetItemFromRow<City>(obj as City, row.GetChildRows("ClientsToCity")[0]);
                        PropertyInfo nestedprop = item.GetType().GetProperty("ClientCity");
                        nestedprop.SetValue(item, obj, null);
                    }
                    else if (c.ColumnName == "CountryID")
                    {
                        object obj = Activator.CreateInstance(typeof(Country));

                        SetItemFromRow<Country>(obj as Country, row.GetChildRows("CityToCountry")[0]);
                        PropertyInfo nestedprop = item.GetType().GetProperty("CityCountry");
                        nestedprop.SetValue(item, obj, null);
                    }
                }

            }
        }

        // function that creates an object from the given data row
        public static T CreateItemFromRow<T>(DataRow row) where T : new()
        {
            T item = new T();

            SetItemFromRow(item, row);

            return item;
        }

        // function that creates a list of an object from the given data table
        public static List<T> CreateListFromTable<T>(DataTable tbl) where T : new()
        {
            List<T> lst = new List<T>();

            foreach (DataRow r in tbl.Rows)
            {
                lst.Add(CreateItemFromRow<T>(r));
            }

            return lst;
        }
    }

Answer 2

Your problem is really common and practically every ORM in circulation addresses this question.
Of course changing an already written application to take advantage of an ORM is often unpractical, but there are some simple ORM that are really easy to add to an existing application and let you replace incrementally the already written code.

One of these ORMs is DAPPER. It consists of just one source file that you can include directly in the same project with your POCO classes and repository methods (Or just reference the compiled assembly). It is really easy to learn and it is incredibly fast considering the complexity of the work to be carried out. Not to mention that the authors of this little gem are regularly on this site answering questions on their work. Just do a search with the #dapper tag

The only nuisances that I have found to date are the mapping one-to-one from your POCO properties and the field names and also the sometime eluding rules between PK and FK when your keys are not named ID. But that's me that I still haven't fully understood these rules.

Answer 3

Consider to use EntityFramework. It will automate all this work.