Running custom queries in ASP.NET MVC

Question

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:

Where do I write the custom query code? In the model?
How do I write it? Is it through some sort of db.execute statements?
Are there any configuration changes I need to make?
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.

you can use SQLDataAdapter and you can excute customized query. msdn.microsoft.com/en-us/library/… — ram2013
– ram2013, Commented May 31, 2013 at 17:37
There are several ways to do this. Read up on ADO.NET and The Entity Framework to get you started. — Brian
– Brian, Commented May 31, 2013 at 17:41

Ant P · Accepted Answer · 2013-05-31 17:58:01Z

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.

Icarus · Accepted Answer · 2013-05-31 18:05:19Z

2

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);
        }
}

answered May 31, 2013 at 18:05

Icarus

64k14 gold badges102 silver badges116 bronze badges

4 Comments

de1337ed Over a year ago

If you remove this folder, do you not have classes that represent the data? I.E. in your DAL, do you create the table manually using a sql query?

Icarus Over a year ago

@de1337ed If you use Entity Framework, the LINQ queries that you write will return IEnumerable<T> (Collections) where the T is a POCO object with the exact same properties as you define in your LINQ query. For example this code in your DAL: var query = from c in db.Users where c.ZipCode==1234 select c; will return a IEnumerable<User> to the Business Layer, the Business Layer takes this collection from the DAL and maps it to Business Objects. I use AutoMapper to do this mapping of DAL objects to BO objects easily. github.com/AutoMapper/AutoMapper/wiki/Getting-started

Icarus Over a year ago

@de1337ed In the end, all your ASP.NET MVC project sees is the Business Objects from your BL and not the objects returned by the DAL. Remember, the ASP.NET MVC does not have a reference to the DAL at all. I encourage you to look at AutoMapper. Once you set up the mappings you are done writing repetitive code to map objects from the DAL to the Business Layer and vice versa.

Ant P Over a year ago

The 'Models' folder isn't for business model entities; it's for view models. Which you should be using.

Collectives™ on Stack Overflow

Running custom queries in ASP.NET MVC

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related