Static vs. non-static connection

[Natan]

Hi,

I`m creating a asp.net intranet system, and would like to put all the
logic of the system in a single dll. That is, when i need to create an
user for example, i would use "User.Check()" and "User.Add()" instead of
putting "insert" sql statements in the page.

Considering he following logic:

User u = new User();

u.Name = "John Doe";
u.Email = "(e-mail address removed)";
u.Login = "john";
u.Pass = "blah";

if (!User.Exists(u) and User.IsValid(u))
User.Add(u);


User.Exists checks if there is a user with that login, User.IsValid
checks to see if the name and other data is valid.. and User.Add adds
the user to the database. All the three methods access the database and
i'm creating a method that will return this database connection.

My question is: What is the best practice? Should i create a method to
return a new SqlConnection object, open and close that conection in each
method i use it, or create a property that returns a static opened
connection? Even with connection pooling, i think there is a better way
to do things than opening and closing a connection 5 or 6 times in a
single page.

Other idea was open a single static connection once, let`s say in
global.asax and using it for every everywhere. Is there any problem with
this?

Thanks.

 

[Patrice]

A static connection is shared by all users.

You could create the connection once for the lifetime of the request (and
check first if opening/closing the connection on a per method basis is a
real performance hit).

Patrice

 

[Natan]

A static connection is shared by all users.

You could create the connection once for the lifetime of the request (and
check first if opening/closing the connection on a per method basis is a
real performance hit).

I thought about this, but how? I would need to pass the connection to
every method i use...

does anyone know a better way?

 

[Marina]

Try adding the connection to the Context collection, this should be
available in every method. Make sure you close it after everyone is done
with it.

 

[Natan]

Thanks! I didn`t know about this collection! Thanks a lot!

 

[Guest]

Best practice is NOT to instantiate a single connection and keep it open for multiple users or multiple queries, etc. The way connection pooling works is that the allocation of actual connections is managed for you. If many sessions need DB access, multiple connections are allocated and shared, as needed. As long as the connection strings are IDENTICAL, you will get optimized allocation for multiple users. (So, use a single user account for all database access, unless different users - or different application functions - require different permissions within the DB itself. If that's the case, the multple connection pools will be allocated for the different connection strings.

Keeping a connection open for longer than it absolutely has to be could, in effect, tie up an actual physical connection longer than necessary - making it unavaliable for the next command, or another command in another session. So, the only thing you have to persist (in an application variable - or wherever) is the connection string, not the connection

Open as late as possible, close as soon as possible. It's that easy - don't try to defeat the purpose of connection pooling


It's that easy - don't try to get

 

[Guest]

How many users are you signing on at once for that to be a performance
consideration?

 

[Natan]

How many users are you signing on at once for that to be a performance
consideration?

There is no heavy traffic, I just wondered what was the best way to do
it. I think using Context collection to store the connection is the best
way. This way I open one connection per request, use it everywhere and
then close it, like in old asp.

 

[Sami Vaaraniemi]

There is no heavy traffic, I just wondered what was the best way to do
it. I think using Context collection to store the connection is the best
way. This way I open one connection per request, use it everywhere and
then close it, like in old asp.

There's one caveat though - if you do anything during the request processing
that may block for a while (e.g., call a web service or read a file) then
the database connection will be in use unnecessarily. This will reduce the
number of concurrent requests you can serve. In this case, opening and
closing the database connection whenever you need it is better than storing
it (and keeping it open) in the HttpContext.

Sami