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){    recordId.RequireThat(recordId).NotZeroOrLess();    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?
Business Objects within a Data Access Layer
c#;.net;architecture;database;business logic
Your DAL should return your data objectsIdeally 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.