none
ADO.net - what are the constaints on database connectivity for use of DataSet and DataAdapter? RRS feed

  • Question

  • Hi,

    Re ADO.net, I get the concept generally of DataSet and DataAdapter. What's not clear if I may ask is:

    Q1 - What constraints are there on whether the database connection need to remain open or not? Can I close the connection but still work with DataSet? If so what needs to be in place to perform an update? Does the DataAdapter need to retain an active database connection?

    Q2 - More generally if I wanted to, during the course of a user using a WinForms application was to have the user:

    1. Get initial data available in database (e.g. readin DataSet using DataAdapter), and then at some point of time later
    2. Add a new row to the database, and have it appear in the actual database (ie saved)
    3. Add another record (but just keep in memory/dataset)
    4. Some time later then SAVE the new row added from [3]

    What would be the DataSet / DataAdapter / Connection things that one would need to do here? Would you have the database connection closed in-between all steps under the guidelines of minimizing keeping the database open?

    Any sample code that shows carrying out the 4 steps above would be nice...(a bonus)

     

    Thanks

    Monday, October 12, 2009 2:20 PM

Answers

All replies

  • No, you do not need to remain connected to use DataSets. The DataSet, once filled by the DataAdapter in your DataAccess layer, can be passed around to your Business layer and to your Presentation layer. Once changes have been made to the DataSet in your Presentation layer and you're ready to save it back to the database you can pass the DataSet back to your DataAccess layer for it to save the changes back to the database.

    I have a written two posts in my blog which covers this, take a peek and let me know if you have any questions.

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by callagga Tuesday, October 13, 2009 7:17 AM
    Tuesday, October 13, 2009 12:28 AM
  • thanks Bonnie - By the way, are you supposed to call Dispose on the SqlDataAdapter? If yes, would you normally leave this non-disposed across the boundaries you are closing and then re-opening the database connection? or does this not really matter? (i.e. is the database connection by far the more important resource to worry about re closing/reopening)
    Tuesday, October 13, 2009 1:11 AM
  • My applications utilize web services, so every call to get data or save data goes through a web service to the backend data access classes. Consequently, the DataAdapters (and database connections) are constantly being recreated (and once they go out of scope, I rely on the garbage collector to take care of stuff). But, that is not a problem with web services and SQL Server, as it utilizes connection pooling ... a simplistic way of looking at that is to think of it as old connections being re-used automatically by the server. Therefore, you really do not take a "hit" at the constant opening/closing.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, October 13, 2009 4:38 AM