DataTables vs IEnumerable<T>

Question

I'm having a debate with another programmer I work with.

For a database return type, are there any significant memory usage or performance differences, or other cons which should make someone avoid using the DataSets and DataTables and favour types which implement IEnumerable<T>... or vice versa

I prefer returning types which implementIEnumerable<T> (List<T>, T[] etc) because it's more lightweight, strongly typed to the object when accessing properties, allows richer information about the underlying type etc. They do take more time to set up though when manually using the data reader.

Is the only reason to use DataTables these day just lazyness?

Solution 1

DataTables are definitely much heavier than Lists, both in memory requirements, and in processor time spent creating them / filling them up.
Using a DataReader is considerable faster (although more verbose) than using DataTables (I'm assuming you're using a DataAdapter to fill them).

That said... Unless this is in some place where it really matters, you're probably fine either way, and both methods will be fast enough, so just go with whatever is more comfortable in each case. (Sometimes you want to fill them up with little code, sometimes you want to read them with little code)

I myself tend to only use DataTables when I'm binding to a GridView, or when I need more than one resultset active at the same time.

Solution 2

Another advantage to using the System.Collections classes is that you get better sorting and searching options. I don't know of any reasonable way to alter the way a DataTable sorts or searches; with the collection classes you just have your class implement IComparable or IEquatable and you can completely customize how List.Sort and List.Contains work.

Also with lists you don't have to worry about DBNull, which has tripped me up on more than one occasion because I was expecting null and got DBNull.

Solution 3

I also like the fact with IEnumerable<T> that you can enhance the underlying type of the collection with methods and properties which makes implementation far more elegant, and the code more maintainable. For example the FullName property. You can also add extension methods to the class if it is out of your control.

public class SomeUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get { return String.Format("{0} {1}", FirstName, LastName); } }
}

Solution 4

Using DataTables directly means tying yourself to the underlying data source and how it is laid out. This is not good from a maintainability point of view. If all your view needs is a list of some objects, that's all you should be giving it.

View more solutions

Comments

[nullException]

platform/language? i guess that DataTable is a platform-specific structure; but making it explicit makes it easier to answer

[CRice]

Sure, the platform is the .Net Framework (c# or vb) 2.0+

[CRice]

I don't think I have used a DataTable since 2009, they suck

[Learning-Overthinker-Confused]

Is it possible to populate T[] by doing "select * from table" using ado.net??

[CRice]

@Learning-Overthinker-Confused Yes but you need to call SqlCommand.ExecuteReader and read through the records with IDataReader

[Learning-Overthinker-Confused]

But T[] can hold records like datatable with columns and values?

[CRice]

@Learning-Overthinker-Confused Yes you make a new class with properties to match the result of the query. So you get a collection of your new type as the result - and no datatable.

[Learning-Overthinker-Confused]

@CRice Do we have some inbuilt library to do this ?

[CRice]

@Learning-Overthinker-Confused If you aren't forced to use ado, then have a look at dapper github.com/StackExchange/Dapper

[Learning-Overthinker-Confused]

Let us continue this discussion in chat.

[Concrete Gannet]

Use a DataView for sorting and searching.