cnn.close() and cnn.dispose()

[Sean]

Hi all,

I encountered one problem when using ADO .NET with VB .NET in VS Studio
.NET Professional.

I'd like to know whether this behavior is normal or it is a bug in ADO .NET
for SqlDataAdapter.

I open a connection then assign the connection to SqlDataAdapter then I
close the connection object and dispose it. I found that I still can fill
the dataset and update it. (cn.close, cn.dispose)

If I close the connection and didn't dispose the object, I can fill the
dataset and update it too. (cn.close)

But if I didn't close it and dispose it, (cn.dispose) i can't fill and
update the database.

I'd like to know what is the difference between cn.close and cn.dispose?
closing the connection does required to dispose it as well?
Why normal example given is dispose the connection without closing
the connection?

Below is the code snippet:-

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click
Dim cn As New SqlConnection("Initial Catalog=GMS;Data
Source=APPLSVR\MSDE;Integrated Security=sspi")
Dim cmd As SqlCommand

cn.Open()

Dim cb As SqlCommandBuilder
adap = New SqlDataAdapter("Select * from TUSER where UserId='U0002'",
cn)
cb = New SqlCommandBuilder(adap)

Dim dataset As New DataSet()

adap.Fill(dataset)
'----problem here ---
cn.Close()
cn.Dispose()
cn = Nothing

datatable = dataset.Tables(0)

datarow = datatable.Rows(0)
TextBox1.Text = datarow(1)

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim cn As New SqlConnection("Initial Catalog=GMS;Data
Source=APPLSVR;Integrated Security=sspi")
cn.Open()
datarow(1) = "Tan Bee Kim"
TextBox2.Text = TextBox2.Text & datarow.RowState.ToString & vbCrLf

Try
TextBox2.Text = TextBox2.Text & "" & vbCrLf
adap.Update(datatable)
Catch ex As DBConcurrencyException
Throw ex
End Try
cn.Close()
End Sub

[Sander]

It has not strange that you can open and close a connection.
conn.open()
conn.close()
conn.open()
conn.close()

Dispose really throws away the connection back to the pool.
In my code i use both methods (close & dispose).

"Sean" <vent...@tm.net.my> wrote in news:OKLigU25BHA.368@tkmsftngp05:

[Andy Baron]

I'm not sure which part of your test you though indicated a bug. But
the documented behavior of the DataAdapter is that if the connection
is closed, the DataAdapter will open it before performing an update
(or a fill) and then close it afterwards.

In general, it is considered safest to call an object's Dispose method
whenever that method is provided. But I haven't seen anything to
indicate a specific reason to do so with connection objects, rather
than just calling close, since both methods return the connection to
the pool.

-- Andy

[Angel Saenz-Badillos]

Sean,

After disposing a connection you can no longer use it since it no
longer has a ConnectionString, it has been freed. You should dispose
an object when you truly do not want to use it again.

That said I have not been able to repro your issue with being able to
use the adapter after calling close and dispose on the connection.
Sure the dataset is still valid, but trying to update it fails as
expected.
Hope this helps
Angel
This posting is provided "AS IS", with no warranties, and confers no
rights.

"Sean" <vent...@tm.net.my> wrote in message news:<OKLigU25BHA.368@tkmsftngp05>...