Best way to deal with DB Connection in the DAL - create or pass?

Question

I want my Data Access Layer to be built very modular.
Therefore I have data retrieval methods that sometimes are called directly from the business layer and sometimes are called by other data retrieval methods to create object dependencies.

What is the best way to deal with database connections in the DAL?

a) Create a new connection in every method and dispose it afterwards.
Good: Easy to write and to work with.
Bad: Many connections are being opened and closed. (performance?)

b) Pass the connection as an (optional) argument.
Good: I could reuse an open connection for multiple commands.
Bad: I have to keep track of the ownership of the connection (who has to close it?) and cannot use the very neat "using" statements.

c) Something else? (Connection as singleton maybe?)

This is the first time I am writing a real DAL so I really could use some help from you experienced folks.

EDIT: As it seems to matter, it's an ASP.Net Website Project.

Answer 1

If you're using ASP.Net, option A is your friend.

Create a new connection for each request, Dispose()-ing when the request is complete. Ensure that you use identical connect strings. The connections will (by default) remain open and available through a connection pool.

See http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx for more information on connection pooling.

You pretty much have to do it this way in a web server so you don't have concurrency issues anyway. Everything needs to be thread-safe (you've got no idea how many concurrent worker threads are executing in your app).

[edited to add example code]

As an example, here's what I would consider to be a typical method to execute a stored procedure. This comes from a custom code generator I wrote — handwritten code will likely look a little different — but it should suffice to get the point across:

public int Exec(  int? @iPatientID )
{
  using ( SqlConnection  conn = new SqlConnection( this.ConnectString ) )
  using ( SqlCommand     cmd  = conn.CreateCommand() )
  using ( SqlDataAdapter sda  = new SqlDataAdapter( cmd ) )
  {
    cmd.CommandText = STORED_PROCEDURE_NAME ;
    cmd.CommandType = CommandType.StoredProcedure ;

    if ( this.TimeoutInSeconds.HasValue )
    {
      cmd.CommandTimeout = this.TimeoutInSeconds.Value ;
    }

    //
    // 1. @iPatientID
    //
    SqlParameter p1 = new SqlParameter( @"@iPatientID" , SqlDbType.Int ) ;
    if ( @iPatientID == null )
    {
      p1.Value = System.DBNull.Value ;
    }
    else
    {
      p1.Value = @iPatientID ;
    }
    cmd.Parameters.Add( p1 ) ;

    // add return code parameter
    SqlParameter pReturnCode = new SqlParameter() ;
    pReturnCode.SqlDbType    = System.Data.SqlDbType.Int ;
    pReturnCode.Direction    = System.Data.ParameterDirection.ReturnValue ;
    cmd.Parameters.Add( pReturnCode ) ;

    DataSet ds = new DataSet() ;

    conn.Open() ;
    sda.Fill( ds ) ;
    conn.Close() ;

    this.ResultSet  = ( ds.Tables.Count > 0 ? ds.Tables[0] : null ) ;
    this.ReturnCode = (int) pReturnCode.Value ;

  }

  return this.ReturnCode ;

}