DataAccess layer to return domain objects in case of Ado.net?

Question

I am creating a project in which I will using ADO.NET as data access layer to interact with database.

Now the thing where i am pretty much confused is with :

1) Shall I have domain objects in this application?

2) Does my sql query result should always be binded with domain objects ?

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

4) If I use domain models and if there is a scenario where i want to display data from multiple tables or scenario like this for eg :

public class Employee
   {
      int id;
      List<Skills>();
   }

I can easily do this in EF but with ado.net and with domain object which would have structure like below how I will achieve this :

public class Employee
   {
       int id;
   }

   public class Skill
   {
       int id;
       int EmployeeId;
   }

Ofcouse I can first get List of employee and then for each employee i can get list of skills based on employee id but isnt this will be painfull that i will have to fire query for each employee to get its corresponding skills which is quite simple in EF based on navigation property and avoiding overhead something like below :

var employees = //get listof employee in this case domain model 
                  List<Employee>
var employeeModel = new List<EmployeeModel>();
foreach(var employee in employees)
{
   EmployeeModel model = new EmployeeModel();
   model.id = employee.id;
   var skills = GetSkill(employee.id);//Get list of skills in this case 
                domain model List<Skill>;
   employeeModel.Skills =  new List<SkillModel>();
   foreach(var skill in skills)
   {
      SkillModel sm = new SkillModel();
      sm.Id = skill.Id;
      employeeModel.Skills.Add(smm);
   }
   employeeModel.Add(model);
}

Finally this EmployeeModel will be returned as response in my Web Api service hence this EmployeeModel will hold only those properties that I will return in my WebApi endpoint.

What should be the architecture that are being considered while working with ado.net as data access layer and I will really appreciate if someone could please help me address above 4 concerns.

Note : I do not want to use ORM(Entity Framework or Dapper etc).

"I do not want to use ORM" - I hate to say it, but what you describe is exactly why ORMs (EF etc) and micro-ORMs (dapper etc) **exist**. This reads a lot like "I want to put nails into wood; note: I do not want to use a hammer" - sure, you can do it all manually, but the fact that it is a lot of error-prone work is *why there are libraries to do it for you* — Marc Gravell, Dec 12 '18 at 11:46
@MarcGravell I completely agree with you.But once I have seen 1 project which was using Ado.net and there was a part where code was written like that what I have mention in point 4.So why it is so much different in Ado.net and EF w.r.t to Point 4.Just trying to understand for my knowledge if you could please share your valuable insights on that — I Love Stackoverflow, Dec 12 '18 at 11:50
SQL server supports OO objects in the form of JSON, you can return a table as rows of JSON. then in SQL you can directly work with object graphs. https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-graph-objects-sql-server-2017-good-bad/ — Walter Verhoeven, Dec 21 '18 at 10:07

Vitaliy Fedorchenko · Answer 1 · 2018-12-14T15:56:38.773

1) Shall I have domain objects in this application?

only you can answer on this; right answer highly depends on what you do with database query results. If you have some fields-specific logic in your C# code it has sense to use DTOs (POCO entity classes). But sometimes you might want just execute some query and return results as JSON, and in this case DTOs may be overkill.

2) Does my sql query result should always be binded with domain objects ?

no, this is up to you. You can use DbDataReader directly to handle query results, or load them into DataTable (or similar more lightweight structure that can be offered by your data access library).

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

If you don't use POCO models you can compose JSON in your code and return it as ActionResult.

4) If I use domain models and if there is a scenario where i want to display data from multiple tables

If you don't use ORM like EF 2 possible cases here:

if this is result of 2 (or more) joined tables: for this purpose you can add special POCO model exactly for this query result, or use generic data container like DataTable to handle the result
if this is parent-child (1-n): execute 2 queries and get result as 2 POCO-model collections (say, List<Employee> and List<Skills> of these employees). Then, if you need to access childs of concrete 'parent' entity perform additional filtering with LINQ.

If you don't want to use EF or Dapper, you can also check my library that can be used either with POCO models or with generic structures like DataTable (in addition to that it offers RecordSet structure).

