CODEWITHSUNDEEP
c#asp.net-mvc
de1337ed
de1337ed

Reputation: 3325

Running custom queries in ASP.NET MVC

I'm new to using the ASP.net MVC framework (and C# too). I have more experience using JSP + JSTL MVC framework. I want to know how to run custom queries if I need to do more complex sql. In the JSP framework, I'm used to creating DAOs to query sql tables which doesn't seem to be the case in ASP.net. For ASP.net, I noticed that we use LINQ to do the querying over a DBSet object from a model.

My Questions:

  1. Where do I write the custom query code? In the model?
  2. How do I write it? Is it through some sort of db.execute statements?
  3. Are there any configuration changes I need to make?
  4. Examples and other resources would be nice.

I tried looking through the asp.net MVC tutorials on asp.net, but I couldn't find anything.

Upvotes: 1

Views: 2035

Answers (2)

Icarus
Icarus

Reputation: 63966

In the JSP framework, I'm used to creating DAOs to query sql tables which doesn't seem to be the case in ASP.net.

It is very much the same, you should have a separate DAL project with your DAO objects and reference those from your Business Layer (another separate project). The ASP.NET MVC project itself will add a reference to the Business Layer project which in turn will add a reference to the DAL. That way, you can keep a 3-tier architecture:

UI (ASP.NET MVC project)
          |
          |
    Business Layer (POCO objects (= to POJO), validation, biz logic) - separate project(s), depending on whether you put your POCO objects in the same project or not.
          |
          |
      Data Access Layer (You can use ADO.NET, EF (LINQ), etc.)

An ASP.NET MVC app usually is composed of 3 folders: Models, Views, Controllers but everything that's typically placed in the Models folder is really your Business Objects so I tend to get rid of that folder completely and I am left with just the Views and the Controllers folders. The views are just the html markup, pretty much. The Controllers classes simply call the methods from the business layer and passes the results to the views. So for example, a UserController class will look something like this:

public class UserController : Controller 
{
        public ActionResult Index()
        {
            var allUsers = UserBusLayer.User.GetAll();
            return View(allUsers);
        }
}

Upvotes: 2

Ant P
Ant P

Reputation: 25221

How you execute SQL queries depends entirely on what data access framework you are using. You can write plain SQL with ADO.NET and execute queries in a manner much like that you would expect.

In answer to the second part of your question, this should be done in a data access layer and be completely unrelated to your ASP.NET MVC application itself. Any necessary logic for instantiating and communicating with classes in that abstracted layer should reside in your controller (and, ideally, be loosely coupled from your application via the use of interfaces and/or service layers).

In this sense, yes, it belongs in the model. However, bear in mind that, conceptually, the 'M' in ASP.NET MVC is the domain model (i.e. the aforementioned data access layer), not the view model. The view model is just an independent wrapper that is designed to store relevant data for a given view and should - in most cases - be completely devoid of logic whatsoever.

Upvotes: 2

Related Questions