By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,965 Members | 1,475 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,965 IT Pros & Developers. It's quick & easy.

Caching a sqlconnection

P: n/a
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
Nov 16 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
Nov 16 '07 #2

P: n/a
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
Nov 16 '07 #3

P: n/a
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
Nov 16 '07 #4

P: n/a
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
Nov 16 '07 #5

P: n/a
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
Nov 16 '07 #6

P: n/a
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
Nov 16 '07 #7

P: n/a
(and when I say "cache", I mean for instance in a static field, not
the asp.net cache)
Nov 16 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.