So I've been creating a data access layer via TDD and have approached somewhat of a concern. I'd rather not start down the wrong path, so I figured I'd ask you guys to see if my thoughts were in line with a clean architecture.

The methods within my Data Access Layer (DAL for short), are pretty simple. They are in line with the stored procedures in the database (no other way to call into it to keep things clean), and they contain those same parameters that the procedures do. They then just connect to the database, and return the query result. Here's one example:

public int DeleteRecord(int recordId)

    List<SqlParameter> parameters = new List<SqlParameter>();
    parameters.Add(new SqlParameter { ParameterName = "@RecordId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = recordId});

    return this.ExecuteNonQuery("DeleteRecord", parameters.ToArray());

This works perfectly for this type of method because I am not doing anything meaningful with the result set. I just want to make sure the command worked, so I will return the result of the non-query, which is just the rows affected, and I can verify the logic using that number.

However, say in another DAL method, I want to load a record. My load procedure is going to be executing selects against a bunch of tables and returning a DataSet, but I am battling with whether my DAL should create the Business Objects within the method using the DataSet, or if my Business Objects themselves should just have a Load() method that gets the DataSet from the DAL, and then basically fills itself in.

Doing it through the DAL would result in less logic in the Business Objects (even though this is just select logic, it's still logic), but would crowd the DAL a little bit and make it feel like it really is doing something that it shouldn't be doing.

What do you guys think?

  • Why didn't you use Entity Framework?
    – jfrankcarr
    Commented Feb 27, 2012 at 1:10
  • @jfrankcarr - To be honest, mainly because I'm not as familiar with it as I should be. I would however need to rework my tables and add in the proper foreign keys, etc. so that the Entity Framework would recognize the relationships properly. Just out of curiosity though, if I were using it, would I just do all of the selection using the framework with the Business Objects themselves, or would there still be a decision as to where to put those LINQ queries?
    – user44797
    Commented Feb 27, 2012 at 2:05
  • I'd recommend taking the time to learn EF. It can seem a bit daunting at first, especially when trying to get it to fit with an existing database that has some pre-existing design issues, but it's worth it.
    – jfrankcarr
    Commented Feb 27, 2012 at 14:56
  • You can also look at NHibernate if you want to look into another option.
    – Becca
    Commented Feb 27, 2012 at 16:18
  • @jfrankcarr - I definitely will look into it, but how does it fit in with a multi-tiered data access application? Would the entity framework itself be implemented within the DAL itself, or within another layer or even the Business Objects themselves?
    – user44797
    Commented Feb 28, 2012 at 2:25

4 Answers 4


Your DAL should return your data objects

Ideally your DAL should be a "black box" object, that your application code can use to ask for a data object or manipulate existing data objects. Sometimes there is another layer put between the DAL and the application code called the Repository, which further separates the two layers, although this is not always needed.

In addition, you usually do not want your business objects being able to create themselves. This can cause security holes where someone can use your library, and create a new instance of your object by calling .Load(someId) on it, and it merges together two layers that should be completely separate.

I also don't recommend providing a.Load(DataSet ds) method because if the data set definition changes, you'll have to hunt down the data objects which use that data set and change them. It's easier to keep all your data access code in one place, so if you change the data access query, you should only have to change your DAL layer.

  • No sure how one layer can be depended on to "return the correct object" if the definition of "correct object" is held in another layer.
    – TMN
    Commented Feb 27, 2012 at 16:19
  • @TMN That was badly worded. I changed the wording a bit because you're right, the app code should know what kind of object its asking for.
    – Rachel
    Commented Feb 27, 2012 at 16:22
  • @Rachel - Gotcha. So you would recommend having the DAL return an instance of whatever my Business Object itself would be, correct? I was kind of confused by your wording of "data objects", but I think I understand it. That way, my code could request a Business Object from wherever it needs (not through them themselves), by just calling BusinessObject bo = DAL.LoadRecord(id); - sound right? The logic to map the query to the BO itself would be contained within the DAL, and only there.
    – user44797
    Commented Feb 28, 2012 at 2:28
  • 1
    @Scott That's correct, although I would name the DAL method something like Get instead of Load, like Customer c = DAL.GetCustomer(id);
    – Rachel
    Commented Feb 28, 2012 at 2:49

My method, even prior to LINQ-To-SQL and Entity Framework, was to have an interface and abstract class library that provided a "written contract" for communication between different layers of the app. This is sometimes called an ontology, a definition for a work domain. Anything that passed between layers used this 'contract'.

I don't like the idea of passing raw Dataset objects from the data layer to the business layer. I've seen this result in a number of problems, especially when integrating legacy data sources. It can also make it very difficult for new people coming into a project to understand where data is coming from. Lastly, it requires your business layer to be in the business of handling data directly from the DB, which can lead to complications down the road.

The example code you had looks similar to code I had prior to LINQ. I had a common DB function class that I used inside my DAL objects. The DAL classes would read the data and fit it into the 'contract' objects. Scalar results, like your delete example, would return a value, usually a boolean.

  • 1
    "I don't like the idea of passing raw Dataset objects from the data layer to the business layer." This. A thousand times, this. Commented Feb 27, 2012 at 21:09
  • @jfrankcarr - My DAL actually does implement an interface, and I do plan on having interfaces for everything that transfer data from layer to layer, so I think our pattern ideas match there. So do you recommend I change the methods that are returning the direct result of the ExecuteScalar queries to return values that make more sense to a business layer, such as bool? I think otherwise, this is a very similar answer to Rachel's.
    – user44797
    Commented Feb 28, 2012 at 2:32
  • I typically return a boolean for create/update/delete calls unless I need the number of records affected. For example, I might return an int if a stored proc is processing multiple order lines or something like that.
    – jfrankcarr
    Commented Feb 28, 2012 at 16:56

Your DAL should return a Dataset. That Dataset returned should be the buisness object, there shouldn't be anything you need to do to it other than check that that it has the expected data. If you need to do more with it then you are either trying to do too much in a single stored procedure or not returning the data properly in the stored procedure.


I'd recommend your business objects have a constructor to populate themselves from a result set. This removes the coupling between your DAL and the business layer. If you want to completely isolate the two, create a simple map of column name => value pairs from your result set and pass that to the constructor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.