Should DAL always return business objects in MVP

Question

1. In Model View Presenter (MVP) pattern, it is said that our DAL always should returns business models. But let's say if I want to get just a number from database like the last ClientID (the latest clients ID) which is a string, should my DAL method returns a ClientInfo object which has about 10 other fields as well like ClientName, Address etc.?

2. If I want to get a list of business objects from my DAL, is it acceptable to do it as follows or better to get a DataTable from DAL and then in the applications BLL, converts it a List?

   public List<Employee>  GetNewEmployees()
   {
       string selectStatement = "SELECT Employee.Emp_ID, Employee.Initials + ' ' + Employee.Surname AS Name,...";
   
       using (SqlConnection sqlConnection = new SqlConnection(db.GetConnectionString))
       {
           using (SqlCommand sqlCommand = new SqlCommand(selectStatement, sqlConnection))
           {
               sqlConnection.Open();
               using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
               {
                   List<Employee> list = new List<Employee>();
                   while (dataReader.Read())
                   {
                       list.Add (
                       new EpfEtfMaster { 
                           EmployeeID = (int) dataReader ["emp_id"],
                           EmployeeName = (string) dataReader ["Name"],
                           AppointmentDate = (DateTime) dataReader["appointment_date"],                                   
                       });                            
                   }
                   return list;
               }
           }
       }
   }
   

Answer 1

This is a bit of an opinion based question, but ill go ahead and answer because i think its important.

1. If you have multiple accesses to your DB just to query your ClientID, then it is valid IMO not to allocate a whole ClientInfo object as it is redundant and there is no need for the whole extra allocation when you only need a int/string. Although if you do see yourself starting to add more than 1 property (Lets say you need to get a ClientID and an Address) then you should transform your return value to a ClientInfo. It really depends on the scenario.

2. Again, this is MO. I would let my DB return a DataTable as i dont want my Data Access Layer to have anything to do with my Data Object Model. If one day i desire to change it, i dont want it to be a cross cutting concern and refactor every part of the program. Instead, i would let an intermediate class take care of transforming the DataTable to my DOM. That way, only one place has to know a change has been made and i only need to refactor one place in my code. I would strongly suggest looking into AutoMapper as that intermediate layer for your application.