ASP.net core table on webpage

Question

Dear,
I am creating an ASP.NET Core Web App using C# using .NET6.0. Core. I have a problem to return results on the web page, my sql is ok. I have only just started and looking to complete a connection to an existing SQL database. In the debugging I see that my table is loaded (topscoorder.cshtml.cs) but it doesn't show up on my web page (topscoorder.cshtml). What do I have to do to show the results?

Topscoorder.chtml.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Http;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System;
using System.Reflection.PortableExecutable;
using System.Data;

namespace Tournament_organizer.Pages
{
public class TopscoorderModel : PageModel`
{
public string? sessionvariable;
public Guid variabelIDdivisie;
public string? variabelToer;
public string? variabelTopScoorder;
public Int32 variabelAantal;
public Int32 variabelAantalSpeler;
public Int32 variabelAantalDoelpunten;
public Int32 variabelAantalKaarten;
public Int32 variabelDoelpunten;
public String variabelVlag;
public String variabelTeam;
public List <DivisieTotaal> DivisiesTjes = new();
public List <TopscoorderTotaal> variabelTopscoorders = new();

public void OnGet()
{
List\<DivisieTotaal\> DivisiesTjes = new List\<DivisieTotaal\>();
sessionvariable = "'25ed9970-ff7a-4750-a35f-1f9f5ec4b286'";

//Set value in Session object.
if (HttpContext.Session.GetString("SessionVar") != null)
sessionvariable = HttpContext.Session.GetString("SessionVar");

try
{
String connectionString = "*************";using SqlConnection connection = new(connectionString);
connection.Open();

//inladen van de divisies met de topscoorders
String sql = "SELECT \* FROM dbo.division WHERE id_toernooi='25ed9970-ff7a-4750-a35f-1f9f5ec4b286'and aktief='J' ORDER BY Naam";

SqlDataAdapter DivAdapter = new SqlDataAdapter(sql, connection);
DataSet dsDivisie = new DataSet();
DivAdapter.Fill(dsDivisie, "Divisie");

foreach (DataTable ODTdiv in dsDivisie.Tables)
{
foreach (DataRow ODRdiv in ODTdiv.Rows)
{
variabelIDdivisie = (Guid)ODRdiv\["id"\];
variabelTopScoorder = "To be confirmed";
List\<TopscoorderTotaal\> variabelTopscoorders = new List\<TopscoorderTotaal\>();

//inladen van de topscoorders                       
sqlT = "select  vlag, speler.naam, team.naam as team, sum(doelpunten) as doelpunten, sum(rodekaarten) as rode_kaarten, sum(gelekaarten) as gele_kaarten, sum(groenekaarten) as groene_kaarten  from speler, speler_game, game, division, land, league, team where speler.id = speler_game.id_speler and speler_game.id_game = game.id and game.id_division = division.id    and division.aktief = 'J' and game.aktief = 'J' and speler.aktief = 'J'    and land.id = league.id_land   and league.id = speler.id_league   and speler.id_team = team.id   and division.id =   '" + variabelIDdivisie.ToString() + "'   group by  land.vlag, speler.naam, team.naam order by sum(doelpunten) desc, sum(rodekaarten) asc, sum(gelekaarten) asc, sum(groenekaarten) asc";

SqlDataAdapter adapter = new SqlDataAdapter(sqlT, connection);
DataSet dsTopscoorder = new DataSet();
adapter.Fill(dsTopscoorder, "Topscoorder");

foreach (DataTable ODT in dsTopscoorder.Tables)
{
foreach (DataRow ODR in ODT.Rows)
{
variabelTopScoorder = (string)ODR\["naam"\];
variabelDoelpunten = (Int32)ODR\["doelpunten"\];
variabelVlag = (String)ODR\["vlag"\];
variabelTeam = (String)ODR\["team"\];

TopscoorderTotaal topscoorderTotaal = new()
{
Doelpunten = variabelDoelpunten,
Vlag = variabelVlag,
Team = variabelTeam,
Topscoorder = variabelTopScoorder
};
variabelTopscoorders.Add(topscoorderTotaal);
};
DivisieTotaal divisieTotaal = new()
{
id_division = variabelIDdivisie,
Naam = (String)ODRdiv\["naam"\],
Shotklok = (String)ODRdiv\["shotklok"\],
Topscoorders = variabelTopscoorders
};
DivisiesTjes.Add(divisieTotaal);    
}
}
}
}

catch (Exception ex)
{
Console.WriteLine("NOT Connected");  // something like this
Console.WriteLine("Exception: connectie NIET ok " + ex.Message);
}
}
}
public class DivisieTotaal
{
public String? Naam;
public String? Shotklok;
public Guid id_division;
public List \<TopscoorderTotaal\>? Topscoorders;
}

public class TopscoorderTotaal
{
public String? Topscoorder;
public Int32 Doelpunten;
public String? Vlag;
public String? Team;
}
}

webpagina.cshtml

@page
@model Tournament_organizer.Pages.TopscoorderModel
@using Microsoft.AspNetCore.Http
@addTagHelper\*, Microsoft.AspNetCore.Mvc.TagHelpers
@using Microsoft.Extensions.Configuration;

@{
Layout = "\_Layout_Overzicht";
}
<row class="eigentabel">
<p>@DateTime.Now</p>
<row>
<div class="container">
<div class="row">
@foreach (var item in Model.DivisiesTjes)
{
//hier zetten we de titel op de pagina
<h2>@item.Naam </h2>

<table id="" class="display">
<thead>
<tr>
<th></th>
<th>Player name</th>
<th>Country</th>
<th></th>
</tr>
</thead>
<tbody>


</tbody>
</table>
}
</div>
</div>
</row>
</row>

Data sql database:

Answer 1

In the debugging I see that my table is loaded (topscoorder.cshtml.cs) but it doesn't show up on my web page (topscoorder.cshtml). What do I have to do to show the results?

Actually, based on your shared code snippet which is really hard to read and out of convention or good practice.

Apparently, I have seen a few mistakes which are possibly causing no projection on your view page. In addition, try to split larger method in a small chuck in order to increase readability.

Let's have a look some of the major issues.

To begin with, we must need to define property for binding values to it so that we can access those that are out side of method. Unfortunately, you have defined only these fields which cannot be accessible from view.

Hence, you should define your property within class. You can do as following:

Model:

public class TopscoorderTotal
    {
        public string? Topscoorder { get; set; }
        public int Doelpunten { get; set; }
        public string? Vlag { get; set; }
        public string? Team { get; set; }
    }

cshtml.cs:

public class ReadFromDataTableModel : PageModel
    {
        
        public List<TopscoorderTotal>? TopscoorderTotals { get; set; }
        public void OnGet()
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var query = "SELECT Topscoorder ,Doelpunten ,Vlag ,Team FROM TopscoorderTotaal";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.CommandType = CommandType.Text;
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                con.Close();
                string serializeObject = JsonConvert.SerializeObject(dt);
                var dataTableObjectInPOCO = JsonConvert.DeserializeObject<List<TopscoorderTotal>>(serializeObject);
                TopscoorderTotals = dataTableObjectInPOCO;
            }
        }
    }

Note: As you can see, we have initialized a list TopscoorderTotals which, will be used in a view in order to loop over the collection. In addition, after getting datatable value we have serialized that into poco class. On top of this, I have prepared the above demo for TopscoorderTotal class only, you can now combine DivisieTotal as well. It's better to build a view model and bind them together. This would be much easier.

cshtml:

@page
@model ReadFromDataTableModel
<table class="table">
    <thead>
        <tr>
            <th>Topscoorder
            <th>Doelpunten
            <th>Vlag
            <th>Team
           
        </tr>
    </thead>
    <tbody>
        @foreach (var items in @Model.TopscoorderTotals!)
        {
            <tr>
                <td>@items.Topscoorder</td>
                <td>@items.Doelpunten</td>
                <td>@items.Vlag</td>
                <td>@items.Team</td>
            </tr>
        }
    </tbody>
</table>

Output:

Note: I would recommend you to learn about entity framework because it will allow you to do the above stuff more conveniently.