.NET Object Design

Question

I have a series of objects I have created:

Item

Order

Song

etc.

Each object has a reasonable number of properties, and I use a datareader where I pass it "SELECT * FROM .objectname." and then I fill a collection of objects, and return the collection. This works as: GetOrdersCollection(), GetSongsCollection(), etc.

I understand SELECT * to be a performance problem, and additionally, sometimes I prefer to include additional columns in the select statement which do not exist in the object, and have those all returned as well.

So my question is, what is the best way to approach this problem?

1. Should I create a new object for every query type?

2. I tried performing a check to see if column is in datareader before storing it, but this presents perf. issues. Is there a negligible perf. way to avoid IndexOutOfRange?

3. Should I just use Datatable and read right from the table?

Answer 1

Performance-wise reading from a forward only data structure like DataReader is going to net you the best performance and resource conservation.

On the other hand populating object (like a OR/M does) can be negligible so long as you are not returning more than a handful of objects.

Your first step should be to profile your database and ensure that you have proper indexes. Write some tests to see where your largest time expense is in the process and optimize the target areas that cost you the most.

Answer 2

Are there any reasons you can't use a simple ORM generator like SubSonic? This will allow you to very easily access these types of collections, and they'll be strongly typed. You also won't have to worry about the SQL since the queries will be built by SubSonic.

Answer 3

I understand SELECT * to be a performance problem,

It's not a performance problem if there are only a few columns, or you need all of the columns anyway.

1.Should I create a new object for every query type?

You should create a new object for each table, and a new method for each query type.

2.I tried performing a check to see if column is in datareader before storing it, but this presents perf. issues. Is there a negligible perf. way to avoid IndexOutOfRange?

If you are referring to your fields by name rather than index, there shouldn't be any IndexOutOfRange problems. If you are referring to your fields by index, you can loop thru them where your index is less than the column Count(), and there shouldn't be any IndexOutOfRange problems.

3.Should I just use Datatable and read right from the table?

That's a perfectly good approach to start out with. Consider spending some time to learn a simple ORM as others have suggested. Subsonic is a good "first" ORM.