Is it okay to convert a result dataset in database layer

Question

I'm working on an ASP.NET MVC project in C# with an N-tier architecture.

A class (EmployeeRepo) in the database project has many functions.
Each function's natural result from the transaction is a dataset, but they are converted as per the need.

The return types are:

• Dataset (with one or more tables)
• DataTable
• int
• bool
• Object (single employee)
• List<Object> (list of employees)

Which is better among the two or is there a standard to follow:

1. Return the result as is without any conversion. The conversion should only happen at other layer and not in the database layer.
2. It is okay to convert the result in this layer before returning.

Answer 1

Return the result as is without any conversion. The conversion should only happen at other layer and not in > the database layer.

In my view, repository layer should only do one thing. And this thing is to query database without applying any business logic and return result. Why? There are some reasones such as:

• it complies with Single Responsibility Principle of SOLID principles.
• code becomes simpler
• code becomes more reusable
• class will have lesser size
• easier to write tests

So all conversions are made in service layer.

In addition, it is not good idea to return Dataset (with one or more tables) or DataTable from repository. It is better to return IEnumerable<T>. I mean you should avoid to return IQueryable or DataTable from repository to avoid querying at service layer. There are many opinions about this. So you can choose what is better for you. Should Repositories return IQueryable? imho, repository should not return IQueryable as all query logic should be placed in one place and not scattered across services.

More over, I hihly recommend you to read this post about is it better to return the most specific or most general type from an action method?