SQLConnection question

[bnob]

Im my ASP.net project I do a lot of connection to the SQL Server
Database. I use a global variabe for my connection declare in a module
(VB.Net code behind)

All is ok but when I start a form from the default page it takes 30
seconds to show the resultat of a select request

And when, during this 30 seconds, a another user want open a another
forms from the default page, I have an error : "Connection are already
open"

I think it 's because I use the connection variable globally.

Any idea to use the best way the connection object in a ASP.net project
?

 

[Guest]

Why don't you create a new connection every time? Most driver has built-in
connection pooling already.

 

[Marina]

Yes - don't make shared objects! Think about it, one connection for an
entire app - so of course if a second user tries to use it while the first
user is using it, you are going to have problems.

So solution is to not use global connection objects, or any object which
really needs to be per user.

 

[bnob]

So the solution is to create a connection object every time and close it
when the request is finished

I mean connections object that are per user.

Yes - don't make shared objects! Think about it, one connection for an
entire app - so of course if a second user tries to use it while the first
user is using it, you are going to have problems.

So solution is to not use global connection objects, or any object which
really needs to be per user.

 

[Marina]

Yes, i would even say do it on a per method basis. That way you aviod
potential connection leaks.

So the solution is to create a connection object every time and close it
when the request is finished

I mean connections object that are per user.

 

[bnob]

Ok but what about polling ?

Must I add paramters poll in the connection string ?

 

[Marina]

Polling? What does polling have to do with this? What is polling and for
what?

 

[Scott Allen]

By chance did you mean "pooling"?

With SqlConnection and the SQL provider pooling happens by default.
You don't need to add any pooling parameters unless you want to turn
off pooling or adjust the number of connections in the pool.

 

[bnob]

For you the best solution is to create a connection object every time and
close it
when the request is finished

I mean connections object that are per user.

 

[Kevin Spencer]

Listen carefully: When you need to access the database (forget about "per
user"), open a Connection, do your business, and close the Connection.
Immediately. Always.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

 

[Patrick Olurotimi Ige]

Thats right Kevin..
Fast and Easy!
Patrick