Each row of a DataTable as a Class?

Question

I have a "location" class. This class basically holds addresses, hence the term "location". I have a datatable that returns multiple records which I want to be "locations".

Right now, I have a "load" method in the "location" class, that gets a single "location" by ID. But what do I do when I want to have a collection of "location" objects from multiple datatable rows? Each row would be a "location".

I don't want to go to the database for each record, for obvious reasons. Do I simply create a new instance of a location class, assigning values to the properties, while looping through the rows in the datatable bypassing the "load" method? It seems logical to me, but I am not sure if this is the correct/most efficient method.

score 2 · Answer 1 · edited May 23 '17 at 09:59

That is (your description) pretty much how a row (or a collection of rows) of data gets mapped to a C# biz object(s). But to save yourself a lot of work you should consider one of a number of existing ORM (object relational mapper) frameworks such as NHibernate, Entity Framework, Castle ActiveRecord etc.

Most ORMs will actually generate all the boilerplate code where rows and fields are mapped to your .NET object properties and vice-versa. (Yes, ORMs allow you to add, update and delete db data just as easily as retrieving and mapping it.) Do give the ORMs a look. The small amount of learning (there is some learning curve with each) will pay off very shortly. ORMs are also becoming quite standard and indeed expected in any application that touches an RDBMS.

Additionally these links may be of interest (ORM-related):

Wikipedia article on ORMs

SO Discussion on different ORMs

Many different .NET ORMs listed

score 1 · Accepted Answer · answered Oct 24 '11 at 21:55

You're on the right track, getting all the locations you need with one trip to the database would be best in terms of performance.

To make your code cleaner/shorter, make a constructor of your Location class that takes a DataRow, which will then set your properties accordingly. By doing this, you'll centralize your mapping from columns to properties in one place in your code base, which will be easy to maintain.

Then, it's totally acceptable to loop through the rows in your data table and call your constructor.

You could also use an object to relational mapper, like Entity Framework to do your database interaction.

Sweet. That's perfect. Don't know why I didn't think of that. — Michael C. Gates, Oct 24 '11 at 21:56

score 1 · Answer 3 · answered Oct 24 '11 at 21:57

Create a method that returns an IEnumerable . In this method do your database stuff and I often pass in the sqldatareader into the location constructor. So I would have something like this

public static IEnumerable<location> GetLocations()
{
   List<location> retval = new List<location>();
   using(sqlconnection conn = new sqlconn(connection string here);
   {
      sqlcommand command = new sqlcommand(conn, "spLoadData");
      command.commandtype=stored proc
      SqlDataReader reader = command.executereader();
      while(reader.read())
      {
          retval.add(new location(reader));
      }
   }
   return retval;
}

obviously that code won't work but it's just to give you an idea.

An ORM mapper could save you loads of time if you have lots to do however!

Each row of a DataTable as a Class?

3 Answers3