I am trying to display two SQL tables on a single Razor page and struggling to get the code right. A single table is fine but when I try to add two the same data is being displayed in both tables.
Any help would be much appreciated.
This is the code I'm using:
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data.SqlClient;
namespace WebStoreTest.Pages
{
public class FreezerModel : PageModel
{
public List<FreezerInfo> listFreezer = new List<FreezerInfo>();
public void OnGet()
{
try
{
string connectionString = "Data Source=LAPTOP\\SQLEXPRESS;Initial
Catalog=test;user id=sa;password=****;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM TopStock";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FreezerInfo TopStock = new FreezerInfo();
TopStock.id = reader.GetInt32(0);
TopStock.Description = reader.GetString(1);
TopStock.Quantity = reader.GetString(2);
listFreezer.Add(TopStock);
}
}
}
}
}
catch (Exception ex)
{
}
}
}
public class FreezerInfo
{
public int id;
public string Description;
public string Quantity;
}
}
public class FreezerModel : PageModel
{
public List<FreezerInfo> listFreezer = new List<FreezerInfo>();
public void OnGet()
{
try
{
string connectionString = "Data Source=LAPTOP\\SQLEXPRESS;Initial
Catalog=test;user id=sa;password=****;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM BottomStock";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FreezerInfo BottomStock = new FreezerInfo();
BottomStock.Description = reader.GetString(1);
BottomStock.Quantity = reader.GetString(2);
listFreezer.Add(BottomStock);
}
}
}
}
}
catch (Exception ex)
{
}
}
}
public class FreezerInfo
{
public int id;
public string Description;
public string Quantity;
}
And the page:
@page
@model WebStoreTest.Pages.FreezerModel
@{
}
<br />
<h2>Top Freezer Stock</h2>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
body {
font-family: Arial;
font-size: 10pt;
}
table {
border: 1px solid #ccc;
border-collapse: collapse;
background-color: #fff;
}
table th {
background-color: #B8DBFD;
color: #333;
font-weight: bold;
}
table th, table td {
padding: 5px;
border: 1px solid #ccc;
}
table, table table td {
border: 0px solid #ccc;
}
</style>
</head>
<body>
<table >
<thead>
<tr>
<th>Description</th>
<th>Quantity</th>
</tr>
@foreach (var TopStock in Model.listFreezer)
{
<tr>
<td>@TopStock.Description</td>
<td>@TopStock.Quantity</td>
</tr>
}
</table>
<table >
<tr>
<th>Description</th>
<th>Quantity</th>
</tr>
@foreach (var BottomStock in Model.listFreezer)
{
<tr>
<td>@BottomStock.Description</td>
<td>@BottomStock.Quantity</td>
</tr>
}
</table>
</body>
</html>
CodePudding user response：
You are just using one list and to model, which will mix your Freezers, pages etc.
Try to create 1 model class for the page. Create 2 lists in this model, and name them accordingly:
Like this:
public List<FreezerInfo> listTopFreezer = new List<FreezerInfo>();
public List<FreezerInfo> listBottomFreezer = new List<FreezerInfo>();
Fill them in your OnGet method:
public void OnGet()
{
//fill listTopFreezer
//fill listBottomFreezer
}
Then, make sure you use them while rendering the page:
@foreach (var TopStock in Model.listTopFreezer)
and
@foreach (var BottomStock in Model.listBottomFreezer)
By the way: your code suggest you put value in the naming of the iterator variable, such as in the naming in the foreach loops. It does not do anything; for all it matter you can name it like this:
@foreach (var foobar in Model.listFreezer)
CodePudding user response：
Sorry if I'm being stupid but I have adjusted the code as per below but it is still only displaying data from one table. What am I doing wrong?
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data.SqlClient;
namespace WebStoreTest.Pages
{
public class TestModel : PageModel
{
public List<FreezerInfo> listBottomFreezer = new List<FreezerInfo>();
public List<FreezerInfo> listTopFreezer = new List<FreezerInfo>();
public void OnGet()
{
//listBottomFreezer;
//listTopFreezer;
{
string connectionString = "Data
Source=LAPTOP\\SQLEXPRESS;Initial Catalog=test;user
id=sa;password=test@09;";
using (SqlConnection connection = new
SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM BottomStock SELECT * FROM
TopStock";
using (SqlCommand command = new SqlCommand(sql,
connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FreezerInfo BottomStock = new FreezerInfo();
BottomStock.id = reader.GetInt32(0);
BottomStock.Description = reader.GetString(1);
BottomStock.Quantity = reader.GetString(2);
listBottomFreezer.Add(BottomStock);
while (reader.Read())
{
FreezerInfo TopStock = new FreezerInfo();
TopStock.id = reader.GetInt32(0);
TopStock.Description = reader.GetString(1);
TopStock.Quantity = reader.GetString(2);
listTopFreezer.Add(TopStock);
}
}
}
}
}
}
}
}
public class FreezerInfo
{
public int id;
public string Description;
public string Quantity;
}
}