Where should Stored Proc business logic be placed in MVC?

Question

I'm looking for a bit of experience and explanation here, given that different sources give different recommendations. I am totally new to MVC. I know this question has been asked before, but I am not (currently) using EF or Linq.

I have a SQL database with many stored procedures. Previously when used with webforms, there was a business layer that contained helper methods for calling the procedures and returning DataSets to the pages. The important part is that the procedures often interrogated about 20 tables; the pages do not simply reflect the database structure exactly (as I see in the majority of MVC tutorials):

SQL database <--> stored procedures <--> business layer <--> web forms

I want to take the best approach here to start on the right footing and learn properly but appreciate there may not be a correct answer. Therefore if you post, could you please offer some explanation as to "why"?

---

• Should stored procedure logic (SQLCommand/business methods etc) go within Model or Controller?

One post advises neither, but retain the business layer. Another expert advises that

[Models/Entities] should not have any addon methods outside of what's coming back from the database

• If the business layer is retained, where are the methods called from (e.g. Model or Controller)?

• If the above answer is "Neither", does that mean the Model part will go unused?

That almost feels that things aren't being done properly, however in this tutorial that appears to be what happens.

• Should I plug in the Entity Framework into the Model layer to call the business layer?

That feels like overkill, adding all that additional logic.

Answer 1

Your controllers should gather the information required to build the page the user is currently viewing. That's it.

Controllers should reference classes in your business logic layer.

For example here's your controller. All it does is translate the http request and call the business logic.

public class MyController : Controller
{
   private IMyBusinessLogic  _businessLogic;
   public MyController(IMyBusinessLogic businessLogic)
   {
      _businessLogic = businessLogic;
   }

   [HttpPost]
   public ActionResult UpdateAllRecords()
   {
      _businessLogic.UpdateAllRecords();
      return Json(new Success());
   }
}

And your business logic class

public class MyBusinessLogic : IMyBusinessLogic  
{
  public void UpdateAllRecords()
  {
    // call SP here
    using(SqlConnection conn = new...
  }
}

There are a number of advantages to this:

• Your business logic is completely separated from your UI, there's no database code in your presentation layer. This means your controller can focus on it's job and code doesn't get polluted.
• You can test your controller and see what happens when your business logic succeeds, throws exceptions etc.

For extra bonus points you should look into creating a data access layer.

public void DataAccess : IDataAccess
{
  public void RunStoredProcedure(string spName)
  {
  }
}

Now you can test that your BLL is calling and processing your SP results correctly!

Expanded following the comment questioning the models: Ideally your model should have no logic in it at all. It should simply represent the data required to build the page. Your object which you're loading represents the entity in the system, the model represents the data which is displayed on the page. This is often substantially lighter and may contain extra information (such as their address) which aren't present on the main entity but are displayed on the page.

For example

public class Person
{
   public int  PersonID {get;set;}
   public string Firstname {get;set;}
   public string Lastname {get;set;}
   public Address Address {get;set;}
}

The model only contains the information you want to display:

public class PersonSummaryModel
{
   public int  PersonID {get;set;}
   public string FullName {get;set;}
}

You then pass your model to your view to display it (perhaps in a list of FullNames in this case). Lots of people us a mapper class to convert between these two, some do it in the controller.

For example

public class PersonMapper
{
  public PersonSummaryModel Map(Person person)
  {
     return new PersonSummaryModel
     {
       PersonID = person.PersonID,
       FullName = string.Concat(person.Firstname, " ", person.Lastname)
     };
  }
}

You can also use some automatic solutions such at AutoMapper to do this step for you.

Answer 2

Your controller should really only be involved with orchestrating view construction. Create a separate class library, called "Data Access Layer" or something less generic, and create a class that handles calling your stored procs, creating objects from the results, etc. There are many opinions on how this should be handled, but perhaps the most

View
|
Controller
|
Business Logic
|
Data Access Layer
|--- SQL (Stored procs)
       -Tables
       -Views
       -etc.
|--- Alternate data sources
       -Web services
       -Text/XML files
       -and son on.

if you feel like learning tiers and best way MSDN have great article on this link MSDN