142

I currently 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 table(s) through the collections method.

Is there any advantage, performance wise or otherwise, of using DataSets or DataTables as a storage method for SQL results?

Hossein Narimani Rad
  • 31,361
  • 18
  • 86
  • 116
GateKiller
  • 74,180
  • 73
  • 171
  • 204
  • 1
    If you're working with web apps, you may want to consider using a DataReader: http://aspnet.4guysfromrolla.com/articles/050405-1.aspx http://aspnet.4guysfromrolla.com/articles/051805-1.aspx http://www.devx.com/vb2themax/Article/19887/1954?pf=true – Chris Burgess Sep 15 '08 at 13:38
  • Ultimately, DataTable and DataSet were useful transitions from ADO RecordSet ... back in 2002. You should not bake them your primary data metaphor these days. – Marc Gravell Jul 12 '20 at 08:42
  • @MarcGravell What would you suggest as a primary data metaphor these days? – m_a_s Jul 31 '20 at 15:53
  • @m_a_s explicit t - classes, typically; perhaps "records" in c# vNext – Marc Gravell Jul 31 '20 at 22:52

7 Answers7

103

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 inefficiency from unoptimized queries than from the "wrong" choice of .NET construct. At least, that's been my experience.

Hossein Narimani Rad
  • 31,361
  • 18
  • 86
  • 116
ZombieSheep
  • 29,603
  • 12
  • 67
  • 114
34

One major difference is that DataSets can hold multiple tables and you can define relationships between those tables.

If you are only returning 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.

Joshua Hudson
  • 2,187
  • 2
  • 20
  • 24
9

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.

Karl Seguin
  • 21,574
  • 5
  • 44
  • 49
  • 4
    AFAIK one big one was that a DataTable couldn't be serialized and couldn't be returned as a result from a WebService. – Martin Clarke Jul 05 '09 at 00:17
6

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.

Shawn
  • 19,465
  • 20
  • 98
  • 152
  • You can also run multiple SQL select queries in one SQLCommand and access each resultset in dataset.Tables[i] – Jan Dec 08 '17 at 10:31
3

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.

tbreffni
  • 5,082
  • 5
  • 31
  • 30
1

When you are only dealing with a single table anyway, the biggest practical difference I have found is that DataSet has a "HasChanges" method but DataTable does not. Both have a "GetChanges" however, so you can use that and test for null.

MickeyfAgain_BeforeExitOfSO
  • 4,112
  • 5
  • 26
  • 27
0

A DataTable object represents tabular data as an in-memory, tabular cache of rows, columns, and constraints. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects.

Nischal Tyagi
  • 199
  • 1
  • 10