Connection Singleton

[Jeff Cope]

Is there any problem with having a singleton connection in an application?
i.e., a single place to get a connection and once that connection has been
established that's what will get returned each time the call to
"GetConnection" is made? In effect the connection is cached.

In this scenario, I'm assuming the connection timeout needs to be set the
same as the IIS session timeout value.

 

[solex]

Jeff,

Since ADO.NET implements connection pooling by default why not store the
connection string in your singleton instead of the actual connection?

Dan

 

[David Browne]

Is there any problem with having a singleton connection in an application?
i.e., a single place to get a connection and once that connection has been
established that's what will get returned each time the call to
"GetConnection" is made? In effect the connection is cached.

Only one thread at a time can use a connection. So if you want to serialize
all of your database access, then all your sessions can share a single
connection. Of course you don't want to do this, so no you cannot cache and
share a connection in an asp.net application.

In a winforms application, you can, but in a web application you generally
cannot.

You can have a static (shared in vb) method GetConnection, but it should
return a new connection each time, and the connection still must be closed.
Something like

class globals
Public Shared Function GetConnection() as SqlConnection
dim con as new SqlConnection(...)
con.open
return con
end function
.. . .
end class


then in page_load or wherever

dim con as SQLConnection = GetConnection()
try
dim cmd as new SQLCOmmand("...",con)
cmd.ExecuteNonQuery()
finally
con.close
end try

Doing this your application will open and share a small pool of connections
to the database, with a new connection opening only when no connection is
available in the pool.


David