none
Best Practice ADO.NET with an MDI Application RRS feed

  • Question

  • Hello MSDN Forums,

    I am developing an MDI Database application that is going to work with Sql Server through the System.Data.SqlClient namespaces.  Almost every Mdi Child in the application will need to connect to the database to retrieve data from one table or another once the user requests them, ie. Customers, Vendors, Inventory, etc.  I was wondering if some light could be shed on the best way to manage the SqlConnection object in this type of scenario.

    Should there only be one SqlConnection object allowed for the entire application like a Singleton theory?  Or should each Mdi Child manage a connection to the database.  The application is going to be fairly disconnected, on the form load a connection to the database would be made, pull data into a dataset and then close the connection until the user needs or request more information.

    I would think the Singleton theory would be perferred due to only having to setup a connection configuration once when the application first initializes but what I do not know is how one SqlConnection object would be shared from Mdi Parent to Mdi Child.

    Could anyone help me with this?

    Thanks,

    Jeremie Grund
    Sunday, August 16, 2009 1:45 AM

Answers

  • > Should there only be one SqlConnection object allowed for the entire application like a Singleton theory

    No.  By default, SqlConnection automatically picks connections from a managed "pool" of actual SQL Server connections regardless of how many SqlConnection objects you actually have in your program.  The recommendation is to create SqlConnection objects and Close them as soon as possible (usually right after you are done with the transaction).  When you do this the connection is not actually closed immediately.  Rather it is released to the pool.  If database access is needed soon after, it will use the same underlying SQL Server connection.  If database access is not needed soon after, the actual connection will automatically close, freeing up resources on the database server.

    This will let the pool work optimally.

    (Another thing that gets people in trouble with the singleton connection is that, in a multithreaded application, you cannot use the same SqlConnection object instance concurrently by multiple threads.  Just use the pooling... it is designed to work well that way.)
    • Marked as answer by Jeremie Grund Sunday, August 16, 2009 3:10 AM
    Sunday, August 16, 2009 2:03 AM

All replies

  • > Should there only be one SqlConnection object allowed for the entire application like a Singleton theory

    No.  By default, SqlConnection automatically picks connections from a managed "pool" of actual SQL Server connections regardless of how many SqlConnection objects you actually have in your program.  The recommendation is to create SqlConnection objects and Close them as soon as possible (usually right after you are done with the transaction).  When you do this the connection is not actually closed immediately.  Rather it is released to the pool.  If database access is needed soon after, it will use the same underlying SQL Server connection.  If database access is not needed soon after, the actual connection will automatically close, freeing up resources on the database server.

    This will let the pool work optimally.

    (Another thing that gets people in trouble with the singleton connection is that, in a multithreaded application, you cannot use the same SqlConnection object instance concurrently by multiple threads.  Just use the pooling... it is designed to work well that way.)
    • Marked as answer by Jeremie Grund Sunday, August 16, 2009 3:10 AM
    Sunday, August 16, 2009 2:03 AM
  • So then each Mdi Child form should create it's own SqlConnection object?  That would probably be easier because then all that each Mdi Child would need to know is the connection string to the database server.

    Thanks for the advice!

    Jeremie Grund
    Sunday, August 16, 2009 2:45 AM