ASP.NET MVC Design Question - Where to put DB access code?

I've been playing around with ASP.NET MVC for the past few weeks. I've got a simple web application with a form which contains a number of drop down lists.

The items in the drop down lists are stored in a database, and I'm using LINQ to SQL to retrieve them.

My question is - where's the appropriate place to put this code? From what I've read so far, it seems that it's advisible to keep the Controller 'thin', but that's where I currently have this code as it needs to be executed when the page loads.

Where should I be putting DB access code etc.? I've included an excerpt from my controller below.

Thanks.

    public ActionResult Index()
    {
        TranslationRequestModel trm = new TranslationRequestModel();

        // Get the list of supported languages from the DB
        var db = new TransDBDataContext();
        IEnumerable<SelectListItem> languages = db.trans_SupportedLanguages
            .Select(c => new SelectListItem
                {
                    Value = Convert.ToString(c.ID),
                    Text = c.Name.ToString()

                });
        ViewData["SourceLanguages"] = languages;
        ViewData开发者_高级运维["TargetLanguages"] = languages;
        return View();

Your database access code should be in a repository. Example:

public interface ITranslationRepository
{
    Translation GetTransaltion();
}

and the controller would use this repository:

public class TransaltionController : Controller
{
    private readonly ITranslationRepository _repository;
    public TransaltionController(ITranslationRepository repository)
    {
        _repository = repository;
    }

    public ActionResult Index()
    {
        // query the repository to fetch a model
        Translation translation = _repository.GetTransaltion();

        // use AutoMapper to map between the model and the view model
        TranslationViewModel viewModel = Mapper.Map<Translation, TranslationViewModel>(model);

        // pass the view model to the view
        return View(viewModel);
    }
}

So the basic idea is the following:

1. The controller queries a repository to fetch a model
2. The controller maps this model to a view model (AutoMapper is great for this job)
3. The controller passes the view model to the view
4. The view is strongly typed to the view model and uses it to edit/display

As far as the implementation of this repository is concerned feel free to use any data access technology you like (EF, NHibernate, Linq to XML, WCF calls to remote resources over the internet, ...)

There are the following advantages:

1. The controller logic is completely decoupled from the data access logic
2. Your controllers can be unit tested in isolation
3. Your models are not littered with properties that should belong to the UI layer (such as SelectListItem) and thus are reusable across other types of application than ASP.NET MVC.
4. The view model is a class which is specifically tailored to the needs of the view meaning that it will contain specific formatted properties and the view code will be extremely readable.
5. Your views are strongly typed => no more ViewData and ugly magic strings

Suggest that your data-access code should be contained in its own project/assembly. That is referenced by the UI tier (ASP.NET MVC application). That'll help achieve the goal of keeping your Controllers thin, and keep all the data access code out of your MVC UI project.

That typically leads to another question/discussion about domain entities: when mapping to the data store. Some architects like to have the entities in their own separate assembly. This encourages reuse in other applications. Some like to keep the entity model and data access code in the same project/assembly. This is totally up to you and your environment.

For an example, let's say it's a billing application; holding customers, invoices, etc. Your implementation will be different, depending on your data access strategy (an ORM like LINQ To SQL, EF, nHibernate, SubSonic, or plain old ADO.NET, or reading from a flat file).

// Assembly: InvoicingDL
public class CustomerRepo
{
    public IQueryable<Customer> ListCustomers()
    {
        return MyDatabase.Customers(); //however you'd get all your customers
    }    
    //etc
}

// Assembly: InvoicingDL
public class InvoicingRepo
{
    public IQueryable<Invoice> GetCustomerInvoices(int custID)
    {
        return MyDatabase.Invoices.Where(i=>i.CustomerID==custID); 
    }    
    //etc
}

Check out the Repository pattern

https://web.archive.org/web/20110503184234/http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/10/08/the-repository-pattern.aspx

http://www.mindscapehq.com/blog/index.php/2008/05/12/using-the-unit-of-work-per-request-pattern-in-aspnet-mvc/

The idea is you abstract your data access in something called a repository that returns domain objects. Your controller can then use this repository to get the appropriate objects from the database and assign them to the model.