Let the adapter handle the connection?

I inherited some code that looks like this:

mConnection = New OdbcConnection(GetConnectionString())

mConnection.Open()
mAdapter.SelectCommand.Connection = mConnection
'Can execute reader or execute nonquery


mAdapter.Fill(mDataSet, mTable)
...
mConnection.Dispose()

I've just read here that the adapter will handle the opening and
closing of the connections.

http://groups.google.com/groups?hl=...%40TK2MSFTNGP12

My assumption is that the code above is still just opening one
connection, so that's there no real need to change it.
Is this correct?

TIA,
Jim

Jim,

This is a Miha subject, however I do it this time.

When you want to fill one datatable, you can let the adapter do the job
When you want to fill more datatables at once (one by one) you can better do
the job yourself.

When you have opened the connection by code, you should close it as well,
because than the dataadapter does not do that for you. When it is not opened
it opens it for you and closes it.

I hope I made it clear and otherwise will Miha surelly add something,

:-)

Cor
"jim corey" <jhcorey@yahoo.com>
> I inherited some code that looks like this:
>
> mConnection = New OdbcConnection(GetConnectionString())
>
> mConnection.Open()
> mAdapter.SelectCommand.Connection = mConnection
> 'Can execute reader or execute nonquery
>
>
> mAdapter.Fill(mDataSet, mTable)
> ...
> mConnection.Dispose()
>
> I've just read here that the adapter will handle the opening and
> closing of the connections.
>
>
http://groups.google.com/groups?hl=...%40TK2MSFTNGP12
>
> My assumption is that the code above is still just opening one
> connection, so that's there no real need to change it.
> Is this correct?
>
> TIA,
> Jim

The adapter will close and open the connection if the connection starts out
as closed, otherwise it will leave it open.

So it is up to you, if you want to manually open it and close it yourself.
If you are going to be doing other work with the database, then you should
open it yourself, do all your work (including getting data through the
adapter), then close it. If all you are doing is getting data via the
adapter, then for brevity, you may let it deal with opening and closing the
connection. But in the end, this doesn't matter, it's just a matter of your
preference.

"jim corey" <jhcorey@yahoo.com> wrote in message
news:1c4f8dcf.0409130557.63d57439@posting.google.com...
> I inherited some code that looks like this:
>
> mConnection = New OdbcConnection(GetConnectionString())
>
> mConnection.Open()
> mAdapter.SelectCommand.Connection = mConnection
> 'Can execute reader or execute nonquery
>
>
> mAdapter.Fill(mDataSet, mTable)
> ...
> mConnection.Dispose()
>
> I've just read here that the adapter will handle the opening and
> closing of the connections.
>
>
http://groups.google.com/groups?hl=...%40TK2MSFTNGP12
>
> My assumption is that the code above is still just opening one
> connection, so that's there no real need to change it.
> Is this correct?
>
> TIA,
> Jim

You should NOT be using Dispose to Close
a Connection.

Structure your code this way:

try
{
mConnection = New OdbcConnection(GetConnectionString())
mConnection.Open()
mAdapter.SelectCommand.Connection = mConnection
'Can execute reader or execute nonquery
mAdapter.Fill(mDataSet, mTable)
}
catch (Exception e)
{
}
finally
{
mConnection.Close();
}

jim corey wrote:

> I inherited some code that looks like this:
>
> mConnection = New OdbcConnection(GetConnectionString())
>
> mConnection.Open()
> mAdapter.SelectCommand.Connection = mConnection
> 'Can execute reader or execute nonquery
>
>
> mAdapter.Fill(mDataSet, mTable)
> ...
> mConnection.Dispose()
>
> I've just read here that the adapter will handle the opening and
> closing of the connections.
>
> http://groups.google.com/groups?hl=...%40TK2MSFTNGP12
>
> My assumption is that the code above is still just opening one
> connection, so that's there no real need to change it.
> Is this correct?
>
> TIA,
> Jim
>

q@q

> You should NOT be using Dispose to Close
> a Connection.
>
One of the exceptions to use dispose is the connection, read for that the
messages from Angel Saenz-Badillos in this newsgroup.

Cor

There is actually a very important difference between the code that he is
showing and relying on the dataset to open and close the connection for you.
In the code below connection dispose is NOT guaranteed and you will leak a
connection _Every single time_ your adapter fill throws an exception. This
is very bad, please take a look at my blog on leaking connections.

When you rely on the adapter fill method to open and close the connection
for you connection.dispose is _guaranteed_ so there will be no leaks.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




"Marina" <someone@nospam.com> wrote in message
news:#pFE2DbmEHA.2948@TK2MSFTNGP11.phx.gbl...
> The adapter will close and open the connection if the connection starts
out
> as closed, otherwise it will leave it open.
>
> So it is up to you, if you want to manually open it and close it yourself.
> If you are going to be doing other work with the database, then you should
> open it yourself, do all your work (including getting data through the
> adapter), then close it. If all you are doing is getting data via the
> adapter, then for brevity, you may let it deal with opening and closing
the
> connection. But in the end, this doesn't matter, it's just a matter of
your
> preference.
>
> "jim corey" <jhcorey@yahoo.com> wrote in message
> news:1c4f8dcf.0409130557.63d57439@posting.google.com...
> > I inherited some code that looks like this:
> >
> > mConnection = New OdbcConnection(GetConnectionString())
> >
> > mConnection.Open()
> > mAdapter.SelectCommand.Connection = mConnection
> > 'Can execute reader or execute nonquery
> >
> >
> > mAdapter.Fill(mDataSet, mTable)
> > ...
> > mConnection.Dispose()
> >
> > I've just read here that the adapter will handle the opening and
> > closing of the connections.
> >
> >
>
http://groups.google.com/groups?hl=...%40TK2MSFTNGP12
> >
> > My assumption is that the code above is still just opening one
> > connection, so that's there no real need to change it.
> > Is this correct?
> >
> > TIA,
> > Jim
>
>