How can I display stored procedure results in my MVC view?

Question

I can't find any updated answers that work with Visual Studio 2017. I've been stuck for over a day trying to figure it out and every guide I find online is either extremely dated or anything that I try to apply to my code give me errors I can't fix. I am using C#, HTML5, ASP.NET MVC5, and VS2017

First off I have my controller which connects to the SQL Server database with:

public void GetStoredProc()
{
    Exclusion objExclusion = new Exclusion();

    string StrConnectionString = ConfigurationManager.ConnectionStrings["EligibilityContext"].ConnectionString;

    SqlConnection sqlConnection1 = new SqlConnection(StrConnectionString);

    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "[advantage].[GetAdvantageEligibilityDataOverride]";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();

    SqlDataReader reader = cmd.ExecuteReader();

    if (reader.HasRows)
    {
        int count = reader.FieldCount;

        while (reader.Read())
        {
            if (count > 3)
            {
                int IntNum;
                int.TryParse(reader.GetValue(0).ToString(), out IntNum);
                objExclusion.PolicyNo = IntNum;
                objExclusion.PolicyMod = (reader.GetValue(1).ToString());
                objExclusion.InsuredName = (reader.GetValue(2).ToString());
                objExclusion.ClientID = IntNum;                   
                //Console.WriteLine(reader.GetValue(i));
            }
        }
    }

    // Data is accessible through the DataReader object here.
    sqlConnection1.Close();
}

Here is my index.html page. I know there needs to be code added here in order to display the web page but I'm a programming noob and am at a loss for what exactly to put.

@model IEnumerable<Advantage_Exclusions_Eligibility.Models.Exclusion>

@{
ViewBag.Title = "Index";
}

<h2>AEE List</h2>

<p>
@Html.ActionLink("Create New", "Create")
</p>
<p>
&nbsp;</p>

<div>
<table class="table">
<tr>
    <th>
        @Html.DisplayNameFor(model => model.PolicyNo)
    </th>
    <th>
        @Html.DisplayNameFor(model => model.PolicyMod)
    </th>
    <th>
        @Html.DisplayNameFor(model => model.InsuredName)
    </th>
    <th>
        @Html.DisplayNameFor(model => model.ClientID)
    </th>
    <th></th>
</tr>

@foreach (var item in Model)
{
<tr>
    <td>
        @Html.DisplayFor(modelItem => item.PolicyNo)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.PolicyMod)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.InsuredName)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.ClientID)
    </td>
    <td>
        @Html.ActionLink("Edit", "Edit", new { id=item.PolicyNo }) |
        @Html.ActionLink("Details", "Details", new { id=item.PolicyNo }) |
        @Html.ActionLink("Delete", "Delete", new { id=item.PolicyNo })
    </td>
</tr>
}
</table>
    <div class="pagination">
        <nav id="AEEPagination" aria-label="Page navigation" 
style="display:none;">
            <ul class="pagination pagination-sm"></ul>
        </nav>
    </div>
</div>

Any and all help would be greatly appreciated!

Answer 1

Your method public void GetStoredProc() will either need to return a model @model IEnumerable<Advantage_Exclusions_Eligibility.Models.Exclusion> or take one in as an argument and populate the model with the result from your stored procedure.

An abbreviated example might look like this:

// Your url, eg. /<controller>/exclusions
public ActionResult Exclusions()
{
    var viewmodel = GetStoredProc();

    // pass your IEnumerable<Exclusion> to the view
    return View(viewmodel);
}

// Note the return type
public IEnumerable<Exclusion> GetStoredProc()
{
    var exclusions = new List<Exclusion>();


    // ... ...

    if (reader.HasRows)
    {
        int count = reader.FieldCount;

        while (reader.Read())
        {
            Exclusion objExclusion = new Exclusion(); // create a new exclusion for each row!
            if (count > 3)
            {
                int IntNum;
                int.TryParse(reader.GetValue(0).ToString(), out IntNum);
                objExclusion.PolicyNo = IntNum;
                objExclusion.PolicyMod = (reader.GetValue(1).ToString());
                objExclusion.InsuredName = (reader.GetValue(2).ToString());
                objExclusion.ClientID = IntNum;                   
                exclusions.Add(objExclusion); // add the exclusion to your result set!
            }
        }
    }

    // Data is accessible through the DataReader object here.
    sqlConnection1.Close();

    return exclusions;
}

