I need to ensure that the application uses the minimum number of connections to the database

Question from certification exam:

You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4 to create an application. The application contains the following code segment. (Line numbers are included for reference only.)

01  class DataAccessLayer
02  {
03    private static string connString;
04
05    ...
06    public static DataTable GetDataTable(string command){
07
08      ...
09    }
10  }

You need to define the connection life cycle of the DataAccessLayer class. You also need to ensure that the application uses the minimum number of connections to the database. What should you do?

[A] Insert the following code segment at line 04.

private static SqlConnection conn = new SqlConnection(connString);
public static void Open(){
  conn.Open();
}
public static void Close(){
  conn.Close();
}

[B] Insert the following code segment at line 04.

private SqlConnection conn = new SqlConnection(connString);
public void Open(){
  conn.Open();
}
public void Close(){
  conn.Close();
}

[C] Replace line 01 with the following code segment.

class DataAccessLayer : IDisposable

Insert the following code segment to line 04.

private SqlConnection conn = new SqlConnection(connString);
public void Open(){
  conn.Open();
}
public void Dispose(){
  conn.Close();
}

[D] Insert the following code segment at line 07.

using (SqlConnection conn = new SqlConnection(connString)){
  conn.Open();
}

Some people are arguing that the correct answer is [D], but from my point of view it is no sense, because the connection is being opened and immediately closed after the "using" block.

Could someone point the correct answer and explain why?

Thanks!!!

Solution

None are the correct answer.

A-C are wrong because they don't handle exceptions.
C is also wrong because you want to encapsulate the data access from within your method that returns a DataSet. Datasets are disconnected, and there is no indication that you are doi anything that requires the class to hold open connections between method calls, so there is no reason to make the entire class hold a connection. Just do it within each method that makes DB calls.
D is close, but wrong. To fix it, add the data access code after the conn.Open() call, inside of the using() {...} block.

Note: I'm not sure if you didn't put the data access code after the .Open() call in D. If you assumed that to be understood, then D is actually the correct answer. When the connection is disposed it is released back into the connection pool. The connection pool will help you minimize the # of open connections. If you need to literally close the connections even when they are inactive you need to start looking into configuring your use of Connection Pooling.