How can I get a generic list from a datatable with the lowest overhead?

Question

I'm looking for best practices here. Sorry. I know it's subjective, but there are a lot of smart people here, so there ought to be some "very good" ways of doing this.

I have a custom object called Employee. That object has seven properties like name, phone, email, and so on. There is also a table in my SQL database called tblEmployees with seven columns labeled somewhat similarly. My goal is to "convert" the results from a query to a generic list of Employee objects. What is the best way to do this (lowest overhead, quickest)?

What I am doing currently is something that I've seen proposed all over the web. I don't like it because I feel like it slows down my page loads. Generic lists make me faster at what I do, but I don't feel good about making my customers pay the price.

Here's what I'm doing:

List<Employee> list = new List<Employee>();
DataSet ds = Employee.searchEmployees("Byron");
foreach (DataRow dr in ds.Tables[0].Rows)
{
   list.Add(new Employee(dr));
}

I have a constructor that takes a DataRow (as shown) which handles the 'property = dr["column"]' stuff.

Looking forward to your thoughts.

Answer 1

I would suggest using an OR Mapper like Linq2SQL or entity framework

Answer 2

The reason for building a list is to pass between functions on the server side.

What is the advantage of passing the list as opposed to passing a DataSet reference or even a DataTable?

Those thoughts aside, what you are currently doing is standard procedure for building a list. How do you think you could speed it up? By not instancing the Employee object? You would then have to do without the Employee object which would probably mess with your entity modeling.

Answer 3

Which part of the process do you feel is slow? Your methodology doesn't have any glaring performance bottlenecks as far as I can see.

Answer 4

Briefly looking at the code, and not seeing how it is used, I would return a IEnumerator instead of a list. You can then use the yield return statement and you won't be looping through the list twice (one to populate and one to display).

so...

protected IEnumerable<Employee> GetEmployees ()
{
   List<Employee> list = new List<Employee>();
   DataSet ds = Employee.searchEmployees("Byron");

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
       yield return new Employee(dr);
   }
}

Answer 5

What you are doing now is something that needs to be done one way or another at some point, and you can't really do it significantly faster than the way you have described. But you can reduce the number of times you have to do it at all by caching your data.

Store the list of customers in the cache. Populate it on application start for example. When something changes a customer record you update the cache and save the record to the database. Any reads by any user will go to the cache, not the database.

This approach will usually be an order of magnitude faster than any approach that hits the database.