In your view, you'll also need to make some tweaks. In this context your model is an IEnumerable of models, and therefore there is no model.PolicyNo, model.PolicyMod, etc.like is being used in the table header. Assuming your Exclusion model class already has the display name properties set, you can just change the header of your table to use those properties of the first Exclusion model in the collection.

<th>
    @Html.DisplayNameFor(model => model.First().PolicyNo)
</th>
<th>
    @Html.DisplayNameFor(model => model.First().PolicyMod)
</th>
<th>
    @Html.DisplayNameFor(model => model.First().InsuredName)
</th>
<th>
    @Html.DisplayNameFor(model => model.First().ClientID)
</th>
<th></th>

I'd also recommend putting some checks around the table to make sure there's data to show:

@if (model.Any()) {
    // Show the table
     <th>
        @Html.DisplayNameFor(model => model.First().PolicyNo)
    </th>
    <th>
        @Html.DisplayNameFor(model => model.First().PolicyMod)
    </th>
    // ... 
}

A side note, it's good practice to wrap SQL connections in a using. I grabbed the first explanation I could find here. You can even chain your SqlCommand and SqlConnection usings together:

eg.

using (var conn = new SqlConnection(connectionString)) 
using (var cmd = new SqlCommand())
{
    // Use conn and cmd here.
}

Answer 2

I worked with the similar kind of code earlier. First create an action in the Controller. Then run the stored procedure using ADO.net. You can pass parameters to your stored procedure using SqlParameter[].

Controller code:

public ActionResult MyResult()
{
    string userid = User.Identity.GetUserId();
    string constr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("My_Result", conn))
        {
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;

            DataSet ds = new DataSet();
            SqlParameter[] prms = new SqlParameter[1];
            prms[0] = new SqlParameter("@Userid", SqlDbType.VarChar, 500);
            prms[0].Value = userid;
            cmd.Parameters.AddRange(prms);
            SqlDataAdapter ad = new SqlDataAdapter(cmd);
            ad.Fill(ds);

            conn.Close();

            return View(ds);
        }
    }
}

With this you stored all the records retrieved from stored procedure into a DataSet. Now return view along with the dataset data.

Now in the View, You need to display these records into a table.

View Code

@using System.Data
@model DataSet

@{
    ViewBag.Title = "MyResult";
}

<h2 style="text-align:center;">Result History</h2>

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>

    <style>
        table{
            width:75%;
            text-align:center;
        }
        th,td,tr{
            border:1px solid black;
            text-align:center;
            padding:20px;
        }
    </style>

</head>
<body>
    <table>
        <tr>

            <th>UserName</th>
            <th>ExamDate</th>
            <th>Score</th>
        </tr>
        @foreach (DataRow row in Model.Tables[0].Rows)
        {
            <tr>

                <td>@row["UserName"]</td>
                <td>@row["ExamDate"]</td>
                <td>@row["Score"]</td>
            </tr>
        }
    </table>
</body>
</html>

Now all the data that is retrieved from stored procedure will be displayed into the table in View.

Answer 3

We can also simplify the view in the following way..!

   @using System.Data;
   @model DataSet
   <center>
   <h2>Result History</h2>
   <table class="table table-bordered table-responsive table-hover">
        <tr>
            <th>UserName</th>
            <th>ExamDate</th>
            <th>Result</th>
        </tr>
    @foreach(DataRow d in Model.Tables[0].Rows)
    {
   <tr>
    <td>@d["UserName"]</td>
    <td>@d["ExamDate"]</td>
    <td>@d["Result"]</td>
   </tr>
 }
 </table>
</center>`