I'm migrating from VB6 and have a question about using 'Using' and the
best way to use it.

Here is a example of a small bit of code:

dbConx("open")
Using CN
    Dim CMD As New OleDbCommand(sSQL, CN)
    Dim DR As OleDbDataReader = CMD.ExecuteReader()

    If DR.HasRows = True Then
      Dim vCID As New Collection()
      While DR.Read()
        vCID.Add(DR.GetInt32(0))
      End While
      DR.Close()
      CMD.Dispose()
      dbConx("close")
    Else
      DR.Close()
      CMD.Dispose()
      dbConx("close")
      Return False
    End If
End Using

dbConx creates an OleDbConnection CN and opens/closes it.  The purpose
of this is to return 0 or more record numbers for later use. If zero
records are returned I just exit the function with a return of false.
If records found, gather them into a collection and carry on, closing
all resources used.

In VB6 I used "set x=nothing" to clean-up.

-What gets cleaned up within a 'Using' block?
-Does my CMD and DR objects get cleaned up along with CN (so not
needing all the .close and .dispose lines?
-Any cleaner way to do this?

Thanks!

Mg,

As long as you use a form or things like that, than don't botter to much
about cleaning up.
(The dispose is in the component part of that)

VB.Net is a programming language inside the managed code group. That managed
code is special to overcome all cleaning troubles. Only unmanaged
*resources* are not cleaned up by the managed code.

In the system.data class that you show, there is not one piece of unmanaged
resource and therefore nothing of that has to be disposed by hand.

The using is quiet standard, it is using dispose consequently at the end.
That can be needed, but as I wrote not for the system.data classes.

Cor

Show quoteHide quote

Cor,

See my comments inline.

Brian

Cor Ligthert [MVP] wrote:
We all know that the GC will eventually do it for us automatically when
the finalizer runs.  But, the issue is with the timing of when that
happens.  And I'd say it's likely that there are unmanaged resources
involved somewhere in those objects.  The fact that they implement
IDisposable is a pretty good clue.

Calling Dispose (or using the 'Using' keyword) is definitely
recommended for the classes in System.Data.  If you don't then a
database connection may be held open until the GC runs and that may
take awhile.

Show quoteHide quote

Brian,

By whom is that recomended?

Cor

Show quoteHide quote

Cor,

In general, the author of the class recommends it when he/she derives
from IDisposable.  So I guess that means Microsoft recommends it.  One
notable exception is the DataSet.  IDisposable is carried along with
MarshalByValueComponent, but we're all pretty sure that a DataSet does
not hold unmanaged resources.  But, that's not what this thread is
about.  We're talking about OleDbConnection.  If you choose not to call
Dispose or Close then the connection will remain open which may lead to
a bug.  It's easy to demonstrate problems by creating a connection
object and calling Open in a loop repeatedly without closing or
disposing the object.

Brian

Cor Ligthert [MVP] wrote:
Show quoteHide quote

Brian,

Who is that author of the class, I am almost sure not the leader of the
ADONET group, as far as I remember me he has written many times that closing
is more than enough for a connection in the ADONET newsgroep.

Cor

Show quoteHide quote

Cor Ligthert [MVP] wrote:
Cor,

Well yeah, that's what I've been saying.  Dispose *will* close the
connection.  Whether you choose to call Close or Dispose it's all the
same.  The important thing to consider is that IDisposable is your clue
that it needs to be done.

Brian

See inline.

mg wrote:
Show quoteHide quoteThe 'Using' keyword will automatically generate the call to Dispose at
the end of the block.  The only thing you can say for certain is that
it affects the object specified in the 'Using' expression.  In your
case, however, it probably disposes any OleDbCommand and
OleDbDataReader objects associated with the OleDbConnection as well.
But, that's really an implementation detail of OleDbConnection.

I'd say probably, but it never hurts to wrap those with the 'Using'
keyword as well.

Well, it's not clear to me what dbConx("open") does and where CN is
created.  I usually create the OleDbConnection object inline with the
'Using' construct that way its scope is limited to just that block.

Show quoteHide quote

Brian Gideon wrote:

Brian, thanks for that.  CN is created in a function dbConx, just a
standard new OleDbConnection.  I agree, it would be good to clearly
define its scope.

And for Cor, this example is fron a console app, not a form based app.

On a similar topic, is it better to open/close a OleDbConnection
connection as quick as possible, or is it better to create a
OleDbConnection using block around a series of OleDbCommand and
OleDbDataReader objects?  By better I mean performance/resources wise.

Thanks, mg

mg,
Using "Using" is really about when to call Dispose.

When to call Dispose?

* Call Dispose when the type itself implements IDisposable

* Call Dispose when the type or one of its base classes *overrides*
Dispose(Boolean) if the class inherits from System.ComponentModel.Component
or System.ComponentModel.MarshalByValueComponent

* Do not explicitly call Dispose on classes deriving from
System.Windows.Forms.Control for instances placed on a
System.Windows.Forms.Form as Form will implicitly dispose of them when the
form is Disposed

* Call Dispose on System.Windows.Forms.Form objects when Form.ShowDialog is
used.

* Do not explicitly call Dispose on System.Windows.Forms.Form objects if
Form.Show is used as Dispose will be implicitly called when the form is
closed

* Do not explicitly call Dispose on classes deriving from
System.Web.UI.Control as it will be implicitly called as part of the normal
ASP.NET page processing

I consider the second rule controversial as it relies on using ILDASM or
Reflector to find out implementation details of a class. Its meant for
classes such as DataSet, that have an inherited Dispose, but Dispose doesn't
really do anything.

These rules are based on private discussions with other MVPs & discussions
held in the newsgroups earlier in 2005.

These rules apply to objects that you create, explicitly or implicitly.
Objects that you "own".

Disposable Objects that are passed to you as a parameter of a method (such
as the Graphics object on the Paint event) should not have their disposed
method call, as the system calls it as part of the method that raises the
event.

Objects that something else "owns" should normally be disposed of by the
"owning" object.

Based on the above rules I would rewrite your code snippet as:

        Using CN As OleDbConnection = dbConx("open")
            Using CMD As New OleDbCommand(sSQL, CN)
                Using DR As OleDbDataReader = CMD.ExecuteReader()

                    If DR.HasRows = True Then
                        Dim vCID As New Collection()
                        While DR.Read()
                            vCID.Add(DR.GetInt32(0))
                        End While
                    Else
                        Return False
                    End If
                End Using
            End Using
        End Using

Or alternatively:

        Using CN As OleDbConnection = dbConx("open"), CMD As New
OleDbCommand(sSQL, CN), DR As OleDbDataReader = CMD.ExecuteReader()

            If DR.HasRows = True Then
                Dim vCID As New Collection()
                While DR.Read()
                    vCID.Add(DR.GetInt32(0))
                End While
            Else
                Return False
            End If
        End Using



Show quoteHide quote

Jay thanks, you are a star!  I don't mind the back-and-forth between
the alpha-devs, but  was really looking for the practical, not the
theory, and you came through.

For me, my recent jump to .net from VB6 has moved me way out of my
comfort zone.  I just have to get used to not being so productive for a
while...

Thanks again!
mg

DataGridView and ComboBox
Version conflict
Data Wizard
formula for calculating sales tax (GST)
VB .NET use MailMessage
Report is mostly hidden in the report viewer
calculate date in the future
Multiple icons into 1 exe or dll
fill combobox1...
combobox with data binding