Data Access Layer and Business Layer collaboration in 3-tier application RRS feed

  • Question

  • Hi all!

    I have a classic 3-tier application:
    Data Access Layer (DAL)
    Business Layer (BL)
    Presentation Layer (PL)

    I have an operation that to be perform several queries in one transaction.
    AND - it's important - I cannot create t-sql stored procedure and put those queries in it, because I want to write my logic in c# (which is better for complex logic than t-sql)

    But, the question arises: which way should I follow?

    1. To have small discreet (separate) methods (queries) in DAL and then invoke them from BL, providing appropriate business logic.

    2. To have a method in DAL that invokes those queries according needed complex business logic and let BL just invoke this DAL method.

    Drawbacks of 1:
    I have to manage the transaction work from BL. I think it's not correct.

    Drawbacks of 2:
    The method in BL just redirects the main work to DAL. Also, DAL get more complicated which is not correct as well.

    The question:
    What approach is correct? Are there any other approaches?

    Tuesday, October 27, 2009 10:40 AM

All replies

  • Hello,

    I prefer method 1 with but with some modifications:
    Usually it is common practice to add Service Layer. Service Layer's purpose is to protect BL from such staff as security, logical transactions, remote calls etc.

    In you Service Layer you can define such term as Logical Transaction. Therefore from BL you can initiate logical transactions whenever your BL wants a set of operations to be treated as one operation. And within Service Layer you can internally use TransactionScope.

    Vitaliy Liptchinsky
    Tuesday, October 27, 2009 11:03 AM
  • Hi,

    1st option looks ok. if you cannot create an additional service layer, try to create and business facade in the business layer and manager the transacations from there.

    Even if you want to do all the business logic in the business layer , you can have th queries as SP's but all they do will return the data from the database. Based on the data recived you try to orchestrate your business in the business facade and implemention in the business logic layer


    Thanks and Regards Azhar Amir
    Tuesday, October 27, 2009 11:26 AM
  • Vitaliy, thanks for your reply!

    Could you provide me some references on what Service Layer could be?

    Do I understand correct? In my BL NOT to create e.g. SqlTransaction dirrectly but call DAL.StartTransaction instead. Right?
    Tuesday, October 27, 2009 12:03 PM
  • You can get more info on Service Layer here:

    Yes, in BL you can either call
    Vitaliy Liptchinsky
    Tuesday, October 27, 2009 1:12 PM
  • Hi,

    Cant you also look at writing your complex logic in .NET and registering that as an external assembly in SQL Server? This means you can call that function from your DAL and the BL can control how the invocation is done.

    Thursday, October 29, 2009 2:49 AM