Managing Connections - Best Practice

  • Thread starter Thread starter Manoj Misran
  • Start date Start date
M

Manoj Misran

I checked lot of posts on this topic and found a common consent of
everyone (including Microsoft) that
--one should open the connection as late as possible and close it ASAP
after he is done.
--Keep the connection open for a minimal period of time.
--Try to use disconnected database approach using DataSets as oppose
to DataReader.

Now, keeping these things in mind I want to plan my database layer.
Earlier I was having a global static connection object that I used to
open at the start of the application and close just before exiting. It
is an MDI application and has about 30 forms and all of them use
extensive database operations.

Now I am planning to change the connection strategy according to these
suggestions. I have one last concern, appreciate if someone can help
me.....

Can I still use the same global static connection object that I create
when application start and just open() and close() the same in all my
forms when I do any DB operations. Or should I even create a new
connection object everytime I open a form.

What my thought is, creation of connection object and just creating a
reference should not hold the connection if we close it properly after
the use and reopen the same connection reference when we need it. Will
it have any impact on the performance. Why I want to go this route is
because I am using this connection at many places and it will be a
major change if I need to remove this static connection object.
Otherwise I just have to open and close the same without worrying
about redefining the same in every form.

But either way I want to know what is the best approach.

Thanks in advance
 
Hi Manoj,

I still use the same global static connection object that I create
when application start and just open() and close() the same in all my
forms when I do any DB operations. Or should I even create a new
connection object everytime I open a form.
Click to expand...

The latter is better in case you are using readers or threading.
It doesn't hurt anyway.
What my thought is, creation of connection object and just creating a
reference should not hold the connection if we close it properly after
the use and reopen the same connection reference when we need it. Will
it have any impact on the performance. Why I want to go this route is
because I am using this connection at many places and it will be a
major change if I need to remove this static connection object.
Otherwise I just have to open and close the same without worrying
about redefining the same in every form.
Click to expand...

The best approach is to create connection objects (holding only connection
string globaly).
However, if you never need more than one connection at a time you should
have no problems with global connection.
 
However, if you never need more than one connection at a >time you
should
have no problems with global connection.
Click to expand...

Thanks a bunch Miha,
I will go with the global connection as I am not needing more than one
connection at a time and it will be a minimum change for me but I
understand your reasoning behind the other approach.

Now the other thing, I have about 23 objects (User, Customer, Vendor
etc) to be created in almost all forms and all uses database info. Now
if I put Open(), Close() connection in these objects, I am effectively
opening and closing 23 times without any visual break. Should I open the
connection before creating the first object and pass the reference
thereafter and close it after the last object. What is the industry
practice.

Thanks again
 
Hi Manoj,

Manoj Misran said:
Thanks a bunch Miha,
I will go with the global connection as I am not needing more than one
connection at a time and it will be a minimum change for me but I
understand your reasoning behind the other approach.

Now the other thing, I have about 23 objects (User, Customer, Vendor
etc) to be created in almost all forms and all uses database info. Now
if I put Open(), Close() connection in these objects, I am effectively
opening and closing 23 times without any visual break. Should I open the
connection before creating the first object and pass the reference
thereafter and close it after the last object. What is the industry
practice.
Click to expand...

If this is an atomic operation (open many objects) than you should open the
connection at the beginning at close it at then end - you'll gain some speed
because even opening a connection from the pool takes some processor ticks.
 
On 23 Dec 2003 13:02:16 -0800, (e-mail address removed) (Manoj Misran) wrote:

¤ I checked lot of posts on this topic and found a common consent of
¤ everyone (including Microsoft) that
¤ --one should open the connection as late as possible and close it ASAP
¤ after he is done.
¤ --Keep the connection open for a minimal period of time.
¤ --Try to use disconnected database approach using DataSets as oppose
¤ to DataReader.
¤
¤ Now, keeping these things in mind I want to plan my database layer.
¤ Earlier I was having a global static connection object that I used to
¤ open at the start of the application and close just before exiting. It
¤ is an MDI application and has about 30 forms and all of them use
¤ extensive database operations.
¤
¤ Now I am planning to change the connection strategy according to these
¤ suggestions. I have one last concern, appreciate if someone can help
¤ me.....
¤
¤ Can I still use the same global static connection object that I create
¤ when application start and just open() and close() the same in all my
¤ forms when I do any DB operations. Or should I even create a new
¤ connection object everytime I open a form.
¤
¤ What my thought is, creation of connection object and just creating a
¤ reference should not hold the connection if we close it properly after
¤ the use and reopen the same connection reference when we need it. Will
¤ it have any impact on the performance. Why I want to go this route is
¤ because I am using this connection at many places and it will be a
¤ major change if I need to remove this static connection object.
¤ Otherwise I just have to open and close the same without worrying
¤ about redefining the same in every form.
¤
¤ But either way I want to know what is the best approach.
¤

Typically I recommend against using persistent connections. In a multi-user environment considerably
more database resources are used under this scenario. In addition, database administrators often set
up the database server to terminate idle open connections after a specified period of time. This
leaves you with a connection object that is valid but with a reference to a dead database resource.

If database resources and the automatic termination of idle connections aren't issues in your
environment, you could use your persistent global connection to maintain an active connection pool.
Essentially you open the persistent connection but never actually use it. When you need to access
the database, create a new connection and close it when you're done. This should enhance performance
when requesting connections on an as needed basis.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi Manoj,
I think best practise is share just connection string betwen classes. Data
adapters open and closes connections automaticly. Dont afraid of lost
performance when you close and open connection when you work with
database... ADO.NET provide great fuctionality called "connection pooling"
for more info see "Professional ADO.NET" from WROX publishing....

Pavel KOHOT
Advantage Solutions, Ltd.
 
Back
Top