Let the adapter handle the connection?

  • Thread starter Thread starter jim corey
  • Start date Start date
J

jim corey

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=en&lr=&ie=UTF-8&selm=OZDN7OmwCHA.968@TK2MSFTNGP12

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
 
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.
 
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();
}
 
q@q
You should NOT be using Dispose to Close
a Connection.
Click to expand...
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/
 
Back
Top