Displaying the result set of a Db view in asp.net core view

Question

I am executing a stored procedure from my asp.net core app. The procedure executes a select statement from a db view. The db view inner joins 3 tables. When I execute the following code the result set comes as an int throwing an exception as the razor view expects List, I need to receive it as a list in order to pass it to the razor view and display the table. I would appreciate any help.

ViewModel:

public class ViewModel
    {
        public int TimeKey { get; set; }
        public int FiscsalYear { get; set; }
        public string LocationNum { get; set; }
        public string Location { get; set; }
    }

View:

@model List<FactWorkOrdersViewModel>
@{
    ViewBag.Title = "Stored Procedure Test";
}

<div class="container">
    <table class="table table-hover">
        <thead>
            <tr>
                <th colspan="5"><h3>Stored Procedures results</h3></th>

            </tr>
            <tr>
                <th>TimeKey</th>
                <th>Fiscal Year</th>
                <th>Location Number</th>
                <th>Location</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
            <tr>
                <td>
                    @item.TimeKey
                </td>
                <td>
                    @item.WorkOrderAltKey
                </td>
                <td>
                    @item.FiscsalYear
                </td>

                <td>
                    @item.LocationNum
                </td>
                <td>
                    @item.Location
                </td>

            </tr>
            }
        </tbody>
    </table>
<div>

Controller:

public IActionResult SPTest(ReportViewModel model)
    {
        DbConnection connection = db.Database.GetDbConnection();

            using (DbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "ExecuteReport";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ReportId", model.ID));

                if (connection.State.Equals(ConnectionState.Closed))
                {
                    connection.Open();
                }

                var result = cmd.ExecuteScalar();

                //var result = cmd.ExecuteNonQuery();

                if (connection.State.Equals(ConnectionState.Open))
                {
                    connection.Close();
                }
                return View(result);

            }

score 0 · Answer 1 · answered Mar 17 '20 at 15:18

This is a possible duplicate. Please refer to What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery? for more information.

Short answer : You need ExecuteReader not ExecuteScalar. ExecuteScalar returns first column's value of first row. ExecuteReader will return the list of rows which we can iterate through and display on your page.

score 0 · Accepted Answer · answered Mar 17 '20 at 18:14

I figured it out, thanks @Amogh

public IActionResult SPTest(ReportViewModel model)
        {
            List<ViewModel> viewModel = new List<ViewModel>();

            using (SqlConnection conn = new SqlConnection("server=ServerName;database=DBName; user id=user_id; password=password; MultipleActiveResultSets=true"))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("ExecuteReport", conn)
                {
                    CommandType = CommandType.StoredProcedure
                };

                cmd.Parameters.Add(new SqlParameter("@ReportId", model.ID));

                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        foreach (var item in rdr)
                        {
                            ViewModel vm = new ViewModel
                            {
                                TimeKey = (int)rdr.GetValue(0),
                                FiscsalYear = (int)rdr.GetValue(2),
                                LocationNum = (string)rdr.GetValue(5),
                                Location = (string)rdr.GetValue(6)
                            };

                            viewModel.Add(vm);
                        }
                    }
                }
            }

            return View(viewModel);
        }

Displaying the result set of a Db view in asp.net core view

2 Answers2