Geek Answers Handbook

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

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

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

a) Create a new connection in each method and delete it later.
Good: easy to write and work.
Bad: many connections open and close. (Performance?)

b) Pass the connection as an optional argument.
Good: I can reuse an open connection for multiple teams.
Bad: I need to track the ownership of the connection (who should close it?) And cannot use very neat “used” statements.

c) Something else? (Perhaps the connection is as a singleton?)

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

EDIT: How it matters, this is an ASP.Net website project.

+5
source share
5 answers

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

, Dispose(), . , . ( ) .

. http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx.

-, concurrency. ( , ).

[, ]

, . , — , , . , :

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 ;

}
+5

A.

Entity Framework, LINQ .. Entity Framework , . Injection Dependency , :

public class MyDao 
{
    IFactory<MyDataContext> _contextFactory;
    public MyDao(IFactory<MyDataContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }

    public Foo GetFooById(int fooId)
    {
        using (var context = _contextFactory.Get())
        {
            return context.Foos.Single(f => f.FooId == fooId);
        }
    }
}

, - , , , , , new MyDataContext().

+2

. (null) , (singleton, ) , DAL ( , @Nicholas Carey). , .

, , . , , using .

+1

More articles:


All Articles