closing open connections

[C Sharp beginner]

I'm sorry about this verbose posting. This is a follow-up
to my yesterday's posting. Thanks William for your reply.
I understand it is a good practice to open connections as
late as possible and close them as early as possible.
My requirement is as follows:
I'm developing a class library that will be instantiated
by a COM component. My class library contains functions
that perform lot of mathematical calculations and read a
lot of data (using SqlDataReader) from the SQL Server
database. Once loaded into the memory (just one instance
of my C# class), the functions are executed many times in
an hour. Also, the class instance will reside in memory
indefinitely unless some error condition occurs in one of
the other services in our applications world in which
case, this class instance will be unloaded from memory.

I'm required to open at least 3 connections (since I will
be using 3 simulataneous data readers)

My questions are:
1.Is it ok to open the connections on startup and leave
them open as long as the class instance is in memory?

2.Will it not be a performance hit to open and close
connections very often?

3.Which is the best place to close the connections and
how?

4. Can you give me a brief example for implementing
IDisposable interface?

I appreciate your reply.

Subject: Re: closing open connections and other resources
From: "William Ryan" <[email protected]>
Sent: 1/5/2004 12:46:19 PM




There are a lot of good reasons not to do this. Leaving
connections open
indefinitely has many potential problems, connection
pooling being the most
noticeable.
However, you could implement IDisposable and take care of
it there.
"csharpbeginner" <[email protected]>
wrote in message

My requirement is a certain database connection should be
closed only when the object is unloaded from memory. Is
the class destructor a good place to close open database
connections? Thanks in advance for your reply.

..

 

[Nicholas Paldino [.NET/C# MVP]]

C Sharp beginner,

See inline.

I'm sorry about this verbose posting. This is a follow-up
to my yesterday's posting. Thanks William for your reply.
I understand it is a good practice to open connections as
late as possible and close them as early as possible.
My requirement is as follows:
I'm developing a class library that will be instantiated
by a COM component. My class library contains functions
that perform lot of mathematical calculations and read a
lot of data (using SqlDataReader) from the SQL Server
database. Once loaded into the memory (just one instance
of my C# class), the functions are executed many times in
an hour. Also, the class instance will reside in memory
indefinitely unless some error condition occurs in one of
the other services in our applications world in which
case, this class instance will be unloaded from memory.

I'm required to open at least 3 connections (since I will
be using 3 simulataneous data readers)

Do you really need three simultaneous data readers? Are you sure that
you wouldn't be better off getting the three separate result sets one at a
time?

My questions are:
1.Is it ok to open the connections on startup and leave
them open as long as the class instance is in memory?

Absolutely not. Since you will not be using them all the time, you
should be disposing of them as soon as possible. If you keep it open, then
the connection is not able to be used in other areas (or by the server as
well), and you are just wasting resources. Open it as late as possible, and
close as soon as possible. You will get the best performance this way.

As a general rule, I do not pass database connections around to other
methods. If you do, the methods should respect the state that the
connection is in. It should open it if it is closed, and keep it open if it
is already open. When done, if it was in a closed state, then it should
close it again.

Then, in my method, I open the connection, and close when done.

2.Will it not be a performance hit to open and close
connections very often?

It will be a bigger performance hit to keep it open. Connection pooling
is most likely running in the background and will keep the connection open
if it feels that doing so would improve performance. Trust the connection
pool, it is your friend.

3.Which is the best place to close the connections and
how?

You should close the connection when you are done with it (for the
operation you are performing). Call the Close method or the Dispose method
in the IDisposable implementation.

4. Can you give me a brief example for implementing
IDisposable interface?

Check out the section of the .NET framework documentation titled
"Implementing a Dispose Method", located at (watch for line wrap):

http://msdn.microsoft.com/library/d...guide/html/cpconimplementingdisposemethod.asp

Hope this helps.