How Does TableAdapter.Update(DataTable) Work Using Existing Stored Procedures? RRS feed

  • Question

  • I have created a typed DataSet using existing stored procedures to update the database according to the changes I've made.

    Now I want to save all the changes the user made to the DataSet's DataTable's rows using the TableAdapter's Update(DataTable) method.

    But what does it actually do when I'm using existing stored procedures to update the database? The help file only provides information about SELECT, INSERT, UPDATE statements being executed but nothing about stored procedures being called....

    Any help is appreciated!

    Friday, July 30, 2010 3:35 PM

All replies

  • You'll  have better control over the process if you don't use TableAdapters, but use DataAdapters instead. TableAdapters are frustrating and confusing and I have never liked them. They are, in effect, wrappers around the DataAdapter which actually does the work, so you're actually better off just using the DataAdapter to begin with. Check out my 3-part series on Data Access for some ideas of making it all a bit simpler by not using TableAdapters. You may, or may not, want to re-work your existing app, but at least keep some of this in mind for any future work you do:

    Each post adds extra complexity to the Data Access classes, but more flexiblity. However, even just the information in the first post is enough to get you going and is a great starting point for those wishing to have more control over their Data Access (and to move away from using those confounded TableAdapters) In the first post is a link to my rant against TableAdapters.  ;0)

    ~~Bonnie Berent [C# MVP]
    Saturday, July 31, 2010 4:39 AM
  • The TableAdapter class has the properties InsertCommandUpdateCommand, and DeleteCommand commands associated with the TableAdapter.Update method. This means that calling a TableAdapter's Update method executes the statements created in these properties.

    So what you need is to change the InsertCommand, UpdateCommand and DeleteCommand from which they are originally generated - change them to your custom commands (Stored procedures or your queries).

    Ali Hamdar (
    Sunday, August 1, 2010 1:07 AM
  • Thanks, Bonnie & Ali, for your replies.

    My first intention to use typed datasets was to have an O/R kind of data manipulation layer. My second intention was to have things go easy.

    When using typed DataSets, VS generates TableAdapters for me. I fear getting into DataAdapters would mean additional effort for me, losing one of the two advantages of typed DataSets, wouldn't I?

    Since I have written the stored procedures already, would be using standard DataSets and a custom O/R layer result in the same effort for me? What would you think?

    Monday, August 2, 2010 9:47 AM
  • First, as far as VS generating the TableAdapters for you,  you can get around that. The TableAdapter is pretty much just a wrapper around the original DataAdapter class. I do not believe in using TableAdapters. They are not a "best practice" in my opinion. You can have Typed DataSets without using TableAdapters, but since VS2005, Microsoft has made it more difficult to do (some extra steps involved).

    I've written a blog post about TableAdapters, ... remove the '-' in the word 'c-r-a-p' ... it get's censored here if I spell it out.

    Here's an excerpt from that blog post:

    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.

    That post also contains a link to another of my blog posts ( about how to go about creating an .xsd and generating the Typed DataSet without all the TableAdapter junk getting put into it.

    Now, granted, if you don't use TableAdapters, there are extra things you need to take care of in a DataAccess class (as I mention in my 3-part series) for using the DataAdapter instead, but you are in better control and don't run into the problems that are inherent with TableAdapters.

    Just my 2 cents ....

    As far as using Typed DataSets versus using an O/R layer with standard DataSets ... regardless of which way you go, you still need a separate "business" layer anyway. I still like, and use, Typed DataSets.

    ~~Bonnie Berent [C# MVP]
    Monday, August 2, 2010 3:59 PM
  • Hi BetterToday,

    I'm writing to follow up the post. Does the BonnieB and Ali Hamdar's suggestion work?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, August 4, 2010 3:29 PM
  • Hello Alex,

    thanks for your e-mail.

    I must admit that I refrained from using typed DataSets in the end. It seemed too much ado to me.

    At my customer's I'm working with VS 2005. Particularly the XSD designer was very awkward to use. It always created nullable function arguments and I had to manually wade through all the generated functions over and over again to set each function argument property to Allow null: false .

    Best wishes,
    Axel Dahmen

    Saturday, August 7, 2010 11:08 AM