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 >
    <div >
        <div >SQL Data Mismatch Tables</div>
        <button  onclick={location.replace('/home/logout')}>Logout</button>
    </div>
    <div >
    <div >
        <table >
            <thead>
                <tr>
                    <th  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?

CodePudding user response：

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>