By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,474 Members | 3,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,474 IT Pros & Developers. It's quick & easy.

When to open a database connection?

P: n/a
I read in a Visual Studio .NET book that with C# it is now recommended to
open database connections whenever you need to query a database as opposed
to the traditional method of opening a database at the start of your app and
closing it at the end. Their reasoning was that "opening a database
connection is no longer an expensive operation".

Do you guys agree?
Nov 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Keith,

It has simple to do with connection pooling in SQL server.
Where leaving the connections open is expensive.

When you use by instance MS access you can think about leaving it open
because otherwise somebody can replace the accessfile because it is not
locked and than you get a strange situation.

Because of that connectionpooling is as well advised as an exception on the
normal use of close, to use connection.dispose instead of connection.close
in the versions 2002/2003. That last will be gone in the version 2005.

Cor
Nov 16 '05 #2

P: n/a
> It has simple to do with connection pooling in SQL server.
Where leaving the connections open is expensive.

When you use by instance MS access you can think about leaving it open
because otherwise somebody can replace the accessfile because it is not
locked and than you get a strange situation.

Because of that connectionpooling is as well advised as an exception on
the normal use of close, to use connection.dispose instead of
connection.close in the versions 2002/2003. That last will be gone in the
version 2005.

Cor


Thanks alot!

My database is an MS Access Database. Is there anything I need to do to
make it "multi-user" capable? Or is this already how it is set up by
default?
Nov 16 '05 #3

P: n/a
> My database is an MS Access Database. Is there anything I need to do to
make it "multi-user" capable? Or is this already how it is set up by
default?


AFAIK, it is multiuser by default, you need to request exclusivity
explicitly to make it single-user with an attribute in the connection
string.

--

Carlos J. Quintero

MZ-Tools 4.0: Productivity add-ins for Visual Studio .NET
You can code, design and document much faster.
http://www.mztools.com

Nov 16 '05 #4

P: n/a
Keith Smith wrote:
I read in a Visual Studio .NET book that with C# it is now recommended to
open database connections whenever you need to query a database as opposed
to the traditional method of opening a database at the start of your app and
closing it at the end.


I recently profiled the expense of creating and opening connections on
the fly using intel vTune. One method I profiled looked sort of like this:

DataSet Load(int x)
{
using(IDbConnection con = Factory.CreateConnection())
{
conn.Open();
DataSet result = new DataSet();

IDbDataAdapter adapter1, adapter2;
// initialize adapters and sql text using x and conn
adapter1.Fill(result);
adapter2.Fill(result);
return result;
}
}

Everytime a certain form opened, this routine was called about 200 times
(in a background thread, FWIW). The execution profile of Load broke down
like this:

total time spent inside this method: 7.397 s
execution count : 273
adapter1.Fill -> 3.814 s == 51.6%
adapter2.Fill -> 2.714 s == 36.7%
conn.Open -> 0.841 s == 11.4%

avg time to open connection : 3.0 ms

Both queries utilized an index on the table. Each datatable had on
average about 50 rows and 6 columns.

Based on the results that I saw, I agree with the "Just create
connections on the fly" philosophy, but don't create them over and over
again in a loop, if you can avoid it.

Creating them on the fly can also make life easier in other ways. First,
it makes it easier to write a using statement to dispose the connection.
Also, if you are writing a multithreaded app, you won't make the mistake
of using the same connection in two threads at the same time. My Load
method can be called from multiple threads without worrying about
synchronization issues.

H^2

Nov 16 '05 #5

P: n/a
the recommendation is only for windows forms applications.

ideally, you would want to turn connection pooling on if it isn't so that
connections are recycled instead of the expensive create step

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"Harold Howe" <hh***@gowebway.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...
Keith Smith wrote:
I read in a Visual Studio .NET book that with C# it is now recommended to
open database connections whenever you need to query a database as
opposed to the traditional method of opening a database at the start of
your app and closing it at the end.


I recently profiled the expense of creating and opening connections on the
fly using intel vTune. One method I profiled looked sort of like this:

DataSet Load(int x)
{
using(IDbConnection con = Factory.CreateConnection())
{
conn.Open();
DataSet result = new DataSet();

IDbDataAdapter adapter1, adapter2;
// initialize adapters and sql text using x and conn
adapter1.Fill(result);
adapter2.Fill(result);
return result;
}
}

Everytime a certain form opened, this routine was called about 200 times
(in a background thread, FWIW). The execution profile of Load broke down
like this:

total time spent inside this method: 7.397 s
execution count : 273
adapter1.Fill -> 3.814 s == 51.6%
adapter2.Fill -> 2.714 s == 36.7%
conn.Open -> 0.841 s == 11.4%

avg time to open connection : 3.0 ms

Both queries utilized an index on the table. Each datatable had on average
about 50 rows and 6 columns.

Based on the results that I saw, I agree with the "Just create connections
on the fly" philosophy, but don't create them over and over again in a
loop, if you can avoid it.

Creating them on the fly can also make life easier in other ways. First,
it makes it easier to write a using statement to dispose the connection.
Also, if you are writing a multithreaded app, you won't make the mistake
of using the same connection in two threads at the same time. My Load
method can be called from multiple threads without worrying about
synchronization issues.

H^2

Nov 16 '05 #6

P: n/a
Alvin Bruney [MVP] wrote:
the recommendation is only for windows forms applications.

ideally, you would want to turn connection pooling on if it isn't so that
connections are recycled instead of the expensive create step


I believe pooling was on in my test. Otherwise, the open call would have
taken longer, I think.

PS: I only write windows forms apps.

H^2

Nov 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.