C # - Should I use a static database connection

Question

In my application to connect to the Orace database, I always create a new connection, open it, start OracleCommands, and finally close it later. I recently thought that implementing a static link would be a better idea. Let's say I have a static connection with which I can access from anywhere. Every time I need to connect to the database, I can check the status of my static connection, open it if it still does not open and does not close it later. Do you think this would be helpful or have more flaws?

Answer 1

I assume that you are using ODBC here because you did not specify exactly and usually used ...

No, you should use a new connection every time, this is standard practice recommended by Microsoft. If you use ODBC, etc., Then windows manage these connections, caching them for reuse, and this simplifies life time management.

If you use a static connection, you can get rid of it earlier or close it without knowing. All in all, this is a bit more inconvenient and premature optimization.

To deploy high-performance applications, you often need to use pooling. However, when you use the .NET Framework Provider for ODBC data, you do not need to enable pooling because the provider manages this automatically.

See OdbcConnection for more details.

Answer 2

In the general case, no, you should not use one connection - all .NET ADO.NET providers support pooling, and the usual template - open / close connections as needed (in using or try/finally , to ensure that the connection is closed in case of an exception) .

In a single-threaded client application, you can get rid of the use of a shared static connection, but this is unlikely to give you any measurable performance advantage - so don't do this.

In any other application, you should definitely not use a shared static connection, as it is not thread safe.