Using SqlQuery in ASP.NET MVC

Question

I've a query in my controller which fetches two columns from the database and I'm storing it in a variable to return it to the view. I'm displaying the data using table. Following is the code for that:

Controller:

public ActionResult DisplayData(LabourTimeModel ltm)
    {

        LabourTimeEntities db = new LabourTimeEntities();
        string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

        var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();

        return View(data);
    }

View:

@model List<LabourTime.Models.LabourTimeModel>

@{
    ViewBag.Title = "Labour Time - Home";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

    <h2><centre>Display Data</h2><hr />

            <table>
                  <tr>
                     <th colspan="2">Time Spent For Process Item</th>
                  </tr>
                 <tr>
                   <th style="padding:0 15px 0 15px;"> Process Item Name </th>
                      <th style="padding:0 15px 0 15px;"> Time Spent </th>
                   </tr>
                @foreach (LabourTime.Models.LabourTimeModel obj in Model)
                    {
                  <tr>
                   <td style="padding:0 15px 0 15px;">@obj.processItemName.ToString()</td>
                   <td style="padding:0 15px 0 15px;">@obj.TimeSpent</td>
                    </tr>
                    }
           </table>

This gives me the output I want. However, I want to write one more query and pass it to the same view and display one more table like this. How do I achieve this? Any help is appreciated, thanks!

Output that I'm getting now:

Column1     Column2
   A          20
   B          10
   C          45

I want one more table to be displayed below this.

Controller that I tried:

    public ActionResult DisplayData(LabourTimeModel ltm)
    {

        LabourTimeEntities db = new LabourTimeEntities();
        string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

string query1 = "ONE MORE QUERY"

        var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();
        var data1 = db.Database.SqlQuery<LabourTimeModel>(query1).ToList();

        return View(data);
    }

But, I cannot pass two results using return view. I'm stuck here.

You want a [View Model](https://stackoverflow.com/questions/11064316/what-is-viewmodel-in-mvc) — Liam, Sep 14 '18 at 16:05

Romias · Accepted Answer · 2018-09-14T16:09:35.077

2

You need to create view model (a class that has the two lists) and pass that object to the view.

var model = new MyModel {
   Data1 = your first query,
   Data2 = your second query
}

return View(model);

In the view:

@model MyModel

edited Sep 14 '18 at 16:09

answered Sep 14 '18 at 16:05

Romias

13,783
7
56
85

2

You seem to [suffer from the FGIW](https://meta.stackexchange.com/questions/9731/fastest-gun-in-the-west-problem) – Liam Sep 14 '18 at 16:09
@Liam I did't know I had a problem! But yes... that is my behaviour :) – Romias Sep 14 '18 at 16:12
@Romias Hi, thanks for your reply. How do I execute the query if I pass the query to Data1 and Data2? I'm sorry, but I didn't understand. – Sep 14 '18 at 18:30

Arun Kumar · Answer 2 · 2018-09-14T18:50:28.443

0

You can use anonymous types also to send back to your view

   public ActionResult DisplayData(LabourTimeModel ltm)
{

    LabourTimeEntities db = new LabourTimeEntities();
    string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

    string query1 = "ONE MORE QUERY"

    var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();
    var data1 = db.Database.SqlQuery<LabourTimeModel>(query1).ToList();

    dynamic viewModel = new ExpandoObject();
    viewModel.Query1= data;
    viewModel.Query2=data1;
    return View(viewModel);
}

In your view use it like

@Model.Query1.ColumnName

edited Sep 14 '18 at 18:50

answered Sep 14 '18 at 16:09

Arun Kumar

885
5
11

And what you declare in the View as the model? Never tried this approach before... – Romias Sep 14 '18 at 16:25
Just use key names as you use model properties like @Html.TextBox("Query1"). In case of list types you can declare it same way you declare your model and then cast it to appropriate type – Arun Kumar Sep 14 '18 at 16:26
@ArunKumar Thanks for your reply. Does it work this way? I get multiple columns for these queries. How do I implement it on the view? – Sep 14 '18 at 18:31
updated my answer a little to use Expnado object which is a dyamic view model. If you don't want to use this then you need to override the view page default behaviour to set model properties in order to support the anonymous types. – Arun Kumar Sep 14 '18 at 18:52

Using SqlQuery in ASP.NET MVC

2 Answers2