Thank you so much for addressing each of the points mention mentioned in the question but actually I saw a video where author said that if my flow is like this : Presentation --> Business Layer ---> Data access layer than its a very bad practice because here business layer is tightly coupled with data access layer which should be avoid.So now if my data access layer would just return data to business layer which in turn will return to presentation than this could break my test cases due to business layer dependency on data access layer.Right? — I Love Stackoverflow, Dec 14 '18 at 13:44
@MrBean 3-layers architecture is not a silver bullet; it can be suitable in some cases, but it is a lot of other situations when n-layered (including 2-layered) approach is more appropriate. If you don't need a business layer, you can use data-layer components directly from your controllers (say, in case of ASP.NET app). — Vitaliy Fedorchenko, Dec 14 '18 at 15:55

score 2 · Answer 2 · answered Dec 12 '18 at 11:42

2

Entity Framework and lazy loading of referenced objects is a reasonable approach. To extend your model, consider the following.

Create a SQL Server View that links the Employee and Skills table and add this to the EF model. The view can be as minimal or maximal as you want (e.g. minimal as in just the object ID's or maximal as in all fields).

From this view, select all records with a particular skill ID and you now have the Employees you require. Since the view is compiled in SQL Server it will be as quick as you can make it.

You can also join the Employee and Skill objects in your code and return just the employees. In LINQ you can view the SQL generated for your code

answered Dec 12 '18 at 11:42

Does that mean my data access layer will be working with Custom models and not with domain objects? – I Love Stackoverflow Dec 12 '18 at 11:46
Yes. You can always convert the custom model into a domain model if you want. There are plenty of libraries such as https://automapper.org/ although I tend to write mine in manual code – Dec 12 '18 at 11:56
But what will happen if i want data from 2 different tables then as because it wont be possible in case of domain objects as domain objects are tightly coupled with database tables right – I Love Stackoverflow Dec 12 '18 at 11:58
If you use the SQL Server VIEW method you can create your objects from that as it has all of the data you need if you include all fields from both table – Dec 12 '18 at 13:02

score 2 · Answer 3 · answered Dec 20 '18 at 05:48

Creating Domain Model in application is good approach. You just need to bind the data to the models. Create models as per database table. If any table have reference to other include its reference in the model as well it will make easy for you to map the data to model. I would recommend you to use micro ORM it will help you in mapping data to the models else you would need to map you self.

score 2 · Answer 4 · answered Dec 20 '18 at 17:42

I will try to give more specific answers which should give you practical guidelines.

1) Shall I have domain objects in this application?

It depends. For very simple applications you don't need it. For more complex applications, it's absolutely recommended.

2) Does my sql query result should always be binded with domain objects ?

Yes if you are going to have the domain model. No if you don't have a domain model or if you are trying to return a simple value (e.g. integer).

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

If you don't have the domain model, then your DAL becomes direct servant of the web api service. So, whatever web api needs, it needs to get from DAL. You can return all that in one go (one method call), or you can have api call DAL through several method invocations. Obviously, retrieving everything in one go is better, but that better fits into the service layer. DAL should be returning aggregate at a time. But without domain model and more advanced approaches, this can be too much to think about.

4) If I use domain models and if there is a scenario where i want to display data from multiple tables or scenario like this

I would run a SQL query with join. Then I would iterate over the rows and create the parent and child objects from rows, and then I would return the object that way. In fact, that's what Entity Framework does with navigation properties - it eager loads the whole structure using join. It does not execute several queries, and you should not either, if you want to optimize it to the max.

Let me know if you have questions about anything that I mentioned.

HojjatK · Answer 5 · 2018-12-21T09:14:21.237

I agree with Tengiz answer. However, You can use Active Record pattern and if by domain object you mean you would like to use a domain driven design approach then your DAL should abstract the data access logic inside and you better to use Repository pattern for each domain aggregate object. Also, you can separate your models to read and write then you can use CQRS pattern.

In any case you need to run your sql commands/stored procedures in your Data access objects and then populate your domain objects from the ADO.NET datareader, datasets, datatable and expose them to your domain model.

As a side note, DTO as its name stands is used to communicate with your internal domain and it is a good practice to separate your domain objects from DTOs (Data Contracts).

score 0 · Answer 6 · answered Jan 20 '19 at 08:47

I just wanted to provide you with another alternative for scenario 4.

4) If I use domain models and if there is a scenario where i want to display data from >multiple tables or scenario like this

Take a look at the answer I provided in the following question: Loading related objects in memory (without an ORM)

I'm using anonymous types and LINQ to map the data for a query across multiple tables.

DataAccess layer to return domain objects in case of Ado.net?

6 Answers6