How to design DAL/BAL to support Lazy loading using POCO without using Entity framework

Question

I am using ADO.NET, I have created DAL and BAL, structure is as follows

DAL
   -POCO(Table to Entities mapping)
   -Repository (Add, Delete, Edit, Select, Search)

BAL
   -POCO(DAL to Entities mapping where Entities are complex type here)
   -BusinessManager(Validation and Business rules)

I will be exposing BAL using services. And multiple applications like Web, Window, Mobile will be consuming this BAL.

I am not sure where should Sql Transactions be started. Who is responsible for creating a complex entities.

e.g. Complex entity could be

   Entity
     -Entity Type
     -Contacts
     -EmployeesReporting
     -ReportingTo

Now who should create an Entity? Is it a BAL or DAL. Where should SqlTransactions be started? How should I make these entities to support lazy loading as I am a supporter of Stored procedure and prefers to use one database connection per request

Answer 1

I'm not sure I see how your desire to use one database connection per request fits with your desire to support lazy loading.

The point of lazy loading is that you don't need to materialize certain parts of the object graph when initially loading it, and you may or may not need to load those parts at some later point in program execution.

Reserving a connection somehow to satisfy that need would be very wasteful of a precious resource (DB connections).

Since the object graph is being used in the Business Access Layer, parts of the object graph that were not initially materialized must somehow be represented in a manner that allows you to intercept requests to have them available, and produce them on demand. The EF solution of placing a proxy in the business layer that, in turn, activates the DAL to fetch missing parts of the object graph on demand, is a good one.