manage oracle sql connection in web api

Question

I am creating a Microsoft Web API backend that connects to an Oracle database. Which is the best way to manage the DB connection: open the database connection once and access it through a static class / method, or open a DB connection each time I need to query the DB and close it again?

Right now I'm using the static connection:

public static class DbConnConfig {

    public static string ConnString = "Provider=OraOLEDB.Oracle; ... otherstuff ...";
    public static OleDbConnection conn = null;

    public static OleDbConnection getConnection() {
        if(DbConnConfi.conn==null) {
            conn = new OleDbConnection(ConnString);
            conn.Open();
        }
        return conn;
    }
}

... and then in any class that needs the connection I call DbConnConfig.getConnection()

Answer 1

It can vary depending on your specific requirements. Here is couple of points to ponder about.

If your app is constantly running and pooling the DB, I would leave the connection open throughout its lifespan. Creating and releasing a DB connection is not needed as you can reuse the same connection. That will save you the resources spent on initiating and releasing the DB connection and it is generally safe: is it safe to keep database connections open for long time. Also, you might be paying more money as some licenses take in to account the # of connection to the DB in their pricing model.

Another aspect you should look into is not to exhaust the number of active connections. That is, plan/design/architect your app with scalability in mind. You can check the number of active connections here. I remember I had an issue with that. I ended up closing connections after finishing units of work (other option was to increase the size of the connection pool). Although, if you are just starting dev and your app is the only consumer, that will not be a problem LOL

Another case where it would be better to just close the DB connection is when you know it will not get used for a long period of time. That way you will have less connections opened (again, scalability in mind). You will also regain the resources occupied by that connection (like memory if that is of any concern to you).

You might find this interesting as well: Entity Framework and Connection Pooling