Best Practices? How many connections & adapters

  • Thread starter Thread starter M
  • Start date Start date
M

M

I've seen alot of sample code about how to create and use them but whats the
best way to have them per application. Should every form that access's a
database have its own connection and adapter or should they share one? I can
see where multithreading might have implications but I'm currently working
single threaded.

I'm assuming an adapter can be reinitialized with a different sql statement
and executed but I'm not sure if thats a good practice or not

Thanks for the info

Regards.
 
I think this is where ADO classic developers go astray -- you have to look
at things differently than you did in the past. With "old" ADO, you would
often hold a connection open for long periods of time -- in .Net, you will
NEVER (well, never say never, but you get the idea) hold a connection open
very long.

Assuming you are connecting to the same database every time, I recommend one
connection STRING with identical syntax.

Your form classes will share the connection simply because that is the
nature of datasets -- you open a connection to Fill or Update an adapter,
then close it: all of the work is done disconnected.
 
Earl comcast net> said:
I think this is where ADO classic developers go astray -- you have to look
at things differently than you did in the past. With "old" ADO, you would
often hold a connection open for long periods of time -- in .Net, you will
NEVER (well, never say never, but you get the idea) hold a connection open
very long.

Assuming you are connecting to the same database every time, I recommend one
connection STRING with identical syntax.

Your form classes will share the connection simply because that is the
nature of datasets -- you open a connection to Fill or Update an adapter,
then close it: all of the work is done disconnected.
Click to expand...
Thanks for the info.

I got the sharing of connection string the other day when I saw a snippet
about getting it from app.config. But for my little project I've create
about half a dozen forms and each time I've been just whipping up a new
connection and adapter. I didn't even know if one form knew about another
forms public members until I saw it in the intellisense. So I was thinking I
should share the connection because at this time there won't be any
contention.

I've used classic ADO (in c++) but this is so much better.

Thanks again.
 
Hi,

My opinion is that to keep minimum opened connections if any. Best approach
is to open connection, get data and close connection. It will release
resources for other clients. Since >NET provides pooling for the
connections, you will not see much of the performance impact when you open
and close connections periodically.
 
M - golden rule about database connections -

Open as late as possible, close as early as possible.

(and another one I might throw in with ADO.NET -> All connectionstrings must
look like "Exactly the same", for pooling to work :) ).

As long as you are following that, you wouldn't need to customize any of the
default connection pooling behavior. If you do decide that you wish to
override the default behavior, ADO.NET will let you do that too.

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/
 
Sahil Malik said:
M - golden rule about database connections -

Open as late as possible, close as early as possible.

(and another one I might throw in with ADO.NET -> All connectionstrings must
look like "Exactly the same", for pooling to work :) ).

As long as you are following that, you wouldn't need to customize any of the
default connection pooling behavior. If you do decide that you wish to
override the default behavior, ADO.NET will let you do that too.

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/
Click to expand...

I haven't really thought about connection pooling, I was just wondering if
each form should have its own connection object (if it needs it) or if I
should share one globally (somehow? I guess static would be it). I am
sharing the connection string so if I get any pooling from that even better.

Thanks

Regards
M
 
I really don't see any advantage of keeping a global connection object
except maybe saving a few KB of memory which in 2 GIG RAM P4s is hardly an
issue.

I would in argument of not using globals, having better code seperation, and
creating a relatively more manageable codebase - recommend that you should
have a connection object per form, if not more than that. If you really
wanted to go crazy on optimum memory use, I would also recommend reading
thru GC, and you can actually ressurect objects after they are GC collected
... (why bother to go to those depths though .. but it's an option).

If the connection string is exactly the same - you will get
connectionpooling by default even if you re-instantiate your forms again and
again and again.

On a sidenote - if this is an enterprise wide app, I would create a seperate
data access layer - like data access application block, and that way a
"Connection" object (back end) never appears on a "form" (UI).

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/
 
Sahil Malik said:
I really don't see any advantage of keeping a global connection object
except maybe saving a few KB of memory which in 2 GIG RAM P4s is hardly an
issue.

I would in argument of not using globals, having better code seperation, and
creating a relatively more manageable codebase - recommend that you should
have a connection object per form, if not more than that. If you really
wanted to go crazy on optimum memory use, I would also recommend reading
thru GC, and you can actually ressurect objects after they are GC collected
.. (why bother to go to those depths though .. but it's an option).

If the connection string is exactly the same - you will get
connectionpooling by default even if you re-instantiate your forms again and
again and again.

On a sidenote - if this is an enterprise wide app, I would create a seperate
data access layer - like data access application block, and that way a
"Connection" object (back end) never appears on a "form" (UI).

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/
Click to expand...

1000 Thank yous for the info. On the sidenote part, can you refer me to some
samples, articles, more info etc etc.
Thank you again
 
Back
Top