I'm Using MVC 3 and I'm having trouble using Entity Framework so I'm trying to understand what would be the best approach to implement my own DAL. I manage several main entities in my system: User, Department, Calendar etc... I'm trying to understand the best practice using this kind of tiered architecture.
Should the DAL implement methods that only return DataTables or DataSets or should it be familiar with the model\ business object (User, Department Calendar etc..)?
Should it hold the classes representing the different models\ business object ?
Where should I place the different repository classes are they part of the DAL as well?
1) Should the DAL implement methods that only return DataTables or DataSets
Absolutely not. DataTables and DataSets are artifacts of the past. DAL methods should take/return your DAL entities. For example if you are using Entity Framework those would be the autogenerated classes that EF created for you. Or if you are using EF Code First those would be the classes you wrote to map to your SQL tables
2) Should it hold the classes representing the different models\ business object ?
As explained in 1) the DAL layer should contain the entities that are mapped to your SQL tables as well as the implementation of the repository interface. The repository interface defines the operations with those entities. Inside the DAL layer you will implement this interface for Entity Framework (if this is what you intend to use). Inside the methods you will use the DataContext to perform the different operations with your entities.
3) Where should I place the different repository classes are they part of the DAL as well?
You should place them in the same assembly as your data access classes.
The ASP.NET MVC application will then consume the DAL layer. Your Controllers will simply take the repository interface as constructor parameter and inside the actions you will call various methods on it. You will then configure the Dependency Injection framework of your choice to inject the specific implementation of this repository interface into the controller. This implementation will be the one specific to Entity Framework.
But no matter what you do, don't forget to define view models inside the ASp.NET MVC application itself. Those could be placed inside the Models folder. The view models are the classes that you will be passing to your views. A typical controller action will use the repository to fetch one or more domain entities, map those entities to a single view model that you have defined for the particular view and finally pass the view model to the view. Of course this works the other way around: a controller action takes a view model as action parameter from a view, maps this view model to one or more domain entities and calls one or more methods from the repository passing those domain entities to them.