Typed connection questions

[pmclinn]

When I'm connecting to Oracle or SQL I always wonder what is the best
way to close/dispose of a connection. I've been toying around with
creating a public shared class that has a connection string and then
calling on the shared class when needed. (See raw examples below.)
If two of my users open up two different pages at the same time will
this still work. And if so should I pre-test to see if the connection
state is already open using a myconnection.state = open then or if
myconnection.state = closed then.... In oracle I have seen that with
the old 1.1 framework we had connection leaks and this drove me
crazy. So what is the best way to handle multiple pages requesting
different sql commands at the same time?

EG:
Public Class clsData
Public Shared MyConnection As New
SqlConnection("server=MyServer;uid=;pwd=;database=;pooling=true")
End Class

And then referencing the class when needed:
such as:

MyConnection.Open()
Response.Write(MyConnection.State.ToString)
MyConnection.Close()
MyConnection.Open()
Response.Write(MyConnection.State.ToString & "2")
MyConnection.Close()
or
try
MyConnection.Open()
Response.Write(MyConnection.State.ToString)
catch
'err handling
finally
MyConnection.Close()
end try

 

[Samuel R. Neff]

Using a single connection is not thread safe. Create a static method
that creates a new connection each time it's called. With connection
pooling you won't see a degredation in performance and it'll be thread
safe.

Also make sure you always close the connection within a finally block
to prevent connection leaks (c# has a "using" statement for this, VB
2005 has something equivalent but I'm not sure if the keyword is the
same or not).

HTH,

Sam

 

[pmclinn]

Interesting enough, this was original code implementation, but I'm
still having simultaneous connection errors using this format (in 2.0
framework) with this format. 'system.data.oracleclient' must still be
buggy. I saw a hot fix for 1.1 but nothing for 2.0. Maybe I'll change
over to oracle's library and see if this fixes the issue. Thanks.