Closing Connections

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I am confused on whether a connection is open or closed.

I was told you didn't have to close a connection if you were binding to
DataGrids, DataReaders, DropDowns etc - that they would close them
themselves.

Is this the same for both the DataAdapters and DataReaders?

I was told the DataAdapters would leave the connection as it found it. If
the connection was open, it would leave it open and if was closed it would
close it.

What about if in the middle of binding to the DataGrid it got an error?
Would it close the connection or do you need to close it yourself?

Thanks,

Tom
 
tshad said:
I am confused on whether a connection is open or closed.

I was told you didn't have to close a connection if you were binding to
DataGrids, DataReaders, DropDowns etc - that they would close them
themselves.
Click to expand...

DataGrids, DataRepeaters, DropDowns, etc. have no knowledge of your database
whatsoever. They do not automatically close your connection. These
controls are bound to a DataSource, which is most likely a DataSet or
DataTable. In turn, the DataSet or DataTable are disconnected objects that,
themselves, have no direct relationship with your connection.
Is this the same for both the DataAdapters and DataReaders?
Click to expand...

DataAdapters and DataReaders are a different story, as they are connected
data objects. DataAdapters will automatically Open and Close their
underlying connections. DataReaders will only automatically close your
connection if you set up the DataReader to do so and only then if you Close
your DataReader.
I was told the DataAdapters would leave the connection as it found it. If
the connection was open, it would leave it open and if was closed it would
close it.
Click to expand...

Whoever is telling you these things is wrong and has very little
understanding of what these objects are and how they work.
What about if in the middle of binding to the DataGrid it got an error?
Would it close the connection or do you need to close it yourself?
Click to expand...

This is why we have Try...Catch...End Try statements. With any database
operation the code that *could* fail should be in the Try and the close
calls should be in the Finally.

Try
'Do the connecting and binding here

Catch ex As Exception
'Deal with your exceptions as needed

Finally
'Explicitly close DataReaders and Connections here

End Try

Also, calling the Close() method of any ADO.NET object that exposes an
Open() method will NOT cause an exception if that object is already closed,
so you can't hurt yourself by calling close more than once. So, in short,
it is a good practice to call Close on anything that gets opened.
 
Tom,

I don't know where you got that information from opening and closing while
using Binding.

The dataadapter is a class that does a lot of handling. Including Open a
connection as that is not open and close it than again. If it is alreayd
open, it does not open it again, but keep in mind as well not close it.

For the rest do I know not other classes which do that (the tableadapter but
that is an inherited dataadapter).

Are you maybe confused with using "using" what is a command that opens and
disposes automaticly.
(Not in VB 2002/2003).

I hope this helps,

Cor
 
The dataadapter is a class that does a lot of handling. Including Open a
connection as that is not open and close it than again. If it is alreayd
open, it does not open it again, but keep in mind as well not close it.
Click to expand...

Are you saying that a DataAdapter will open the conneciton but NOT close it?
If that is the case, I disagree. DataAdapters open AND close their
underlying connections.
 
Scott M. said:
Are you saying that a DataAdapter will open the conneciton but NOT close
it? If that is the case, I disagree. DataAdapters open AND close their
underlying connections.
Click to expand...
No if the dataadapter does not open the connection itself, than it does not
close the connection.

Cor
 
But then you wrote:

"If it is alreayd open, it does not open it again, but keep in mind as well
not close it."

And this is what I'm commenting on.
 
"If it is alreayd open, it does not open it again, but keep in mind as
well not close it."

And this is what I'm commenting on.
Click to expand...
But that is as it acts. Otherwise you could never build a transaction
including more updates.

Cor
 
Hi Scott,

I think Cor's english is not perfect and that's why you misunderstood him.
I think he is saying the same, you just need Cor's to English dictionary ;-)
 
Miha,

No probably we are not saying the same.
But it *does* open the connection and it *does* close the connection, Cor
Click to expand...
This can mean: it does *forever* open the connection and it does *forever*
close the connection, Cor

However you interrupted it now in a way which had nothing to do with the
discussion and did add absolute nothing to that. If you think that my
sentence is in English not perfect (beside the obvious typo). Than please
tell what it has to be.

Cor

Miha Markic said:
Hi Scott,

I think Cor's english is not perfect and that's why you misunderstood him.
I think he is saying the same, you just need Cor's to English dictionary
;-)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


Scott M. said:
But then you wrote:

"If it is alreayd open, it does not open it again, but keep in mind as
well not close it."

And this is what I'm commenting on.
Click to expand...
Click to expand...
 
bool isConnectionOpened = "is connection opened";
if (!isConnectionOpened)
Connection.Open();
try
{
Fill(...)... // actuall fill invocation
}
finally
{
if (!isConnectionOpen)
Connection.Close();
}

Here, un universal language of what Fill method does regarding the
connection ;-)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
Miha,

No probably we are not saying the same.
But it *does* open the connection and it *does* close the connection, Cor
Click to expand...
This can mean: it does *forever* open the connection and it does *forever*
close the connection, Cor

However you interrupted it now in a way which had nothing to do with the
discussion and did add absolute nothing to that. If you think that my
sentence is in English not perfect (beside the obvious typo). Than please
tell what it has to be.

Cor

Miha Markic said:
Hi Scott,

I think Cor's english is not perfect and that's why you misunderstood
him.
I think he is saying the same, you just need Cor's to English dictionary
;-)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


Scott M. said:
But then you wrote:

"If it is alreayd open, it does not open it again, but keep in mind as
well not close it."

And this is what I'm commenting on.
Click to expand...
Click to expand...
Click to expand...
 
Scott,

It is as is written on the by you showed page as.

Thus, the SqlDataAdapter always leaves the connection in the same state it
took it as. Pro ADO.NET 2.0 (Page 190).

So if it is already open, it gives it back open and will not close it.

That is exactly as the code that Miha made from the text that I had written.
(Although I know that Miha knows this, he has as well stated this often in
this newsgroup).

Cor
 
I stand corrected. My apologies, Cor. There is a lot of incorrect
documentation out there.

-Scott

Miha Markic said:
No, no, Cor is right. But hey, why take our word - try it for yourself.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Scott M. said:
Cor, a DataAdapter will always close the connection it is configured to
use after it has executed its appropriate commands. It does not matter
if the connection was already open prior to the DataAdapter's code
executing.

http://davidhayden.com/blog/dave/archive/2005/11/04/2555.aspx
Click to expand...
Click to expand...
 
Back
Top