use an oledbconnection on more than one form

[hcs]

Hello,

i have got a form working which displays data from an access database. is it
possible to share the connections, dataadapter and datasets that have been
setup in this form on another form???

Cheers

 

[Sahil Malik]

Yes it is possible to share connections. But do remember that ADO.NET will
pool the connections for you automatically, and in most cases it makes sense
to let ADO.NET pool the connections for you rather than you pooling them
yourself. It could be argued that for a winforms app connection pooling is
uneccessary but I digress especially because by not connection pooling you
lock yourself in a bad architecture that prevents you from remtoing part of
your application to an app server and sharing connections in a database
layer over there as the demands of your application continue to grow.

Even if you were to pool the *instance* of the connection object, you'd only
be pooling the instance, not the underlying connections (which is what you
really want to do). To do that you'd have to explicitly disable connection
pooling.

Now if you still insist on sharing connections, you'd have to expose the
connection objects from this form as public variables and let the other form
access them via an instance of this form. If it is an MDI application or
similar, it may make sense to keep these connections as the logical
equivalence of global variables on the main parent form and pass the
references to the child forms.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[W.G. Ryan eMVP]

Like Sahil says- the answer is yes. However with the connection - just
share the connection string and instead of sharing the connection make sure
that you close all those bad boys once their opened so they are returned to
the pool

In VB you can create the datasets- adapters etc as either shared properties
or stick them in a module (which is the same thing under the hood).

 

[hcs]

Thanks you have helped point me in the right direction

 

[Guest]

Related Question...
Is the connection always open after it is created (until you explicitly
close it) or does VB open when you make change with a DataAdapter and close
it immediately?

 

[Sahil Malik]

Remember connection object != actual db connection. Connection object sits
on top of a connection pool.

If you simply create a connection object, that object's state is closed ..
HOWEVER .. there might be one or more open connections in the connection
pool underneath.

If you call a dataadapter.fill, and pass it a connection *object*, it
changes it's state to Open, fills, and Close.

Now, when it calls Open, what happens behind the scenes is - it checks if
there is an available and idle open connection in the connection pool. if
there is, it hands it over, else opens a brand new one. But to your
application, it won't even notice the difference - except in better
performance maybe.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik