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

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

Select allOpen in new window

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

Giannis

Hope this link helps. http://www.asp.net/mvc/overview/getting-started/introduction/getting-started

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.

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!

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!

The continuation of this question is located at https://www.experts-exchange.com/questions/28656390/How-to-Modify-Existing-Controller-and-View-to-Incorporate-Additional-Data-from-Stored-Procedure.html

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>

Select allOpen 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;}
}

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

Select allOpen 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")

Select allOpen 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;}
}

Select allOpen 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)

Select allOpen in new window

Giannis