Closing Connections

[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

 

[Scott M.]

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.

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?

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.

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?

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.

 

[Cor Ligthert [MVP]]

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

 

[Cor Ligthert [MVP]]

Scott,

I forgot to look at your message before I wrote mine,

Sorry

Cor

 

[Scott M.]

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.

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.

 

[Cor Ligthert [MVP]]

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.

No if the dataadapter does not open the connection itself, than it does not
close the connection.

Cor

 

[Scott M.]

But it *does* open the connection and it *does* close the connection, Cor.

 

[Cor Ligthert [MVP]]

Scott,

Did I write something else?

Cor

But it *does* open the connection and it *does* close the connection, Cor.

 

[Scott M.]

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.

 

[Cor Ligthert [MVP]]

"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.

But that is as it acts. Otherwise you could never build a transaction
including more updates.

Cor

 

[Miha Markic [MVP C#]]

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 ;-)

 

[Cor Ligthert [MVP]]

Miha,

No probably we are not saying the same.

But it *does* open the connection and it *does* close the connection, Cor

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

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/

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.

 

[Miha Markic [MVP C#]]

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/

Miha,

No probably we are not saying the same.

But it *does* open the connection and it *does* close the connection, Cor

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

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/

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.

 

[Scott M.]

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

 

[Cor Ligthert [MVP]]

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

 

[Miha Markic [MVP C#]]

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

 

[Scott M.]

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

-Scott

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/

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