Tuesday, September 22, 2009

TableAdapters Are Crap!

I haven't played around that much with TableAdapters, mainly because what I *have* seen, I didn't like. The TableAdapter Wizard puts crap into my .xsd that certainly doesn't belong there, such as connection strings. My DataSet .xsd's should be DataSet schema and that is all! The wizard *totally* messed with my existing .xsd and I just don't like all the extra stuff it put in there.

I have heard people say that the TableAdapter stuff can be decoupled from the generated DataSet (which it should be, IMHO ... DataAccess stuff has no business being in a DataSet), but I didn't see any automated way to do it ... just cut/paste elsewhere manually and then of course you'd need to do that each time you made changes (I may have missed something though).

Another thing, it seems to generate code only for one DataTable ... even though my StoredProc returns many tables and my .xsd contains many tables. I may have missed something, but this is what it looks like to me. If this is the case, this is totally useless to me.

One of the problems that I see with the TableAdapter is the inflexibility. The generated class tightly-couples the DataSet to the database, and I don't believe that DataSets should be used that way. I use DataSets simply as a data transport mechanism. My DataSets know nothing about where their data comes from, nor should they.

OK, technically speaking, the DataSet class doesn't know about where its data comes from, just because I'm using a TableAdapter. But the fact the TableAdapter gets generated inside the DataSet.designer.cs means that the DataSet DLL is no longer database agnostic, and that's a very bad thing for correct client-server / SOA architecture. There is no more separation of layers when done this way. The DataAccess classes should be a totally separate layer with their own DLLs, just as the DataSet should live in their own DLLs.

My advice is to skip the TableAdapter Wizard ... it generates a lot of bloated and useless code and you could code it up yourself just as easily and more efficiently. Some of it may be useful to initially see how things work maybe, but then you should just use the DataAdapters yourself instead of using a TableAdapter which basically “wraps” the DataAdapter.  This is just my opinion, so take it for what it's worth.

UPDATE 4/18/2010: I have another related post that may be useful to readers of this post. Please check out my post: Create An Xsd.

18 comments:

  1. Bonnie,

    I totally agree, I would take it one step further and say "wizards" shouldn't be apart of production development especially if it concerns data connections and/or security.

    As a developer we should understand every aspect of a applications function and not rely on wizards. Wizards are nice to have, but as you also pointed out they sometimes come with baggage. Great Blog!

    ReplyDelete
    Replies
    1. Excellent response and couldn't agree more about ‘wizards’ (unless the functionality is internal - i.e., restoring a SQL Server database from a backup). And I also agree that this is a great blog, and what has been pointed out here is still true in 2013.

      One thing that really drives me crazy is that the TableAdapter control is pretty much a 'black box'. Yes, it has a debugger function, but oftentimes it is useless.

      Delete
    2. Thanks for appreciating my blog! Have you also read my 3-part series of blog posts on DataAccess?

      Delete
  2. Thanks for your comments Carl!!

    ReplyDelete
  3. Your the best Bonnie, couln't agree more. Thanks!

    John Grove

    ReplyDelete
  4. Hey John ... I know you agree with me on this one! I wish more people "grokked" our point of view. ;0)

    ReplyDelete
  5. I agree. I ran into all sorts of problems using TableAdapters for my project, so next time I'm not going to use them.

    ReplyDelete
  6. "The TableAdapter Wizard puts crap into my .xsd that certainly doesn't belong there, such as connection strings".
    I checked... and tested. The generated code accesses the mysettings entry for connections and you can change that at rutime. (You had me scared there, thought we were back in the vfp reports conundrum, where it kept the printer info, remember that? :) )

    ReplyDelete
  7. Suit yourself Marc ... but there's a lot of other TableAdapter crap that gets generated in the .xsd ... not just connection strings. Want to see for yourself? Take a look at what gets generated when you let it put the TableAdapter crap in there. Then use the code from my "Create An .xsd" post referenced above to create an .xsd. "My" .xsd is a whole lot cleaner.

    ReplyDelete
  8. Not arguing dear...
    I've looked at what is generated, but frankly at this stage, I don't understand most of what's there.
    Do you explain somewhere what your approach is to binding? I guess that's done manually in the form's instance load handler, right?

    ReplyDelete
  9. >>I've looked at what is generated, but frankly at this stage, I don't understand most of what's there<<

    My point exactly ... it's all useless, IMHO. As I said, compare it to what you can generate if you use my code. The .xsd that my code produces is quite simple and easy to read.

    Not sure exactly what you're asking about the DataBinding, but yes, I prefer to do it manually in code rather than setting stuff in the Property Sheet. All of my controls typically contain a DataBind() method, in which I put all the databinding for the control. So, for a Form, its DataBind() method will have all the code to DataBind each of the controls on that Form. And yes, that typically gets called from the Form's Load eventhandler.

    ReplyDelete
  10. Table adapters can be very helpful. You don't have to use them, but they can be very convenient expecially when used with bindingsources and binding navigators in good old fashioned winforms. The wizard, (what is really more a designer), doesn't put any dataaccess in the dataset. The table adapters aren't in the dataset; they're in a completely different class.

    However, as GG notes, the designer and table adapters can fall short so I was really glad to find a technique for creating a dataset from a storedproc that returns more than one result set.

    With all the new ORMs, I thought that I would never use datasets again, but I'm having to support IBM DB2 and there doesn't seem to be an affordable, up to date way of use LINQ or entities so here I am touting datasets and tableadapters.

    ReplyDelete
  11. Hi Alfetta,

    I still prefer to roll my own DataAccess with DataAdapters. No one will ever convince me to use TableAdapters. As you have discovered, TableAdapters don't always "fit the bill", so why bother with them ever? I hate mixing-and-matching different "styles" in one application. Consistency is so very important!!

    However, since I wrote this blog post, I have discovered that one of my objections to TableAdapter no longer holds true. Starting with Visual Studio 2008, Microsoft produced a workaround for separating the DataSet and the TableAdapter into two separate files (and projects). I haven't actually tried it, but I suppose I should at least point my readers to it (those who insist on using TableAdapters):

    http://msdn.microsoft.com/en-us/library/bb384586.aspx

    ReplyDelete
  12. I can't agree more. Hard-coded the connection string in xsd is really a bad idea.

    ReplyDelete
  13. I can't agree more. Hard-coded the connection string in xsd is really a bad idea.

    ReplyDelete
    Replies
    1. Yep, pretty bad idea. Not to mention all the other useless stuff that gets put into the .xsd. Thanks for reading my blog!

      Delete
  14. I absolutely agree with you. But there are others out there who choose the fastest way to deliver the results they need. Sometimes all we need is not always the best way. Forgive my poor English and thank you ...

    ReplyDelete
    Replies
    1. Hello Bayu -- I agree that some people choose "fast" over "best". In the long run, that will come back to bite them. Any serious developer should work on getting the architecture of their application(s) done first ... one part of this means developing base classes, a framework, on which all their future applications will be built. Yeah, that's a slower approach at first, but if done right then in the long run they'll be able to build apps much quicker with the framework they've put in place.

      Delete