Where queries should be placed in ASP.NET MVC

Question

I am using ASP.NET MVC, building an ERP with Framework 4.0 and SQL Server 2008.
My question is where should query be placed? I saw multiple methods while surfing. Few of them are as follows:

In Models
In Separate Files, that are in DAL Folder
In Separate DAL Project

I am still confused about using queries in Models after research.

Further Info:
I am using simple query method, not Linq and Entity Framework.
I have a plan to also convert this project to Desktop Application in future.

Another Question is that How business Logic works in ASP.NET MVC if I use it?

Detail About Project
It is a Website + Online Information System of a Company with around 40-50 pages of both.
User can enter in Info System by Signing in from website.

Before Voting to Close Leave a comment so that I can learn something.

You're trying to choose an architecture and it cannot be answered unless you provide much more info about your application, requirements, etc. — VahidNaderi, Sep 04 '13 at 16:04

Abbas Amiri · Accepted Answer · 2015-09-18T09:44:13.797

Your question is about infrastructure of a project that depends on lots of factors.

Considering the information you have provided in your question:

I have a plan to also convert this project in Desktop Application in future.

for the sake of usability, you should have your Data Access Layer in a separate project.

If you are interested in obtaining more information about the infrastructure of a project, I recommend to you: Microsoft Spain - Domain Oriented N-Layered .NET 4.0 Sample App that is well documented and gives you much information that you need to produce an enterprise level application.

score 4 · Answer 2 · answered Sep 04 '13 at 16:16

4

The idea behind MVC is SEPARATION. I don't think very much IF ANY logic or data access should go in the controllers at all. Most sites and applications that I create end up using an API project so it makes it pretty easy to throw the DB access in there. It's really up to you in the end and comes down to one question: Do I want an organized solution?

It's a touchy subject with most, but I've found through my experience that having a more organized Solution/Project will make for a better practice later. Since you plan to convert to a desktop application later, I would highly suggest moving the DAL to a separate project using an API. This way you only have to write the code once.

answered Sep 04 '13 at 16:16

technicallyjosh

3,511
15
17

is it a routine to keep DataAccess in Model when working on small project – syed mohsin Sep 05 '13 at 03:39
You said "logic or data access shouldn't go in the controllers" I want to know about Models. – syed mohsin Sep 05 '13 at 03:45
@syedmohsin I would avoid putting data access in the models. They are generally meant to be responsible for *representation* of the data, not responsible for getting that data or sending it. – technicallyjosh Sep 05 '13 at 16:11

score 2 · Answer 3 · answered Sep 04 '13 at 16:06

I think there is no mustdo rule. Everyone has his own approach. In my applications, I'm creating Helpers folder with static helper classes. For example, create class DBLogic:

public static class DBLogic
{
   public static List<Clients> GetAllClient()
   {
     //**your query here
      return //**some result
    }
}

Than in your Controller, when you need list of Clients

public ActionResult Index()
{
  var clients = DBLogic.GetAllClient()

  return View(clients);
}

This is my approach. Enough logical as for me.

score 1 · Answer 4 · answered Sep 04 '13 at 16:08

In all my jobs so far we create a seperate DAL project to put all our database calls in. But I mean I think its really just for organization and keeping things seperated. If its a smaller project I think just putting it in a seperate class would be fine. Im not sure about in the model though, doenst make sense to me.

score 1 · Answer 5 · answered Sep 04 '13 at 19:15

1

You can create a DAL folder Or you can create a separate DAL Project to write your Data Access Code

answered Sep 04 '13 at 19:15

ali

182
2
2
10

score 1 · Answer 6 · answered Sep 04 '13 at 21:12

Lets say I am in shopping cart scenario which involves following steps:

Get Cart from database
Calculate tax
Total everything

Project.Core(Has all domain classes)

public CartItem
{
   public int Id { get; set; }
   public string Desc { get; set; }
}

public CartDetails
{
   public int Id { get; set; }
   public List<CartItem> CartItems { get; set; }
   public Decimal Tax { get; set; }
   public Decimal Total { get; set; }
}

Project.DAL

public class CartDAL  
{
  public List<CartItem> GetCart(int cartId)
  {
     //execute sql here, get datareader or datatable
     //loop thru the rows and mait into a List<CartItem>

     return List<CartItem>
  }
}

Look into Dapper or Massive, instead of manually like above.

http://code.google.com/p/dapper-dot-net/

https://github.com/robconery/massive

Project.BLL

public class CartBLL
{     
  CartDAL cartDAL = new CartDAL();
  public CartDetails GetCartDetails(int cartId)
  {
     var cartDetails = new CartDetails();
     cartDetails.CartItems = cartDAL.GetCart(cartId);
     cartDetails.Tax = cartDAL.GetCart(cartId);
     cartDetails.Total = cartDAL.GetCart(cartId); 
     return cartDetails;
  }
}

Project.Web

public class CartController
{
   CartBLL cartBLL = new CartBLL();
   public ActionResult Index(int Id)
   {
       var cartDetails = cartBLL.GetCartDetails(Id);

       // Make a cartViewModel

       View(cartViewModel);
   }
}

Project.WPF

//Nothing much changes in desktop comapared to Web, just call the BLL stuff and you are set
CartBLL cartBLL = new CartBLL();
var cartDetails = cartBLL.GetCartDetails(Id);
//Populate view

So, basically you have to reuse your BLL in all the front end projects

BLL shouldn't reference the DAL. Normally I define the business entities in the BLL (they belong there in my opinion), and reference the BLL from the DAL. The BLL also contains interfaces to abstract a repository/context/..., and the DAL implements one, and glue them together using some DI. — Maarten, Sep 05 '13 at 11:22
http://stackoverflow.com/questions/12214371/use-of-bal-in-3-tier-architecturehow-to-call-methods-from-dal-to-bal/12214997#12214997 — Maarten, Sep 05 '13 at 11:27
I agree, I didn't want to confuse the OP with complex issues like DI when he seems to be just starting out. — sunil, Sep 05 '13 at 15:02

Where queries should be placed in ASP.NET MVC

6 Answers6