Data Access Library Return DataSet or Object

Question

Is there a general consensus out there for when working with library's that call stored procedures? Return datasets or use sqldatareader to populate custom objects?

Is the cost of serialization your Data Transport Object less then a DataSet?

Answer 1

I also use the dataReader, but be aware, if you do, you must be careful to close it (and the connection it is using) as quickly as possible after you are done populating the custom object(s)... One gotcha to watch for is that when you call OpenReader(), make sure you pass the optional parameter called CommandBehavior set to CommandBehavior.CloseConenction, or, even if you close the reader, the underlying connection will not be closed and released to the pool until it gets picked up by the GC, which can easily cause you to run out of available connections if you're calling multiple reader objects in a loop.

Answer 2

At some point, it depends on the purpose of the library, or I sould say, the functionality of the library. Since the OOP hype, the "general consensus" is to first retrieve/fetch the data in the DAL using datareaders as they are faster, then load up your objects and close your readers, however, this is not always the case. To keep simplicity, some pass back the dataset so that gridviews can be bounded and paging/sorting can be enabled with minimal code. Remember simple is best.

In reporting application however, I've noticed a likeness for datasets, especially if the data is exposed by webservices.

The cost of serilization will be based on the usage of the application and also experience. An inexperience developer may return 3000 to 50000 rows of un-necessary data in a dataset. Remember the dataset is an animal, but with a lot of functionality. Use wisely.

Most ORM does serialization behind the scenes (I stand corrected here)so it could be fair to say that it wouldn't cost that much, but then again it depends on the application.

Answer 3

Personally, I use a SqlDataAdapter with DataTables. DataTables have WAY less overhead than DataSets. My entity objects only contain business rules, they aren't used to transport data across tiers.

Answer 4

You may want to think about skipping the Data-Access Library; instead, have your business objects automatically there for you, populated with data, when you need them. NHibernate.

Answer 5

I'd have to agree with Justice, not necessarily about NHibernate (altho it's a great option) I would definately look at using some sort of ORM like NHibernate, Subsonic, Linq-to-sql, llblgen or any other one of the ORMs around.

As Jeremy Miller states:

if you're writing ADO.Net code by hand, you're stealing from your employer or client.

and to that end, I'd have to recommend returning objects as opposed to datasets or datatables.

Also, if you're returning datasets, unless you strongly type each dataset, you're going to have to write a lot of "lifting" code in your library to get the values out of the datasets. With an ORM and objects all that heavy lifting is done for you.

Finally, with Linq in c# you now get much better functionality for working with collections (aggregates, grouping, sorting, filtering etc) that may have given datasets the advantage.