Home-grown ORM vs. DataTables?

Question

This is a simplification of the issue (there are lots of ways of doing things), but among applications that need to talk to a database I have usually seen one of two patterns:

1. Object-Relational Mapping (ORM), where (usually) each table in the database has a corresponding "row wrapper" class with public properties that match the columns in the table. Sometimes these classes also automagically retrieve related information, so that foreign key columns can instead be exposed and displayed as the related data (rather than just the PK values).
2. DataTables (and/or DataSets), where data is retrieved from the server as a DataTable and worked with in that form (even in the UI).

One of the major differences between the two approaches is that ORM allows you to reference strongly-typed fields in your code like so:

Person bob = new Person();
bob.FirstName = "Bob";
collectionPeople.Add(bob);

whereas with the DataTable approach your code would be something like:

DataRow newrow = datatablePeople.NewRow();
newrow["FirstName"] = "Bob";
datatablePeople.Rows.Add(newrow);

In this case, the ORM approach benefits from compile-time checking while the DataTable approach does not. On the other hand, the DataTable (and the DataSet) are already-written data structures that do an excellent job of representing relational data directly, so code that uses them can usually be implemented more quickly. In addition, code that uses DataTables can be easily understood and modified by others; home-grown (and often COTS) ORM systems often do extra database access "under the hood" to populate foreign keys and so forth, which can create problems for the unaware.

So which approach do you generally favor and why?

Answer 1

I used to just use a datareader to read fields onto my object using GetString, GetInt etc. but i've moved on now to a much more OO and testable approach using a Gateway to return a datatable from a query, this is then passed into a Service class which parses the table onto an object.

I never really liked ORM tools, they were always cumbersome and difficult to maintain, but i havent had a chance to play with LINQ yet so my opinion may change.

Answer 2

I favor the DataTables way, because I'm old, tired, and skeptical of fashions like Subsonic and Linq.

Beyond that, when you are working with an ORM, you are generally minimizing what you do in SQL. You don't put a lot of logic in the SQL and you don't batch up several SQL statements so as to do multiple things in one trip to the database. Therefore, you tend to go to the database more often, and that's a big performance hit.

Using Datasets, I can do something like:

select col1, col2... from table1
select col1, col2... from table2
select col1, col2... from table3

and then make ONE trip to the database to get all three DataSets, using Tables[0], Tables[1], Tables[2] to reference them. It makes a very big difference in performance.

Someday maybe the interaction with the database will be so fast that there would be no point in batching up the SQL, but that day isn't here yet. When it comes, I'll switch to ORM, but until then, I'm willing to have my code be a little uglier in exchange for performance. It's no fun for users to use a sluggish app.

Finally, I like SQL. I'm good at SQL. Real good. I don't want to spend my time figuring out how to co-erce Linq to emit the SQL that I want. It would slow down MY performance.

Answer 3

You could combine both approaches mentioned using Strongly Typed DataSets.

It's possible to add them to a Visual Studio project via "Add New Item" dialog "DataSet template" and then use visual Dataset Designer (it edits XSD file behind the scenes).

There is another article on subject.

Answer 4

I find that I'm able to develop less code and test my code better using an ORM than DataSets/DataTables. I'm currently using LINQ-to-SQL and wrapping a thin layer around the designer generated code via partial classes as my ORM. The thin layer basically allows me to add some role-based permissions and increases the testability of the code by refactoring to interfaces and using dependency injection to specify the data context (that way I can mock it up for other tests).

I've done both -- write my own ORM around DS/DT, strongly-typed DS/DT, etc. and have switched to LINQ and am not going back. I may eventually move to something like NHibernate, Entity Framework, or something else, but for now my LINQ solution offers pretty much all I need and is much simpler than my old solutions.

Answer 5

Datatable will certainly be conceptually more straight forward in working with data. And its devoid of sometimes unnatural idioms that you find in ORM. (querying a record into local memory, before updating it; joins are pointers; the key value itself is a pointer, hence, adding a record requires loading the parent record)

The big advantages for ORM are...

1) it writes the sql for you, so you dont really have to write any sql to do basic crud. Of course writing more complex statements has to be done in a less powerful sublanguage (i.e. hql)

2) The other big advantage of ORM is when you get results back, it maps it into value objects, without writing a bunch of code to map the values and handle type conversion.

If you have strong sql skills but want advantage 2 covered, i would go with ibatis

Answer 6

I would look at the advantages of data objects versus DataTables (a fancy ORM library isn't really necessary though they can be nice):

• Conceptually clean. You're forced to apply OO concepts to your data. It's easier to understand "this is a Person" versus "the Person I want is somewhere in this table".
• Enforces separation of concerns. It's tempting to tack UI context data to a DataTable - for one UI I get a Person with a primary address in the same record, for another I get a Person with credit information in the same record. When I'm working with a model, I want that model to be consistent wherever I consume it.

• Transform the data only once. In the DataTable-crunching apps I've seen, there's a lot of this scattered all over:

  if(row["col"] == DBNull.Value || string.IsNullOrEmpty(row["col"] as string)) ...

  I'd rather check that condition once when I populate the data object versus checking it everywhere the DataTable is used.

• Easier to unit test. If you reference a field in a data object that doesn't exist, you get a compile-time error. If you reference a field in a DataTable that doesn't exist, you get a run-time error.

I do believe ORM can make you lazy. For instance, there's absolutely no reason to populate a set of related data objects from individual queries in the objects if those objects are always used together. Instead, write a big and efficient query that grabs all the necessary data and then builds the data object graph. Still, if you keep its limitations in mind, it does save a lot of work.

Related: http://stackoverflow.com/questions/37378/how-to-convince-my-co-workers-not-to-use-datasets-for-enterprise-development-ne.

Answer 7

In .Net strongly typed datasets have the benefits you attribute to ORM -- the value of strong typing in the IDE and Intellisense,

The TableAdapters created in Visual Studio will write all of your CRUD SQL for you. You put your business logic in the C# (or other lang.) and not in the SQL.

I think the disconnected data model offered by datasets proves to be more efficient in practice than typical hand-coded SQL. It leads to non-chatty DB interactions where you get all of your related table data in a single query. And it has very good support for optimistic locking (providing DBConcurrency exceptions). The dataset also tracks insertions, modifications and deletions to your data, so you don't have to.

Strongly typed datasets also offer straight-forward navigation to related table data.

A good reference on DataSets is

Programming Microsoft® ADO.NET 2.0 Core Reference by David Sceppa

Publisher: Microsoft Press Pub Date: May 17, 2006 Print ISBN-10: 0-7356-2206-X Print ISBN-13: 978-0-7356-2206-7 Pages: 800

+tom

Answer 8

I used to use DataSets in early .NET days, then started using typed DataSets, over time found that the memory footprint left by DataSets are very high and it did not make sense to use unmanaged objects for every simple tasks.
Hence concluded that DTOs/POCOs are a better choice and started using NHibernate and iBatis.
My average(or below average) developers found them complex and hard to use(no pun intended).
So I made a home grown ORM XmlDataMapper that was much easier to use that the complex ORMs out there.

To integrate XmlDataMapper all you need to do is 4 little steps

1. Create a Business Entity / DTO for the tables
2. Create an XML File with the mapping information between the table and the DTO.
3. Specify the DTO and xml file in the configuration.
4. Just call the DTOConverter.Convert(dataReader) and other such methods to convert your database record to DTO / Business Entity