Relational object mapping c#

Question

What is the best way to map relational table which has more than 150 columns. I don't want to use any 3rd party or entity framework. if i am using data-reader it is very hard to write all the columns and convert it to relevant type.

Is there any easy way to do it or is this the only way?

 SqlDataReader _dr = _sqlCom.ExecuteReader() ;

        Info inObj= new Info ();


        while (_dr.Read())
        {

            inObj.a=  (int)_dr["a"];
            inObj.b= (int)_dr["b"];
            inObj.c= (int)_dr["c"];
            inObj.d= (int)_dr["d"];
            inObj.e= (int)_dr["e"];
            .....

            ......
        }

There's a Micro-ORM called [PetaPOCO](http://www.toptensoftware.com/petapoco/). You may want to use that, if you don't want a real ORM. otherwise you have to code everything yourself, or use reflection, which is as crappy and slow. — Federico Berasategui, May 31 '13 at 20:08

score 0 · Answer 1 · answered May 31 '13 at 20:16

0

You could use reflection, if you're OK with incurring the performance hit. Something like this should work:

SqlDataReader reader = ...;
var propertyMappings = typeof (Info).GetProperties(BindingFlags.Instance | BindingFlags.Public)
    .Select(p => new {Property = p, Ordinal = reader.GetOrdinal(p.Name)})
    .ToList();
while (reader.Read())
{
    var info = new Info();
    foreach (var propertyMapping in propertyMappings)
        propertyMapping.Property.SetValue(info, reader[propertyMapping.Ordinal]);
}

answered May 31 '13 at 20:16

Michael Gunter

12,528
1
24
58

I don't know the exact numbers, but it is not insignificant. In many situations it's not a problem, but if you're processing 100K rows with 10 columns, it might add up to an issue for you. You may need to do your own testing. Note that the code above pulls out the ordinal for each property before entering the Read() loop. This will improve performance over the string-based reader["column"] calls. So if you want an apples-to-apples comparison while performing your own performance tests, you'll need to change your code to also use ordinals. – Michael Gunter Jun 03 '13 at 15:14

score 0 · Answer 2 · answered May 31 '13 at 20:21

If you want it done for you, you'll need to either use an ORM, there are some good ones out there, like Dapper, or use reflection (but will be slower). Otherwise you need to handle it all yourself. However instead of the ugly casting, you could write a few more lines of code to get rid of the casting, and would probably perform better

SqlDataReader _dr = _sqlCom.ExecuteReader() ;


Info inObj = new Info();

if (!_dr.HasRows) { return; }

int aPosition = _dr.GetOrdinal("a");}
int bPosition = _dr.GetOrdinal("b");
int cPosition = _dr.GetOrdinal("c");
int dPosition = _dr.GetOrdinal("d");
int ePosition = _dr.GetOrdinal("e");

while (_dr.Read())
{
    inObj.a = _dr.GetInt32(aPosition);
    inObj.b = _dr.GetInt32(bPosition);
    inObj.c = _dr.GetInt32(cPosition);
    inObj.d = _dr.GetInt32(dPosition);
    inObj.e = _dr.GetInt32(ePosition);

    .....

    ......
}

Relational object mapping c#

2 Answers2