So I have to read in all restaurant records in a MSSQL database and each resaurant has a list of tables. My issue goes as I know how to read one restaurant row:
public Restaurant GeefRestaurant(int id)
{
using (SqlCommand cmd = _connection.CreateCommand())
{
try
{
_connection.Open();
string naam;
string email;
string telefoonnummer;
int postcode;
string gemeente;
string straat;
string huisnummer;
Keuken keuken;
int tafelnummerOld = -1;
int tafelnummer = 0;
int aantalPlaatsen = 0;
bool isBezet = false;
bool first = true;
Locatie l = null;
Restaurant r = null;
List<Tafel> tafels = new();
cmd.CommandText = $"select r.Id RestaurantId, r.naam, r.email, r.telefoonnummer, r.keuken, l.id locatieid, l.postcode, l.gemeente, l.straat, l.huisnummer, t.Tafelnummer, t.aantalplaatsen, t.isbezet "
$"from Restaurant r "
$"left join locatie l on r.locatieid = l.id "
$"left join tafel t on r.id = t.restaurantid "
$"where r.id = {id}";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (r == null)
{
postcode = (int)reader["Postcode"];
gemeente = (string)reader["Gemeente"];
straat = reader["Straat"] == DBNull.Value ? null : (string)reader["Straat"];
huisnummer = reader["Huisnummer"] == DBNull.Value ? null : (string)reader["Huisnummer"];
keuken = (Enum.Parse<Keuken>((string)reader["keuken"]));
naam = (string)reader["naam"];
email = (string)reader["Email"];
telefoonnummer = (string)reader["telefoonnummer"];
l = new(postcode, gemeente, straat, huisnummer);
l.ZetId((int)reader["LocatieId"]);
r = new(naam, l, telefoonnummer, email, keuken);
r.ZetId((int)reader["RestaurantId"]);
}
if (!reader.IsDBNull(reader.GetOrdinal("Tafelnummer"))) // heeft tafels
{
tafelnummer = (int)reader["Tafelnummer"];
if (tafelnummer != tafelnummerOld)
{
// Nieuwe tafel of de eerste
if (tafelnummerOld > 0)
{
// Maak tafel, einde bereikt
Tafel t = new(tafelnummerOld, aantalPlaatsen, isBezet);
r.VoegTafelToe(t);
}
first = true;
tafelnummerOld = tafelnummer;
}
if (first)
{
aantalPlaatsen = (int)reader["AantalPlaatsen"];
isBezet = (bool)reader["IsBezet"];
first = false;
}
}
}
reader.Close();
if (tafelnummer > 0)
{
Tafel t = new(tafelnummer, aantalPlaatsen, isBezet);
r.VoegTafelToe(t);
}
return r;
}
catch (Exception ex)
{
throw new RestaurantRepositoryException("GebruikerRegistreren - repo", ex);
}
finally
{
_connection.Close();
}
}
}
But I don't exactly know how to read all of them out in code, I know the sql command, I've tried numerous things but none of them did what it was supposed to do :/. What I want is to start reading a Restaurant row, read out all of it's tables and then move on to the next until all data is read.
I tried something like this but this doesn't work...
public IReadOnlyList<Restaurant> GeefAlleRestaurants()
{
using (SqlCommand cmd = _connection.CreateCommand())
{
try
{
_connection.Open();
string naam;
string email;
string telefoonnummer;
int postcode;
string gemeente;
string straat;
string huisnummer;
Keuken keuken;
int restaurantIdOld = -1;
int restaurantId = 0;
int tafelnummerOld = -1;
int tafelnummer = 0;
int aantalPlaatsen = 0;
bool isBezet = false;
bool first = true;
bool firstRestaurant = true;
Locatie l = null;
List<Restaurant> restaurants = new();
//Restaurant r = null;
List<Tafel> tafels = new();
cmd.CommandText = $"select r.Id RestaurantId, r.naam, r.email, r.telefoonnummer, r.keuken, l.id locatieid, l.postcode, l.gemeente, l.straat, l.huisnummer, t.Tafelnummer, t.aantalplaatsen, t.isbezet "
$"from Restaurant r "
$"left join locatie l on r.locatieid = l.id "
$"left join tafel t on r.id = t.restaurantid";
SqlDataReader reader = cmd.ExecuteReader();
//TODO!
while (reader.Read())
{
if (!reader.IsDBNull(reader.GetOrdinal("RestaurantId"))) // heeft restaurants
{
restaurantId = (int)reader["RestaurantId"];
if (restaurantId != restaurantIdOld)
{
// Nieuwe restaurant of het eerste
if (restaurantIdOld > 0)
{
// Maak restaurant, einde bereikt
postcode = (int)reader["Postcode"];
gemeente = (string)reader["Gemeente"];
straat = reader["Straat"] == DBNull.Value ? null : (string)reader["Straat"];
huisnummer = reader["Huisnummer"] == DBNull.Value ? null : (string)reader["Huisnummer"];
keuken = (Enum.Parse<Keuken>((string)reader["keuken"]));
naam = (string)reader["naam"];
email = (string)reader["Email"];
telefoonnummer = (string)reader["telefoonnummer"];
r = new(naam, l, telefoonnummer, email, keuken);
if (reader.HasRows)
{
if (!reader.IsDBNull(reader.GetOrdinal("Tafelnummer")))
{
tafelnummer = (int)reader["Tafelnummer"];
if (tafelnummer != tafelnummerOld)
{
if (tafelnummerOld > 0)
{
Tafel t = new(tafelnummer, aantalPlaatsen, isBezet);
r.VoegTafelToe(t);
}
tafelnummerOld = tafelnummer;
aantalPlaatsen = (int)reader["aantalplaatsen"];
isBezet = (bool)reader["isbezet"];
}
}
}
restaurants.Add(r);
}
firstRestaurant = true;
restaurantIdOld = restaurantId;
if (firstRestaurant)
{
naam = (string)reader["naam"];
email = (string)reader["Email"];
telefoonnummer = (string)reader["telefoonnummer"];
postcode = (int)reader["Postcode"];
gemeente = (string)reader["Gemeente"];
straat = reader["Straat"] == DBNull.Value ? null : (string)reader["Straat"];
huisnummer = reader["Huisnummer"] == DBNull.Value ? null : (string)reader["Huisnummer"];
keuken = (Enum.Parse<Keuken>((string)reader["keuken"]));
l = new(postcode, gemeente, straat, huisnummer);
l.ZetId((int)reader["LocatieId"]);
r = new(naam, l, telefoonnummer, email, keuken);
r.ZetId((int)reader["RestaurantId"]);
if (reader.HasRows)
{
if (!reader.IsDBNull(reader.GetOrdinal("Tafelnummer")))
{
tafelnummer = (int)reader["Tafelnummer"];
if (tafelnummer != tafelnummerOld)
{
if (tafelnummerOld > 0)
{
Tafel t = new(tafelnummer, aantalPlaatsen, isBezet);
r.VoegTafelToe(t);
}
tafelnummerOld = tafelnummer;
aantalPlaatsen = (int)reader["aantalplaatsen"];
isBezet = (bool)reader["isbezet"];
}
}
}
restaurants.Add(r);
}
}
}
}
reader.Close();
return restaurants;
}
catch (Exception ex)
{
throw new RestaurantRepositoryException("GebruikerRegistreren - repo", ex);
}
finally
{
_connection.Close();
}
}
}
I also tried to make another methods that returns a list so I could use this method instead of trying to get all the Tafel records from the db but I get an error that the connection was already opened and I don't know if that is good practice...
CodePudding user response：
A SQL query is not guaranteed to return the records in any particular order if you don't specify one explicitly. Since you want to add a new restaurant to the list whenever the restaurant id changes, you must order by the restaurant id
SELECT r.Id RestaurantId, r.naam, r.email, ...
FROM Restaurant r
LEFT JOIN locatie l ON r.locatieid = l.id
LEFT JOIN tafel t ON r.id = t.restaurantid
ORDER BY r.id
Note that because the restaurant id is the primary key, it cannot be null. Therefore, don't test it for DbNull. But location and tables are left-joined and can therefore be null, e.g. if a restaurant doesn't have a location or tables defined.
Tables should occur only once per restaurant, so (restaurantid, Tafelnummer) must be a compound primary key or have a unique index. You could also have a separate tafelid primary key being unique for the whole tafels table.
Locations can occur in any order and can be assigned to several restaurants. Therefore, I suggest adding them to a dictionary with the table number as key. This enables us to look then up easily.
The logic then becomes
List<Restaurant> restaurants = new();
Dictionary<int, Locatie> locations = new();
int restaurantIdOld = -1;
... (skipping details)
while (reader.Read()) {
int restaurantId = (int)reader["RestaurantId"];
if (restaurantId != restaurantIdOld) {
Locatie l = null;
if (reader["LocatieId"] is int locId &&
!locations.TryGetValue(locId, out l))
{
//TODO: get location fields
l = new(locId, postcode, gemeente, straat, huisnummer);
locations.Add(locId, l);
}
//TODO: get restaurant fields
r = new(restaurantId, naam, l, telefoonnummer, email, keuken);
restaurants.Add(r);
}
if (reader["Tafelnummer"] is int tafelnummer) {
//TODO: get table fields
// No need to test whether this is a new table. Tables are unique.
Tafel t = new(restaurantId, tafelnummer, aantalPlaatsen, isBezet);
r.VoegTafelToe(t);
}
}
return restaurants;
We can also remove the $ from the SQL string since we don't have any string interpolation.
There is no need to declare all the variables in advance. Declare them in the scope they are needed.
I skipped some details. Use a using declaration for the reader. This will close it automatically at the end of the scope.
using SqlDataReader reader = cmd.ExecuteReader();
You can do the same with commands and connections.