When to open a database connection?

[Keith Smith]

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?

[Cor Ligthert]

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

[Keith Smith]

> 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?

[Carlos J. Quintero [.NET MVP]]

> 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

[Harold Howe]

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

[Alvin Bruney [MVP]]

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

[Harold Howe]

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