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
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
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
>
>
>
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
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
>
>
>
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
>>
>>
>>
Rbrt> I 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.
Miro> I thought I read that connection pooling is automatic
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
(and when I say "cache", I mean for instance in a static field, not
the asp.net cache)