ONE connection to the database vs. disconnect ?
jeff
MSDE. I have the connection opened and closed for each query which can be
numerous. Sometimes a hundred connections are sleeping in EM activity.
VB.NET will GC when it feels like it. Datareaders have to opened on
individual connections, can't have two on one connection. Many datasets can
be opened on one connection. The problem is the governor on msde. The way
I've architected the app has much to do with this I think. Can't I use ONE
connection to the database, and run every query as needed to help alleviate
the governor kicking in?
Thanks,
Jeff
ozone@yahoo.com Jed Ozone
Two things. Are you creating a new Connection object with each query? If
you are closing the connection and letting the object fall out of scope, you
should really do a Dispose() on the object first to free up resources (and I
believe this will kill the database connection).
Actually, it's not totally true that DataReaders have to open on individual
connections. You can't have 2 active DataReaders on 1 connection, but if
you queue up your database access, I believe multiple DataReaders can use
the same connection (they just have to take turns, and be done, before the
next one uses the connection). I haven't used DataReaders much, but I
believe that is true (in my mind, they're like ADO recordsets).
DataSets actually are disconnected, so they use 0 (zero) connections. You
can Fill a DataSet over DataAdapter (which uses a connection). You can
certainly reuse the same Connection for multiple DataAdapters.
"jeff" <je...@datahook.com> wrote in message
news:OWrbSykwCHA.640@TK2MSFTNGP12...
William (Bill) Vaughn
First you have a serious connection problem. You MUST close connections
that you open while the Connection is still in scope. You CAN'T depend on
the Garbage Collector to do this for you (as you could in VB). There are two
fundamental ways ADO.NET handles connections:
1) With the DataReader, you're responsible for opening the connection
AND closing it. ADO.NET can help in this regard if you use the
ExecuteReader(CommandBehavior.CloseConnection) option. In this case, ADO.NET
will close the connection for you IF the DataReader is closed. ADO.NET will
also close the DataReader for you if you data bind (web-forms only).
2) With the DataAdapter (used to create a DataSet), the Fill method
handles the Connection for you. It opens it, completes rowset population and
closes the Connection--UNLESS you have opened the Connection before it
runs--in which case the Connection is left open.
Next, MSDE is not governed by connections. It never has been. This is a
common misconception. MSDE is governed by simultaneous tasks. That is, a
connection does not count against the task count unless you are actually
executing something. This means you can have hundreds of connections and the
governor won't kick in as long as fewer than 5 of those connections have
active tasks.
Yes, it's possible to share a single connection with MSDE. It's easiest
with the DataAdapter.Fill/Update methods as connections are not left in an
unusable state. If you use the DataReader, you're in charge of making sure
the DataReader completes rowset population or is closed before trying to use
it again. It's the DataReader that leads to most Connection leaks that
eventually bring your system to it's knees.
hth
--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"jeff" <je...@datahook.com> wrote in message
news:OWrbSykwCHA.640@TK2MSFTNGP12...
Inge
Block for this kind of thing - it seems to to pool connections for you.
Basically, you can use a single connection as long as you remember to CLOSE
that datareader before attempting to use it again...
And you might also have some transactional issues, as you cannot run
multiple transactions at the same time through one connection...
-Inge
"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:OZDN7OmwCHA.968@TK2MSFTNGP12...