When to open a database connection?

  • Thread starter Thread starter Keith Smith
  • Start date Start date
K

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?
 
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
 
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
Click to expand...

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?
 
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?
Click to expand...

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
 
Keith said:
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.
Click to expand...

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
 
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
 
Alvin said:
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
Click to expand...

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
 
Back
Top