Link to home
Start Free TrialLog in
Avatar of Todd Penland
Todd PenlandFlag for United States of America

asked on

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!

User generated image
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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
Avatar of Todd Penland

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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