How to Display Table of Values From Stored Procedure in ASP.NET MVC5 View (Step-by-Step Help Needed)

I hope this is a relatively simple problem to solve.  I have created a stored procedure that takes a single integer parameter and returns a single column of unique datetime values (see attached) which I would like to display  in a table within an MVC5 view.  I'm rather new to this so I could really use a step-by-step walkthrough (using EF Database First rather than EF Code First). I've tried finding tutorials online but have found nothing that I'm able to follow using Visual Studio 2013.  Any help would be appreciated.  Thanks!

List of DateTime Values
LVL 1
penlandtAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Hi,

EF has a means of running an SP and even define the kind of data you expect from it.

Let's suppose you have name your db context as "context". This has a property named "Database", which in itself has a method "SqlQuery", which accepts a string as the sql string you want to run against your db and a string array that will represent the params to send to the sql string.

In your case you could run something like:

var myintparam = new SqlParameter
{
    ParameterName = "myawsomeintegerparam",
    Value = 1
};
var results = context.Database.SqlQuery<DateTime>("dbo.MyCoolStoredProcedure @myawsomeintegerparam").ToList();

Open in new window


You may return the "results" in your View as you would normally do.

Giannis
Aaron JabamaniTechnical ArchitectCommented:
penlandtAuthor Commented:
Thanks for the suggestions so far.  Apeter, I have used that tutorial extensively however (as you may know) it doesn't demonstrate how to model and display data from a stored procedure, which is the objective of this particular question.  jyparask, your answer looks promising however I need to know step-by-step how to get from modeling the data the stored procedure returns (if that's necessary) all the way to displaying it.  Does the code you've provided go in a controller or in the view?  I'm sorry to be asking so much in one question but I really cannot find any guidance for this anywhere else so far.  Any help you can offer, I'd appreciate it.  Thanks.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Ioannis ParaskevopoulosCommented:
Hi,

Here is a step by step guide but I do make the assumption that you know some of the basics. If you need any more clarifications just ask.

1. Add an MVC controller.

2. Add a View.

3. In the controller add the following code.

public ActionResult Index(int id)
{
    var myintparam = new SqlParameter
    {
        ParameterName = "myParam",
        Value = id
    };
    var results = context.Database.SqlQuery<DateTime>("dbo.MyStoredProcedureName @myParam").ToList();
    return View(results);
}

Open in new window

Note that SqlQuery<DateTime> will dictate that the return value is of DateTime. You could define another model if you would need.
I am also assuming you have somehow defined the context variable but that is EF related.

4. In the View add the following code.

@model IEnumerable<DateTime>
@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<table>
@foreach(var date in model)
{
    <tr>
        <td>@date</td>
    </tr>
}
</table>

Open in new window


Now with the default routing you would be able to go to /ControllerName/Index/5 and get the results.

Giannis

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
penlandtAuthor Commented:
Thank you jyparask.  Yes, I *think* I know most of the basics - it's just particular implementations like this that stump me until I can link it up conceptually with what I already know.  Let me see if I can make this work now.  Appreciate it!
penlandtAuthor Commented:
I think I'm just about there.  I'm going to accept your answer because it covers the exact question I asked.  I'm going to have to open a new question though about incorporating this into the Details controller I already have (this list of dates is just one part of the view so I don't want to lose the rest of it while implementing this).  Thanks again for your help!
penlandtAuthor Commented:
Ioannis ParaskevopoulosCommented:
In that case you may need to check out about DisplayTemplates. In short you add a folder under "/Views/Shared" named "DisplayTemplates". There you may define several templates that will accommodate your needs.

Under that newly created folder add a new cshtml file called "MyDateTimes" (or whatever else you like).

In that file go ahead and add the following code:

@model IEnumerable<DateTime>
<table>
@foreach(var date in model)
{
    <tr>
        <td>@date</td>
    </tr>
}
</table>

Open in new window


It is quite similar with the code that i gave you before but lacks the definition of the layout.

Now lets assume you have a model that has several properties and also one property that is going to be filled with the results of the stored procedure. Lets get the following model as an example:

public class Person
{
    public int Id {get;set;}
    public string Name {get;set;}
    public string SurName {get;set;}
    public IEnumerable<DateTime> LoginDates {get;set;}
}

Open in new window


And lets also modify the controller:

public ActionResult Index(int id)
{
    var person = context.Persons.Where(x=>x.Id == id).Select(x=>new Person{x.Id, x.Name, x.Surname}).FirstOrDefault();
    //Notice that i only get the id, name and surname from the table using EF. I will call the procedure to fill in the dates

    if(person!=null)
    {
        var myintparam = new SqlParameter
        {
            ParameterName = "myParam",
            Value = id
        };
        person.LoginDates = context.Database.SqlQuery<DateTime>("dbo.MyStoredProcedureName @myParam").ToList();
    }
    
    return View(person);
}

Open in new window


We also need to change the view:

@model Person
@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<table>
    <tr>
        <td>Person Id : </td><td>@Model.Id</td>
        <td>Person Name : </td><td>@Model.Name</td>
        <td>Person Surname : </td><td>@Model.SurName</td>
    </tr>
</table>

<h1>Login Dates</h1>
@Html.DisplayFor(m => m.LoginDates, "MyDateTimes")

Open in new window


The last line in the view will cause the display template to run so you will end up with a table displaying the dates.

I have not tested this somehow, so there might be some inconsistencies but that is the general idea.

Do not forget to look up for "DisplayTemplates" and also "EditorTemplates" which may help you a lot for creating forms.

Another thing you could do if you do not like the "MyDateTimes" in the "DisplayFor" is use an attribute in the property of the model such as shown next:
public class Person
{
    public int Id {get;set;}
    public string Name {get;set;}
    public string SurName {get;set;}
    [UIHint("MyDateTimes")]
    public IEnumerable<DateTime> LoginDates {get;set;}
}

Open in new window


This way you can keep you view a bit clearer. This will let the engine know that whenever it finds a DisplayFor for the LoginDates of a Person model it will render it as defined in the DisplayTemplates folder in the "MyDateTimes.cshtml" file. In this case you just need to write it in the view as:

@Html.DisplayFor(m => m.LoginDates)

Open in new window


Giannis
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.