Caching a sqlconnection

[=?Utf-8?B?UmJydA==?=]

I am developing a asp.net 2.0 app using vb and am thinking about using the
system cache to cache a sqlconnection. However, I am concerned there might be
implications to this which are not obvious.

Can anybody offer any thoughts on whetehr this might be a good or bad idea?

Thanks

Robert

[Marc Gravell]

Don't bother. Just ensure you close and dispose your connections after
every use, and let the connection-pool do its job. Closing a
SqlConnection doesn't close the *actual* connection - it just releases
it to the pool for re-use.

Marc

[=?Utf-8?B?UmJydA==?=]

The reason I am interested in doing it is that it saves code. To open a
connection, I have to go to the trouble of setting the connection string,
catching and handling exceptions....

To get it from the cache, I just

dim conn as sqlconnection = new sqlconnection
if not isnothing(cache("conn")) then
conn=cache("conn")
else
set up conn, open conn, handle errors, cache conn...
end if

"Marc Gravell" wrote:

Don't bother. Just ensure you close and dispose your connections after
every use, and let the connection-pool do its job. Closing a
SqlConnection doesn't close the *actual* connection - it just releases
it to the pool for re-use.

Marc

[Marc Gravell]

in asp.net? that means that every request is going to be trying to use
*the same* connection. Not even "MARS" is that good...
Seriously, people have spent a lot of time getting pooling "right"...
and it isn't any more code:

using(SqlConnection conn = new SqlConnection(someString)) {
conn.Open();
}

that's it! I'm sure VB.NET has a "using" equivalent... (I'm a C# guy
so can't say 100%)

Marc

[=?Utf-8?B?UmJydA==?=]

I have a "utility" connection in the code that is used for performing
"plumbing" type tasks - building controls, logging error messages, reading
system parameters from a parameter table, logging in a user, etc... That is
the connection I would be caching.

However, your comment tells me that I might be overloading the connection
when there are many sessions so that is the sort of thing I was wondering
about.

Thanks.

"Marc Gravell" wrote:

in asp.net? that means that every request is going to be trying to use
*the same* connection. Not even "MARS" is that good...
Seriously, people have spent a lot of time getting pooling "right"...
and it isn't any more code:

using(SqlConnection conn = new SqlConnection(someString)) {
conn.Open();
}

that's it! I'm sure VB.NET has a "using" equivalent... (I'm a C# guy
so can't say 100%)

Marc

[Miro]

I thought I read that connection pooling is automatic - ( not using mars ), that if
all the parameters are the same as what the last connection pool was, it uses the
connection from the pool.

Change anything - even as far as a variable name, its considered a new connection.

So the trick is to always use the same "my.connection" setting or whatever ur variable is.

I may have read it wrong / and be completely wrong - im a newbie.

M.

Rbrt wrote:

I have a "utility" connection in the code that is used for performing
"plumbing" type tasks - building controls, logging error messages, reading
system parameters from a parameter table, logging in a user, etc... That is
the connection I would be caching.

However, your comment tells me that I might be overloading the connection
when there are many sessions so that is the sort of thing I was wondering
about.

Thanks.

"Marc Gravell" wrote:

>in asp.net? that means that every request is going to be trying to use
*the same* connection. Not even "MARS" is that good...
Seriously, people have spent a lot of time getting pooling "right"...
and it isn't any more code:

using(SqlConnection conn = new SqlConnection(someString)) {
conn.Open();
}

that's it! I'm sure VB.NET has a "using" equivalent... (I'm a C# guy
so can't say 100%)

Marc

[Marc Gravell]

RbrtI have a "utility" connection in the code that is used for
performing "plumbing" type tasks
cache the connection string; let the connection-pool worry about the
rest. Honestly, this is the best approach. Otherwise you will have
concurrency and/or scalability issues.

MiroI thought I read that connection pooling is automatic <snip>
Your understanding is correct; it simply compares the connection
string. Note that pooling can be disabled, and isn't enabled by
default on all providers - but with SqlClient it is.

Marc

[Marc Gravell]

(and when I say "cache", I mean for instance in a static field, not
the asp.net cache)