Connection handling

[John]

Hi

Should I dispose connection after every close in my app and re-create it
when opening it or would this have some adverse effect? My app
(vb.net+access backend) seems to be stressing the server a little bit and I
wonder if disposing the connection will help the server.

Thanks

Regards

 

[t.egginger]

Hello,

when doing a singlethreaded winforms app (and a
manageable ammount of users is accessing the application)
you could maybe think about the use of a singleton
connection class...
otherwise always close the connection and reopen it at
next use!

Regards

Tom

 

[Miha Markic [MVP C#]]

Hi John,

Yes, yes do it, it helps scalability.
The connections are pooled anyway so you mostly won't really close the
physical connections and that's ok.

 

[John]

All my data is coming through data adapter which I presume close the
connection after each fetch. This means that my application will normally
keep the connection closed. My question is how bad is it to recreate and
reopen a connection every time a user wants to say go to the next record.
Would he feel a visible delay before the command is carried out?

I ma just trying to see what is the most optimum way to handle connection in
my case.

Thanks

Regards

Hi John,

Yes, yes do it, it helps scalability.
The connections are pooled anyway so you mostly won't really close the
physical connections and that's ok.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi

Should I dispose connection after every close in my app and re-create it
when opening it or would this have some adverse effect? My app
(vb.net+access backend) seems to be stressing the server a little bit

and

I
wonder if disposing the connection will help the server.

Thanks

Regards

 

[Miha Markic [MVP C#]]

Hi John,

All my data is coming through data adapter which I presume close the
connection after each fetch. This means that my application will normally
keep the connection closed.

Only Fill method closes the connection if it wasn't opened before.

My question is how bad is it to recreate and

reopen a connection every time a user wants to say go to the next record.
Would he feel a visible delay before the command is carried out?

The delay is so minimal that nobody will ever notice it - createing a new
connection instance just uses an open connection (there is one open by
default) from the pool.

I ma just trying to see what is the most optimum way to handle connection in
my case.

Using new connection and closing it asap is the best way (normally) and also
protects you from concurrency issues.

 

[George]

John,

You state that your data is coming through a data adapter. My
assumption then is that the data is being loaded into a DataSet
object. If this is the case, DataSet Objects are disconnected from
the database according to my understanding and all the data is
physically stored in the dataset. Thus each read of the next record
does not go back to the database but simply to the dataset.

Is this a WinForms or WebForms based application? I am going to
assume WinForms. In this case the server might be stressing because
large amounts of data are being extracted from the database and loaded
into the DataSet. You might try only returning what the user needs
(IE - allow them to enter some criteria before retrieving information
from the database) if this is not already being done. Additionally,
if the data is simply displayed to the user and is read only, you
might explore using the DataReader object as it is much lighter weight
then a Dataset; however it is a Forward Only Read Only cursor.

Now to answer you original question, you might try disposing the
DataAdapter first and see what that does. If you re-sue the
connection in one request from the user (I.e. - they click a button
and three different tables are queried before any results are returned
to the user), I would not dispose the connection each time unless the
disposing of the DataAdapter does not generate the desired
improvements. Regardless, when working with any database objects or
data (IE connection, adapters, commands, datasets etc) I would highly
recommend releasing them (i.e. closing, disposing and setting to
nothing) as soon as their use is complete. Also note that the command
object (if you are using it) also has a dispose method.

I hope this helps. If not, possibly providing a little more
insight into the architecture of the application and what exactly is
meant by 'stressing the server a little bit... ' would help.

George