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

Liam
– Liam

2018-09-14 16:05:41 +00:00
Commented Sep 14, 2018 at 16:05 — Liam
– Liam, Commented Sep 14, 2018 at 16:05
@Liam Thank you!

user8205798
– user8205798

2018-09-14 18:30:21 +00:00
Commented Sep 14, 2018 at 18:30 — user8205798
– user8205798, Commented Sep 14, 2018 at 18:30

Romias · Accepted Answer · 2018-09-14 16:09:35Z

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, 2018 at 16:09

answered Sep 14, 2018 at 16:05

Romias

14.2k7 gold badges61 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Liam Over a year ago

You seem to suffer from the FGIW

Romias Over a year ago

@Liam I did't know I had a problem! But yes... that is my behaviour :)

user8205798 Over a year ago

@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.

Arun Kumar · Accepted Answer · 2018-09-14 18:50:28Z

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, 2018 at 18:50

answered Sep 14, 2018 at 16:09

Arun Kumar

8955 silver badges12 bronze badges

4 Comments

Romias Over a year ago

And what you declare in the View as the model? Never tried this approach before...

Arun Kumar Over a year ago

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

user8205798 Over a year ago

@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?

Arun Kumar Over a year ago

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.

Collectives™ on Stack Overflow

Using SqlQuery in ASP.NET MVC

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related