Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

connection--open in a(), close in b()--danger?

Here's what I'm doing:

Declare and instanciate a connection object with class level scope, but open and close as I need it in functions within the class.

a() fills a datareader, the ouput of which is going to require some complex formatting, thus I want to move the formatting to its own function (b()).  Therefore I have also declared the reader with class level scope so I don't have to pass it from a() to format it in b().  What makes me nervous though is I cannot execute read on the reader in b() if I've already closed the connection in a().  So what I'm doing is opening the con in a(), then closing it in b().  b() always gets called after successful execution of a(), so theoritically the con should always get closed, but I don't have a lot of real world programming experience so I wonder if this is considered risky or just bad form and if so, what might be an elegant way to handle it?

Friday, February 6, 2004

Short answer: yes, this is bad form.

If you know that you must release/close something or the app will be an inconsistent state, you should put it inside a try/finally, or a using clause in C# (which is really the same thing).

using (Connection conn = new Connection("blah;blah;blah"))
  do stuff.


Dim conn As Connection
  conn = New Connection("blah;blah;blah;")
  do stuff...
End Try

But in your situtation, I would take a closer look at why you are using those class-level fields. Things like connection objects and DataReaders become unhappy about being called by different threads, which is really easy to accidentally do with static fields.

Since I don't know what your program does, I can't judge if this is a problem for you. But as I understand it, best practive is to not put things like connections and datareaders in static scope.


Aaron Boodman
Friday, February 6, 2004

> best practive is to not put things like connections and datareaders in static scope.

Cool.  That answers the larger question, which renders the my specific question moot.  I decided to create the reader in a(), pass it to b() for the formating, then return execution to a() to ensure close.

Once question though about putting connections in static scope: is there more or less overhead to creating and disposing a new instance of a connection in each of 2-3 functions inside a class compared to creating one instance with static scope and opening and closing it throughout?

Thanks for the help.

Friday, February 6, 2004

ADO.Net pools the connection objects automatically, so you don't need to worry much about the overhead of instantiation since you aren't *really* instanciating a new connection each time you say new Connection().

There is some overhead associated with open/close, so you shouldn't call it over and over again in a loop. For example, when you call Update( ) on a DataAdapter and pass it a DataTable, it internally opens the connection once and does each update/insert/delete for each row as appropriate, then closes the connection.

So for your case, if it's not too inconvenient to arrange the queries so that you can do them one after another, it would probably be faster to do it within one connection.

On the other hand, unless it's a large app, you aren't going to notice the difference one way or another :). Good questions though.

more info:

Aaron Boodman
Friday, February 6, 2004

Great!  Thanks again for all the answers and the link to best practices.  All were very helpful.

Friday, February 6, 2004

*  Recent Topics

*  Fog Creek Home