sql - Transforming DataTable to List<T> in C# -

i've searched through stackoverflow (and other websites) transforming datatable list reflection in c#.

my results until pretty good: can reflect 200k lines in 3.5 seconds (0.5 seconds in hardcoded mode).

but entities (the classes represent data, think know that) follow pattern:

my database have columns (i don't this, you'll idea):

table: clients columns:     clientid, clientname, clientphone, cityid[fk] 

i'm using sqlconnection (mysqlconnection), have hardcode entities , transform database result in list of entity. like:

select *, cit.* 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 sql correct, think got idea. should return fields this:

clientid, clientname, clientphone, cityid, cityname, countryid, countryname 

shoud result list<client>.

here's problem: have 2 inner joins , represent data in entities (i 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 have client object, country name expression client.clientcity.citycountry.countryname. call 3-level property acessor.

and want reflect properly. here main method transform datatable list. native language portuguese, tried translate comments match description above.

the idea of code is: try find in main class column have set. if don't find it, search property in properties objects. cityname inside clientcity inside client. code mess.

 public list<t> tolist<t>(datatable dt) t : new()     {          type type= typeof(t);         reflectionhelper h = new reflectionhelper(type);         insertpropinfo(tipo);  //a pre-reflection work, cache delegates, etc..         list<t> list = new list<t>();         datatablereader dtr = dt.createdatareader();         while (dtr.read())         {             t obj = new t();             (int = 0; < dtr.fieldcount; i++)             {                 getobject(ref obj, tipo, dtr.getname(i), dtr.getvalue(i));             }             list.add(obj);         }          return lista;      }         //ref t obj: object create before calling method         //type classtype: type of object (say, client)         //string colname: database column i'm trying fill. clientid or cityname or countryname.         //collinedata: data want put in colname.      public void getobject<t>(ref t obj, type classtype, string colname, object collinedata) t : new()     {           //i caching reflect once, , after first iteration, think reflection need done.         foreach (propertyinfo info in _classpropinfos[classtype])         {             //if current propertyinfo valuetype (like int, int64) or string, , on             if (info.propertytype.isvaluetype || info.propertytype == typeof(string))             {                 //i think string.equals little faster, had not difference using "string" == "string"                 if (info.name.equals(colname)) //did found property?                      if (info.propertytype != typeof(char)) //i have convert type if char. mysql returns char string.                     {                         _delegatesetters[info](obj, collinedata); //if isn't char, set it.                     }                     else                     {                         _delegatesetters[info](obj, convert.changetype(collinedata, typeof(char)));                     }                 break;             }             else //but, if property class, clientcity:             {                 //i reflect city class, if isn't reflected yet:                 if (!_classpropinfos.containskey(info.propertytype))                 {                     insertpropinfo(info.propertytype);                 }                 //now search property:                 boolean foundproperty = false;                 object instance = _delegategetters[info](obj); //get existing instance of clientcity, can fill cityid , cityname in same object.                  foreach (propertyinfo subinfo in _classpropinfos[info.propertytype])                 {                     if (subinfo.name.equals(colname))//did found property?                     {                         if (instance == null)                         {                             //this happen if i'm trying set first property of class, cityid. have instanciate it, in next iteration won't null, , have it's cityid filled.                             instance = _initializers[info.propertytype]();//a fast object initializer. i'm worried dictionary lookups, have no other idea how cache it.                         }                         _delegatesetters[subinfo](instance, collinedata);//set data. method fast. search lambda getters & setters using system.linq.expression.                         foundproperty = true;                         break;//i break loops when find property, wont iterate anymore.                     }                  }                 if (foundproperty)//if found property in code above, set instance of clientcity client object.                 {                     _delegatesetters[info](obj, instance);                     break;                 }             }         }     } 

there problem code: can reach cityid , cityname, , fill it. countryid , countryname wont. because code can 2-level reflection, need recursive-approach fill many levels need. tried got many stack overflows , null reference exceptions gave up.

this code make easier fetch database rows, did find library or want? if not, how achieve n-level reflection make proper list datatable?

your problem common , practically every orm in circulation addresses question.
of course changing written application take advantage of orm unpractical, there simple orm easy add existing application , let replace incrementally written code.

one of these orms dapper. consists of 1 source file can include directly in same project poco classes , repository methods (or reference compiled assembly). easy learn , incredibly fast considering complexity of work carried out. not mention authors of little gem regularly on site answering questions on work. search #dapper tag

the nuisances have found date mapping one-to-one poco properties , field names , sometime eluding rules between pk , fk when keys not named id. that's me still haven't understood these rules.