Display 2 SQL tables using c#

Question

I am trying to display two SQL tables from the same DB in a webpage but the code below is displaying the 'BottomStock' table twice and everything I try seems to either get part of the data from the 'TopStock' table or none at all. I have scroll through countless forums but I have been unable to find a suitable solution. Any help would be appreciated.

public class Test4Model : PageModel
{
    public List<FreezerInfo> listTopFreezer = new List<FreezerInfo>();

    public List<FreezerInfo> listBottomFreezer = new List<FreezerInfo>();

    public void OnGet()
    {
        try
        {
            using (var connection = new SqlConnection("Data Source=SDS- 
LAPTOP\\SQLEXPRESS;Initial Catalog=test;user id=sa;password=wis09"))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM TopStock";
                    command.ExecuteNonQuery();

                    command.CommandText = "SELECT * FROM BottomStock";
                    command.ExecuteNonQuery();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        //reader.Read();
                        {
                            FreezerInfo TopStock = new FreezerInfo();
                            TopStock.Description = reader.GetString(1);
                            TopStock.Quantity = reader.GetString(2);
                            listTopFreezer.Add(TopStock);

                            FreezerInfo BottomStock = new FreezerInfo();
                            BottomStock.Description = reader.GetString(1);
                            BottomStock.Quantity = reader.GetString(2);
                            listBottomFreezer.Add(BottomStock);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
        }
    }
}

public class FreezerInfo
{
    public string Description { get; set; }
    public string Quantity { get; set; }
}

Answer 1

You are using SqlCommand completely wrong. ExecuteNonQuery does not return results. Only ExecuteScalar or ExecuteReader do. Furthermore, you have two batches each with a SELECT, but you are only executing one and somehow expecting the results to be interleaved.

I would advise you to use one batch of two SELECT statements, you can use NextResult to move to the next resultset within the batch.

• Store your connection string in a settings file, not hard-coded.
• Only select the columns you need, rather than SELECT *.
• Use column names rather than ordinals, especially if you are using SELECT *.
• Do not swallow exceptions. Handle them or allow them to bubble back to the caller.
• Consider using async to allow the caller to continue asynchronously.
• Reconsider the data types of the columns.
• Consider why you have two almost identical tables in the first place. Perhaps they should be merged.

public void OnGet()
{
    try
    {
const string query = @"
SELECT Description, Quantity
FROM TopStock;

SELECT Description, Quantity
FROM BottomStock;
";
        using var connection = new SqlConnection(connectionStringFromSettingsFileHere);
        using var command = new SqlCommand(query, connection);
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())

        while (reader.Read())
        {
            FreezerInfo TopStock = new FreezerInfo
            {
                Description = (string)reader["Description"],
                Quantity = (string)reader["Quantity"],  // shouldn't it be an int???
            };
            listTopFreezer.Add(TopStock);
        }

        reader.NextResult();

        while (reader.Read())
        {
            FreezerInfo BottomStock = new FreezerInfo
            {
                Description = (string)reader["Description"];
                Quantity = (string)reader["Quantity"],  // shouldn't it be an int???
            };
            listBottomFreezer.Add(BottomStock);
        }
    }
    catch (Exception ex)
    {
        // exception handling here. DO NOT SWALLOW
    }
}