C# - Should I use static database connection

Question

In my application to connect to Orace database I always create a new connection, open it, execute OracleCommands and finally close it afterwards. Recently I thought implementing a static connection would be a better idea. Say I have a static connection that I can access from anywhere. Each time I need to connect to database I can check for the state of my static connection,open it if it's not already open and close it afterwards. Do you think this would be beneficial or there are more disadvantages?

Answer 1

I'm assuming you're using ODBC here because you haven't stated exactly and it's normally used...

No, you should use a new connection each time, this is the standard practise that Microsoft recommend. If you're using ODBC etc then windows manages these connections, caching them for re-use and it makes it easier to manage the lifetime of things.

If you use a static connection, you might dispose of it to early or have closed it without knowing. In general it's just a bit more awkward and a premature optimization.

To deploy high-performance applications, you frequently must use connection pooling. However, when you use the .NET Framework Data Provider for ODBC, you do not have to enable connection pooling because the provider manages this automatically.

See OdbcConnection for more info.

Answer 2

In general, no, you shouldn't use a single connection - all of the .NET ADO.NET providers support connection pooling, and the normal pattern is to open/close connections as you need them (in a using or try/finally block to ensure the connection is closed in the event of an exception).

In a single-threaded client application you could get away with using a shared static connection, but it's unlikely to give you any measurable performance benefit - so don't do it.

In any other application, you definitely shouldn't use a shared static connection, as it is not thread-safe.