Fetching SQL Server table data in ASP.NET Core Razor pages

Question

I am trying to fetch some SQL data whenever user comes to the Index page in my ASP.NET Core application (using Razor Pages). In my Index.cshtml.cs I am trying to establish connection to SQL Server and storing the rows in a list. Now I want to fetch this list in Index.cshtml. I am not sure about this approach but here is what I tried..

This is my Index.cshtml.cs

namespace SQLICMAlertWebApp.Pages
{
    [Authorize]
    public class IndexModel : PageModel
    {
        public IConfiguration Configuration { get; }

        public IndexModel(IConfiguration configuration)
        {
            Configuration = configuration;
            con.ConnectionString = Configuration.GetConnectionString("OEM_US_SQL_CORL");
        }

        SqlCommand com = new SqlCommand();
        SqlDataReader dr;
        SqlConnection con = new SqlConnection();
        List<ProActive_Monitoring_Missing_PVR_Orders> table_errors = new List<ProActive_Monitoring_Missing_PVR_Orders>();

        public void OnGet()
        {
            if (table_errors.Count > 0)
            {               
                table_errors.Clear();
            }

            try
            {
                con.Open();
                com.Connection = con;
                com.CommandText = "SELECT * FROM ProActiveMonitoring_Missing_PVR_Orders WHERE isICMSent = 'No'";

                dr = com.ExecuteReader();

                while (dr.Read())
                {
                    table_errors.Add(new ProActive_Monitoring_Missing_PVR_Orders()
                    {
                        CustomerNbr = dr["CustomerNbr"].ToString(),
                        CustomerName = dr["CustomerName"].ToString(),
                        MSOrderNumber = dr["MSOrderNumber"].ToString(),
                        Quantity = dr["Quantity"].ToString(),
                        PromoQuantity = dr["PromoQuantity"].ToString(),
                        QtyDiff = dr["QtyDiff"].ToString(),
                        NetAmount = dr["NetAmount"].ToString(),
                        PromoNetAmount = dr["PromoNetAmount"].ToString(),
                        AmtDiff = dr["AmtDiff"].ToString(),
                        ExtendedAmount = dr["ExtendedAmount"].ToString(),
                        PromoExtendedAmount = dr["PromoExtendedAmount"].ToString(),
                        IsICMSent = dr["IsICMSent"].ToString()
                    });
                }

                con.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex);
            }
        }
    }
}

Here is the Index.cshtml to retrieve data from table_errors variable defined above.

@page
@model SQLICMAlertWebApp.Pages.IndexModel
@{
    ViewData["Title"] = "Home";
    string[] TableHeaders = new string[] {"CustomerNbr"
                              ,"CustomerName"
                              ,"MSOrderNumber"
                              ,"Quantity"
                              ,"PromoQuantity"
                              ,"QtyDiff"
                              ,"NetAmount"
                              ,"PromoNetAmount"
                              ,"AmtDiff"
                              ,"ExtendedAmount"
                              ,"PromoExtendedAmount"};
    bool checkEmty = true;
}

<div class="HomePage">
    <div class="myNavbar">
        <div class="header-title">SQL Data Mismatch Tables</div>
        <button class="LogoutButton" onclick={location.replace('/home/logout')}>Logout</button>
    </div>
    <div class="HomePageBody">
    <div class="table">
        <table class="table table-bordered table-hover">
            <thead>
                <tr>
                    <th class="th_table_name" colspan="11">
                        ProActive_Monitoring_Missing_PVR_Orders_Errors
                    </th>
                </tr>
                <tr>
                    @{
                        foreach (var head in TableHeaders)
                        {
                            <th>
                                @head
                            </th>
                        }
                    }
                </tr>
            </thead>

            <tbody>
                @{
                    if (Model != null)
                    {
                        foreach (var Data in table_errors)
                        {
                            checkEmty = false;
                            <tr>
                                <td>@Data.CustomerNbr</td>
                                <td>@Data.CustomerName</td>
                                <td>@Data.MSOrderNumber</td>
                                <td>@Data.Quantity</td>
                                <td>@Data.PromoQuantity</td>
                                <td>@Data.QtyDiff</td>
                                <td>@Data.NetAmount</td>
                                <td>@Data.PromoNetAmount</td>
                                <td>@Data.AmtDiff</td>
                                <td>@Data.ExtendedAmount</td>
                                <td>@Data.PromoExtendedAmount</td>
                            </tr>
                        }
                        if (checkEmty)
                        {
                             <tr>
                                <td colspan="11">No Errors</td>
                            </tr>
                        }
                    }
                }
            </tbody>
        </table>
        </div>
    </div>
</div>

I am not sure if it should work in this way or not, but Index.cshtml is not recognizing table_errors variable. What should I change in my approach if this isn't correct!!

EDIT #2

After changing table_errors to Model.table_errors it worked fine!! But I encountered another error in the code, do you have any idea about this?

Answer 1

Razor Pages uses the MVVM pattern. So if you want to access to an object in View (int this case: Index.cshtml), you should access to object through the Model. For example, to use table_errors in View, do this way: @Model.table_errors

This code will work:

<tbody>
            @{
                if (Model.table_errors != null)
                {
                    foreach (var Data in Model.table_errors)
                    {
                        checkEmty = false;
                        <tr>
                            <td>@Data.CustomerNbr</td>
                            <td>@Data.CustomerName</td>
                            <td>@Data.MSOrderNumber</td>
                            <td>@Data.Quantity</td>
                            <td>@Data.PromoQuantity</td>
                            <td>@Data.QtyDiff</td>
                            <td>@Data.NetAmount</td>
                            <td>@Data.PromoNetAmount</td>
                            <td>@Data.AmtDiff</td>
                            <td>@Data.ExtendedAmount</td>
                            <td>@Data.PromoExtendedAmount</td>
                        </tr>
                    }
                    if (checkEmty)
                    {
                         <tr>
                            <td colspan="11">No Errors</td>
                        </tr>
                    }
                }
            }
        </tbody>