ASP.NET MVC C#: Bringing in data from multiple tables/queries into a view

Question

Ok, I'm still getting the hang of asp.net and the MVC framework and converting my knowledge over from classic ASP and VB - so please be gentle.

I've got my first view (/home/details/X) functioning well thanks to previous help pointing me in the right direction, now I need to add data from multiple tables and queries/views to the MVC view (I hate that SQL and MVC both use the word view for different meanings).

I'm not looking for someone to write the answer for me (unless they're feeling really energetic), more so for someone to point me in the right direction of what I should be looking at and reading up on to understand it and do this myself.

My problem

There are multiple datasets which I need to display in this view, and each different data set has a proper PK/FK 1-M relationship established, and the resultant records would need to be looped through.

How I would have done this previously

In my classic ASP days, I would have just defined the SQL query at the head of the page where the data was to be used, with a select statement along the lines of:

SELECT * FROM query_name
WHERE query_uniquecolumnname = Request.QueryString("value")

Once that was done, you'd set the do while query_name NOT BOF/EOF up, then drop in the field names you wanted from that query and it was all done.

How do I acheive this now?

So, fast forwarding from my classic ASP knowledge, how do I acheive the same outcome with MVC?

The tables/views I wish to use are already defined within my data model (and the relationships are showing up in there which I would assume is a plus), I just need to work out how I could call these within the page and use the ID of the record being displayed in the Details view to ensure only related data is displayed.

Thanks in advance

Answer 1

I would recommend reading this primer on ASP.NET MVC

http://weblogs.asp.net/scottgu/archive/2009/04/28/free-asp-net-mvc-nerddinner-tutorial-now-in-html.aspx

It covers most basic scenarios you'll need to get up and running.

If however you want to combine multiple resultsets into one, and then return it as a view, you should create a custom object, and map the resultset to it, then you can bind against your custom object in the view.

Answer 2

The concept you are looking for is called a ViewModel. Essentially this is a custom class that you write that contains all the data that would be used in your view. So it is responsible for amalgamating all the data from the different tables and exposing it as properties. If you're using a data access layer, this is often as simple as bringing a few entities together. If you're using raw SQL to do it, then you would execute your queries when the properties were accessed.

Then you would make your View inherit from the ViewModel, like so:

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master"
 Inherits="System.Web.Mvc.ViewPage<MvcApplication1.Models.MyViewModel>" %>

Now in your View, you can access all the different properties of your object simply by writing statements like:

<%= Html.TextBox("MyProperty", Model.MyProperty) %>

To construct your view from your controller, create a new instance of your class (MyViewModel), pass it the ID of the details record that you need, and the logic in your class will take care of getting the right data. Then return your view from your controller like normal.

   var myDetailsModel = new MyViewModel(detailsID);
   return View(myDetailsModel);

Answer 3

When I need to display multiple things like this on a web page, I use typically use RenderAction to do it.

RenderAction allows you to use a controller method dedicated to that particular part of the view (a subview, in effect), so you can pass a single data set of strongly-typed data to that "subview".

RenderAction is in the Microsoft.Web.Mvc ("futures") assembly.

If you are new at all of this, I apologize; this is a bit bleeding edge, but you're going to need to know it anyway. Be sure to check out the NerdDinner tutorial first.

http://www.andreas-schlapsi.com/2008/11/01/renderaction-and-subcontrollers-in-aspnet-mvc/

http://davidhayden.com/blog/dave/archive/2009/04/04/...

Answer 4

I tried taking the advantage of foreign key relationship that exist between 'Movies' table and 'MovieGenreAssocs' table and another foreign key reln that exist between 'Genres' and 'MovieGenreAssocs' table.

So when i need to display Movies title and different genres they fall into ,I simply used the code below

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<MovieApp.Models.Movie>>" %>
<% foreach (var item in Model) { %>
 <td>
                <%: item.Title %>  
 </td>

   <td>
             <% foreach (var i in item.MovieGenreAssocs)
                { %>
                <%: i.Genre.type%>

                <%} %>
    </td>
<%} %>

Now I am wondering is that a bad practice using kinda query inside view ?