Rendering Two SQL Data Tables in a Razor Page

Data and Databases
1

I’m attempting to show data from two SQL tables on one Razor page, but I’m having difficulties with the implementation. I can successfully display one table, but when I add a second, both tables show identical data. I would greatly appreciate any assistance with this issue. Here’s the code I’ve written so far:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data.SqlClient;

namespace MyWebApp.Pages
{
    public class RefrigeratorModel : PageModel
    {
        public List<RefrigeratorInfo> freezerData = new List<RefrigeratorInfo>();

        public void OnGet()
        {
            try
            {
                string connString = "Data Source=MYPC\SQLEXPRESS;Initial Catalog=mydatabase;user id=sa;password=****;";
                using (SqlConnection sqlConnection = new SqlConnection(connString))
                {
                    sqlConnection.Open();
                    string query = "SELECT * FROM StockTop";

                    using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection))
                    {
                        using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                RefrigeratorInfo topStock = new RefrigeratorInfo();
                                topStock.Id = dataReader.GetInt32(0);
                                topStock.ItemDescription = dataReader.GetString(1);
                                topStock.ItemQuantity = dataReader.GetString(2);

                                freezerData.Add(topStock);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // Handle exception
            }
        }
    }

    public class RefrigeratorInfo
    {
        public int Id;
        public string ItemDescription;
        public string ItemQuantity;
    }
}

public class RefrigeratorModel : PageModel
{
    public List<RefrigeratorInfo> freezerData = new List<RefrigeratorInfo>();

    public void OnGet()
    {
        try
        {
            string connString = "Data Source=MYPC\SQLEXPRESS;Initial Catalog=mydatabase;user id=sa;password=****;";
            using (SqlConnection sqlConnection = new SqlConnection(connString))
            {
                sqlConnection.Open();
                string query = "SELECT * FROM StockBottom";

                using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            RefrigeratorInfo bottomStock = new RefrigeratorInfo();
                            bottomStock.ItemDescription = dataReader.GetString(1);
                            bottomStock.ItemQuantity = dataReader.GetString(2);

                            freezerData.Add(bottomStock);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // Handle exception
        }
    }
}

public class RefrigeratorInfo
{
    public int Id;
    public string ItemDescription;
    public string ItemQuantity;
}

And for the Razor page:

@page
@model MyWebApp.Pages.RefrigeratorModel
@{
}

<h2>Top Stock Inventory</h2>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Stock Overview</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 12pt;
        }
        table {
            border: 2px solid #aaa;
            border-collapse: collapse;
            background-color: #f9f9f9;
        }
        table th {
            background-color: #C1E1C1;
            color: #333;
            font-weight: bold;
        }
        table th, table td {
            padding: 8px;
            border: 1px solid #aaa;
        }
    </style>
</head>
<body>
<table class="table">
<thead>
    <tr>
        <th>Item Description</th>
        <th>Item Quantity</th>
    </tr>

    @foreach (var topStock in Model.freezerData)
    {
        <tr>
            <td>@topStock.ItemDescription</td>
            <td>@topStock.ItemQuantity</td>
        </tr>
    }
</tbody>
</table>

<table class="table">
    <tr>
        <th>Item Description</th>
        <th>Item Quantity</th>
    </tr>
    @foreach (var bottomStock in Model.freezerData)
    {
        <tr>
            <td>@bottomStock.ItemDescription</td>
            <td>@bottomStock.ItemQuantity</td>
        </tr>
    }
</table>
</body>
</html>
2

Hey, it seems you are overwriting your data because you define “freezerData” list identically for both data sets. Try creating separate lists, like “freezerData” and “fridgeData” for each table and populate them individually. Then in razor, bind each to the correct table. Hope this helps! :slightly_smiling_face:

3

To solve your issue, ensure that each SQL query result is stored in its own list. You’ve declared the same property name for two different purposes, which is causing the data to overwrite. Additionally, you could benefit from combining the two model classes into one or using separate methods to load each table’s data. Handy practice is to use distinct method names like LoadTopStock() and LoadBottomStock(). This organization will allow you to easily trace and bind your data on the Razor page correctly.