VB.net: Any problems using a Shared method for getting a db connection?

Question

I have a utility class that creates & returns a db connection:

Public Shared Function GetConnection() as OracleConnection
  dim c as New OracleConnection()
  ... set connection string...
  c.Open()
  Return c
End Function

Is there any risk of concurrent calls returning the same connection? The connection string enables pooling.

Answer 1

Since you are returning a new connection each time you will not have any concurrency issues. If you were using a Shared method to return multiple references to the same instance, that could be a problem but that is not what you are doing here.

You are safe to use this method in this way as long as you are always returning a new instance of your database connection object each time. Any connection pooling will also work the same as it always would - you won't need to worry about your Shared method usage created problems there either.

Answer 2

Forget the concurrent calls issue for a moment. If there is any connection pooling going on you will absolutely have reuse of the same underlying database connections, even if they don't use the same object.

This is generally a desirable thing as opening a connection to the DB can be an expensive operation.

Are you worried about closing the connection object out from under another caller? If so, as another response pointed out, I think you are safe with the code you provided.

Answer 3

I don't think so.

Since c is a local variable ("stack variable") and not a static one every call has it's own instance of c. Next you create a new object (the connection) and return this.

Answer 4

Shouldn't be any problem with concurrency, because each call is a new connection.

I might make one change, though: make the method private.

This will force you to put all your data access code in one class and push to create a nice, separate data access layer. At very least make it internal so that your data access layer is confined to a single assembly (that's separate from the rest of your code).