In a C# application, should a DB Connection be created once, or everytime a SQL statement is executed?

Question

In a C# application, should a OleDBConnection be created once, or every time a SQL statement is executed?

I am looking at C# code written by someone else. Every SQL statement is preceded with the creation of an OleDbConnection object whose connection string points to an MDB database.

Is it appropriate to create an OleDbConnection object every time, or should the object be created at the start of the application and used from then on.

Answer 1

It depends on the situation.

If you're going to be executing several statements in a row, then you're better off performance-wise to open it once, execute all the statements, and then close it,

If you're asking about opening the connection when the program starts and keeping it open until the program closes regardless of what's going on, then no. Close it as soon as you're done with it. It's better not to leave a connection hanging open.

Another factor that people never seem to think of is the maintenance programmer coning along later, who has to trace the code and keep track of where the connection is opened and when it is closes. Say, for example, that you have a program that accesses a database, then branches off into several other functions, each of which may need the connection. Tracking that stuff in code is a nightmare.

Then again, this factor is secondary to proper operation and performance, but still something to consider in a complex app.

The main factor is how it will affect performance vs. the cose of keeping a connection open. You need to decide that in each situation.

Answer 2

You should open a connection every time you need something from the db and close it afterwards. Leave to the connection pool to decide when physically close the connection.

Answer 3

According to Best Practices for Using ADO.NET:

High performance applications keep connections to the data source in use for a minimal amount of time, as well as take advantage of performance enhancing technology such as connection pooling...

Answer 4

Hi,

Assuming connection pooling is available for your database, which likely it is you will want to open and close a connection for every call to your database. This allows you to only use the finite resource of a database connection only when you need it and then return it to the pool for other callers to use as soon as you finished making the call. If you hold on to the connection you will soon run out of the finite resource, that being the total available connections to the database, and hence severely hinder the scalability and performance of your application.

I usually use a using statement to ensure the connection is close after use - see below:

  using (ODBCConnection c = new ODBCConnection(ConnectionString))
  {
    c.Command.CommandType = CommandType.Text;
    // make a call
  }

Enjoy!

Answer 5

Your connection to the database should only be open while you are performing activity directly on the database. Maintaining an open connection to the database while your application is performing other activity can block other users from accessing the database once your connection pool reaches its limit.

If you have a CPU-intensive function to perform on the client PC, then you should perform this task once you have closed your connection so that you do not tie up the connection.

But if you have a series of database functions to perform, your client's performance can be improved by doing these together in a single open connection.