Each row of a DataTable as a class?

Question

I have a class "location". This class mainly contains addresses, hence the term "location". I have a datatable that returns a few records that I want to be "locations".

Now I have a "load" method in the "location" class that gets one "location" by identifier. But what should I do when I want to have a collection of "location" objects from several rows of data? Each line will be a "location".

I do not want to go to the database for each record for obvious reasons. Am I just creating a new instance of the location class by assigning values ​​to the properties, iterating over the rows in a datatable, bypassing the "load" method? It seems logical to me, but I'm not sure if this is the right / most efficient method.

Answer 1

You are on the right track to get all the places you need with one trip to the database, it would be better in terms of performance.

To make your code cleaner / shorter, create a constructor for your Location class that accepts a DataRow , which then sets your properties accordingly. By doing this, you will centralize your mapping from columns to properties in one place in the code base, which will be easy to maintain.

Then it’s perfectly valid to scroll through the rows in your data table and call your constructor.

In addition, you can use the relational mapmaker object as an Entity Framework to interact with the database.

Answer 2

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

Most ORMs actually generate all the boilerplate code, where strings and fields are mapped to your .NET object properties and vice versa. (Yes, ORMs allow you to add, update, and delete db data as easily as retrieving and matching them.) Give ORMs a look. A small amount of training (there is some learning curve with each) will pay off very quickly. ORMs are also becoming quite standard and really expected in any RDBMS application.

In addition, these links may be of interest (related to ORM):

Wikipedia article on ORM

SO Discussion of various ORMs

Many different .NET ORMs listed

Answer 3

Create a method that returns IEnumerable. In this method, the database data and I often pass sqldatareader to 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, the code will not work, but it will just give you an idea.

An ORM converter can save you a lot of time if you have many opportunities to do it!