C# Populating object from database

Question

Currently our DAL (Data Access Layer) queries the database and we loop through the resultant data to populate an object (one object per row) in which the final result is a List.

What is the most efficient (and quickest) way to populate the objects?

Which is the quickest way to get data out of the database and into a specific object designed for that result set?

This is C#

-- EDIT

By quickest, we mean the fastest for the computer to process, not speed of development.
This is also a .NET 2.0 application

--

Why not using Linq-To-Sql, nHibernate or any other ORM-Tool? — Yves M., Nov 05 '10 at 11:37
Actually I prefer to write my own DAL but admit that it is time-consuming. By quickest I mean, fastest for the computer to process, not quickest to write. — Horges, Nov 05 '10 at 11:40
Please define "quickest" and "most efficient". Are you after performance or ease of development? — Anders Fjeldstad, Nov 05 '10 at 11:40
The processing overhead of using Linq2SQL is insignificant in comparison to the latency and bandwidth limitations of calling the database server. — Albin Sunnanbo, Nov 05 '10 at 11:55

score 1 · Answer 1 · answered Nov 05 '10 at 11:37

There are a lot of ways to do this, depends on which version of C# you use.
You can use the following:

1. DataSet (.net 2.0 above I think?)
2. Linq2SQL (.net 3.5 above)
3. EntityFramework (.net 3.5 above)

For example of using Linq2SQL:

var result = (from t in YourTable
              select t).ToList();

score 1 · Answer 2 · answered Nov 05 '10 at 11:42

1

Most efficient is how you are doing using a forward-only SqlDataReader http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Requires plenty of "boiler plate" code however so not the quickest to write. I personally use NHiberate for that but that requires some learning http://nhforge.org/Default.aspx

answered Nov 05 '10 at 11:42

Adam

619
1
4
19

See for example this benchmark: http://www.devtoolshed.com/content/performance-benchmarks-linq-vs-sqldatareader-dataset-linq-compiled-queries-part-2 – Anders Fjeldstad Nov 05 '10 at 11:44

score 0 · Answer 3 · edited May 23 '17 at 12:04

I believe the quickest (as in quickest to develop) is LINQ to SQL.

It does exactly that. Create classes that represent a list of attributes from each table. When accessing them, it retrieves data automatically from the database and populates these objects with data, which you can use then as a list of objects. It's all automated. You just write one single row of code for this, after you've made the mapping (dragged tables from the database to the Linq to SQL mapping)

The code can look as simple as this:

List<Something> yourList = YourAutomaticallyGeneratedDataContext.Something.ToList();

For Firebird, of course, LINQ to SQL won't work. Try Entity Framework instead. NHibernate is too much of a fuss, in my opinion.

But since you want the Quickest to process, I recommend you check out this site: http://ormbattle.net/ Choose your weapon, then fire. I am not considering the option with DataReader. It's a pain, and can lead to bugs. It's hard to maintain too.

Or, check this question, Best free ORM tools to use with .NET 2.0/3.5

Edited. Check out last row. It's a reference to a question here on SO that might help you. — Alex, Nov 05 '10 at 11:46

C# Populating object from database

3 Answers3