[C#] Connect to DB in constructor



  • Is it generally acceptable to connect to a database in your constructor?


    Background: DB queries are 'expensive', and it might constitute a WTF if most programmers would not expect a constructor to do this kind of work.



  • <FONT face=Tahoma>Although I would normally do that in a separate method (so you would connect only when you really need to), I think it would be ok as long as you fetch the data and close the connection immediately (still within the constructor). You wouldn't want to open a connection for a long time.

    But still, it depends on what you really want to achieve.

    Just my 2¢. I would also like to know what the others think.



    </FONT>


  • ♿ (Parody)

    Are you using ADO.NET?

    ADO.NET has a very smart connection pooling system, meaning that you should open connections right before you need them and close them right after you're done. Let it take care of the "expensive" connections ... don't try to "save" by implementing your own connection persistence or pooling, you'll only make it worse.



  • At least in Oracle, making the connection is relatively slow and expensive. You wouldn't want to do that repeatedly, unless you have some kind of transparent connection pooling like the one Alex described.



  • It does really depend on the application.  If it is a thick client, you may want to consider a factory class that explicitly creates your connection objects.  Without knowing how your planning on using the connections, I think most of us will just be shooting in the dark.  I'm sure you'll get far better responses, if you give us some background on the type of application, what your trying to do, and the general outline of your classes.

    Larry



  • @ammoQ said:

    At least in Oracle, making the connection is relatively slow and expensive. You wouldn't want to do that repeatedly, unless you have some kind of transparent connection pooling like the one Alex described.

    That's true of SQL server too.  Still, best practice in .Net is to never hold a connection open unneccesarily.  Try this someday -- repeatedly connect and disconnect in a method while monitoring the database with whatever tool the database engine uses.  You'll see that ADO.Net is smart enough to actually not disconnect when you call Close so it doesn't have to reconnect when you call Open.  Viola, expensive operation avoided.

    Then try this -- write a method that creates two connections, holds them opened and executes a statement on each.  Then write a method that does the same but doesn't hold them open.  You'll see that in the second case, only one connection will actually be opened -- ADO.Net will simply reuse it wherever it is appropriate.

    It would be VERY difficult for you to build a connection management strategy that works better than the stock stuff in ADO.Net.  Simply follow the rule "only hold a connection open as long as you have to" and everything will work out wonderfully.



  • @jsmith said:

    @ammoQ said:

    At least in Oracle, making the connection is relatively slow and expensive. You wouldn't want to do that repeatedly, unless you have some kind of transparent connection pooling like the one Alex described.

    That's true of SQL server too.  Still, best practice in .Net is to never hold a connection open unneccesarily.  Try this someday -- repeatedly connect and disconnect in a method while monitoring the database with whatever tool the database engine uses.  You'll see that ADO.Net is smart enough to actually not disconnect when you call Close so it doesn't have to reconnect when you call Open.  Viola, expensive operation avoided.

    Then try this -- write a method that creates two connections, holds them opened and executes a statement on each.  Then write a method that does the same but doesn't hold them open.  You'll see that in the second case, only one connection will actually be opened -- ADO.Net will simply reuse it wherever it is appropriate.

    It would be VERY difficult for you to build a connection management strategy that works better than the stock stuff in ADO.Net.  Simply follow the rule "only hold a connection open as long as you have to" and everything will work out wonderfully.



    Nice feature, but it seems (like every other kind of connection pooling) problematic with Oracle if the program uses statefull stored packages.

Log in to reply