Dataset vs. Returning a Datatable

Question

I have created alot of stored procedures in my application that return the result of a query in a datatable object. I never use a dataset. Why would I ever use a dataset over simply returning the result of a query as a datatable object?

Answer 1

Neither nor. I always return strong typed objects. The usage of DataSets or DataTables in general indiate a programmer not having understood the usage of object orientation. It is totally outdate technology.

Look at more modern approaches to handle data. Stuff developped in the last 20 years (which is how long O/R mappers are around).

Answer 2

A DataSet can hold more than one DataTable. So the main reason would be if you wrote a stored proc that returned multiple result sets.

An even better way is to use an ORM like NHibernate and return model objects instead of DataTable.

Answer 3

If you are still getting to grips with ADO.NET, you should ideally look at getting your query results into DataReaders rather than DataTables. They are a lot more efficient. If I was using ADO.NET and wanted to return multiple rcordsets from a procedure, I'd use the NextResult() method of the DataReader to go through them.

As mpenrow points out, Datasets are containers for DataTables, and can hold multiple tables. One reason you would use a Dataset is to create relations between DataTables if, for example, you want to display Master/Child records eg http://www.mikesdotnetting.com/Article/57/Displaying-One-To-Many-Relationships-with-Nested-Repeaters.

As others have said, ORMs such as Linq To Sql, Entity Framework or nHibernate are the way to go eventually. They all use DataReaders internally to populate model objects.