I current use a datatable to get results from a database which I can use in my code.
However, many example on the web show using a dataset instead and accessing the tables through the collections method.
Is there any advantage, performance wise or otherwise, of using datasets or datatables as a storage method for sql results?
in 1.x there used to be things DataTables couldn't do which DataSets could (don't remember exactly what). All that was changed in 2.x. My guess is that's why a lot of examples still use DataSets. DataTables should be quicker as they are more lightweight. If you're only pulling a single resultset, its your best choice between the two.
It really depends on the sort of data you're bringing back. Since a DataSet is (in effect) just a collection of DataTable objects, you can return multiple distinct sets of data into a single, and therefore more manageable, object.
Performance-wise, you're more likely to get innefficiency from unoptimised queries than from the "wrong" choice of .NET construct. At least, that's been my experience.
One feature of the DataSet is that if you can call multiple select statements in your stored procedures, the DataSet will have one DataTable for each.
There are some optimizations you can use when filling a DataTable, such as calling BeginLoadData(), inserting the data, then calling EndLoadData(). This turns off some internal behavior within the DataTable, such as index maintenance, etc. See this article for further details.
On major difference is that DataSets can hold multiple tables and you can define relationships between those tables.
If you are only retuning a single result set though I would think a DataTable would be more optimized. I would think there has to be some overhead (granted small) to offer the functionality a DataSet does and keep track of multiple DataTables.
If you're working with web apps, you may want to consider using a DataReader:
http://aspnet.4guysfromrolla.com/articles/050405-1.aspx