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).

Answer 1

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).

Answer 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

Answer 3

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.