14. SQL databanken

Wat we willen bouwen

Voor dit hoofdstuk bouwen we een complete databankgestuurde applicatie, waarin we werknemers (tabel Employee) en hun jobhistoriek (tabel Job) willen bijhouden.

Databank opzetten

Stap 1: nodige software installeren

Voor dit vak heb je deze software nodig:

• SQL Server Express: de databank server, download hier
• SQL Server Management Studio (SSMS): een tool om databanken te beheren, download hier

Stap 2: SQL importeren in SSMS

In dit vak zul je altijd een databank aangeleverd krijgen als een SQL bestand, dat je moet importeren met SSMS via New Query → kopieer de SQL code → Execute. Importeer CompanyDB.sql:

Stap 3: database bestuderen

Voor je met de WPF toepassing begint, moet je het schema (de structuur) en relaties van de gebruikte databank grondig begrijpen. In dit hoofdstuk nemen we een databank bestaande uit één enkele tabel Employee. Het schema:

schema en data

Met schema wordt het design bedoeld van de databank, zonder data.

Schema en data van de tabel Employee:

Schema en data van de tabel Job:

veldeigenschappen

Nadat je de databank geïnstalleerd hebt, bestudeer je best de eigenschappen van de kolommen (ook velden genoemd). Zo zul je opmerken dat het id veld zoals het hoort aangeduid is als Primary Key, en eveneens als Identity Column (auto increment). Voor meer uitleg hierover verwijzen we naar de cursus databanken.

Voor bepaalde kolommen zul je nog een verduidelijkende beschrijving vinden onder Properties → Description, zoals voor de getalcodes gebruikt bij gender.

check constraints

Regels die data moet volgen noemt men check constraints. Dit kan vanalles zijn: postcode moet uit 4 cijfers bestaan, een verkoop moet een geldig klantnummer bevatten enz... De constraints van de tabel Employee:

• accesscode moet tussen 1000 en 9999 liggen
• gender moet 0, 1, 2 of 9 zijn

Solution opzetten

Stap 1: blank solution maken

Open Visual Studio, kies Create new project..., en kies blank solution:

Stap 2: class library toevoegen

voordelen

Een class library is een speciaal projecttype waar je klassen kan aanmaken. Die klassen kan je dan gebruiken in één of meerdere andere projecten (b.v. een app voor gebruikers en een app voor admins). De voordelen van het gebruik van classes en een class library in het bijzonder zijn groot:

1. Minder herhaling: bv. de SQL code voor het opvragen van employee gegevens wordt zowel in MainWindow als in WinEditEmployee gebruikt; die code zou beter op één centrale plek bewaard blijven
2. Lichtere code-behind: door verplaatsen van alle SQL code en andere logica van het project naar een class library maakt de overblijvende code-behind veel lichter
3. Intuïtieve code: een algemeen voordeel van classes is dat de code meer aansluit bij onze leefwereld en dus leesbaarder wordt

Je kan classes uiteraard ook nog steeds in het project zelf aanmaken, maar om hergebruik bij meerdere apps mogelijk te maken zullen we ze onderbrengen een class library.

multi-tier architectuur

Dankzij de class library is de toepassing verder opgesplitst in verschillende lagen met elk een eigen taak. Dit is de zgn. meerlagen- of multi-tier architectuur:

Elke laag communiceert enkel met de laag erboven of eronder. Bijgevolg:

1. Geen SQL code buiten de class library
   Alle communicatie met de databank moet via de class library gebeuren, en enkel daar mag SQL code voorkomen. De WPF windows bv. mogen nooit rechtstreeks de databank aanspreken.
2. Geen WPF controls in de class library
   Omgekeerd mag de class library, dus de businesslaag, niet rechtstreeks de XAML code, dus de presentatielaag, aanspreken. Daarom: geen WPF controls in de class library (zelfs niet als parameter!).

toevoegen

Voeg een nieuw project type Class Library toe (.NET8) en noem het b.v. CLCompany:

Stap 3: WPF Application(s) toevoegen

Voeg nu een WPF Application toe (.NET8), en noem het b.v. WpfCompany:

Je solution zou er zo moeten uitzien:

Voeg eventueel nog andere projecten toe die deel uitmaken van je toepassing. En verwijder Class1.cs, die hebben we niet nodig.

Solution configureren

Stappen:

1. genereer een connection string via Server Explorer
2. voeg App.Config toe aan WPF project, en bewaar hierin de connection string
3. voeg nodige packages toe aan de class library
4. voeg een referentie naar de class library toe aan je WPF project

Stap 1: connection string genereren

Een connection string bevat de gegevens nodig om een connectie te maken: database host, tabelnaam, authenticatiegegevens...

Maak het aan via Server Explorer, Add Connection...:

In het volgende dialoogvenster kies je de server en authenticatiemethode (vul dezelfde gegevens in als waarmee je connecteert op SQL Server Management Studio), en dan de databank:

De gegenereerde connectiestring vind je tenslotte terug bij de connection properties:

Stap 2: connectiestring toevoegen aan App.config

Hoewel je de connectie string ook overal letterlijk in je C# code kan gebruiken, is het beter het één keer globaal te bewaren in App.config. Als het niet al aanwezig is in je WPF project, voeg het toe via Add, new Item..., Application Configuration File:

Voeg dan de eerder gegenereerde connection string toe aan App.config:

<configuration>
   <connectionStrings>
      <add name="connStr" connectionString="Data Source=(localdb)\mssqllocaldb;Initial Catalog=CompanyDB;Integrated Security=True" />
   </connectionStrings>
   ...
</configuration>

• name="...": kies hier een naam, b.v. connStr
• connectionString="...": kopieer hier jouw gegenereerde connectiestring

Stap 3: NuGet packages toevoegen aan class library

Om de connectiestring uit App.config te kunnen aanspreken en om SQL te kunnen uitvoeren, hebben we twee NuGet packages nodig: System.Configuration.ConfigurationManager en Microsoft.Data.SqlClient. Deze packages hebben we enkel nodig in de class library, omdat we alle methodes voor interactie met de databank (SQL instructies) in de classes zullen onderbrengen, niet in de WPF application zelf.

Stap 4: referentie naar class library toevoegen in project

Om je class library te kunnen gebruiken in WpfCompany, voeg je een een referentie toe naar CLCompany:

Klassen uitwerken

De volgende stap is onze klassen uitwerken in de class library. We hebben minstens voor elke tabel één klasse. Elke klasse bevat:

• connectiestring (gehaald uit App.config)
• properties: komen overeen met de tabelvelden
• minstens de lege constructor
• publieke CRUD methodes: Update(), Insert(), Delete(), GetById(), GetAll()...
• een public override string ToString()

Stap 1: klassen Employee en Job in class library maken

Voeg classes Employee en Job toe:

• voor het gemak kiezen we voor de class dezelfde naam als de tabelnaam in de databank
• kies class-namen altijd enkelvoud, dus niet Employees maar Employee; ze beschrijven een object, geen verzameling
• verander de zichtbaarheid van internal (enkel beschikbaar in de library) naar public (overal beschikbaar)

Stap 2: basisstructuren klassen Employee en Job

public enum GenderType { Onbekend, Man, Vrouw, Nvt = 9 } // enumeratie voor gender types
public class Employee
{
   // variabelen
   private static string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

   // properties
   public int Id { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public string Email { get; set; }
   public GenderType Gender { get; set; }
   public DateTime BirthDate { get; set; }
   public int? AccessCode { get; set; } // nullable field → nullable property
   public int? BossId { get; set; } // nullable field → nullable property

   // constructors
   public Employee() { }

   // database methoden
   public static List<Employee> GetAll() { /* TODO */}
   public static Employee GetById(int empId) { /* TODO */}
   public void DeleteFromDb() { /* TODO */}
   public int InsertInDb() { /* TODO */}
   public void UpdateInDb() { /* TODO */}

   // override ToString
   public override string ToString() { return $"{FirstName} {LastName}"; }
}

public class Job
{
   // variabelen
   private static string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

   // properties
   public int Id { get; set; }
   public DateTime StartDate { get; set; }
   public DateTime? EndDate { get; set; } // nullable field → nullable property
   public string JobTitle { get; set; }
   public int EmployeeId { get; set; }

   // constructors
   public Job() { }

   // database methoden
   public void DeleteFromDb() { /* TODO */}
   public int InsertToDb() { /* TODO */}
   public void UpdateInDb() { /* TODO */}

   // override ToString
   public override string ToString() { return JobTitle; }
}

Opmerkingen:

• de properties komen overeen met de tabelvelden; let op dat nullable velden ook nullable properties hebben
• let bij de database methoden heel goed op het onderscheid tussen static en non-static:
  static: gebeurt op de klasse, b.v. alle oplijsten, zoeken op basis van een id...
  non-static: gebeurt op een specifiek object, b.v. toevoegen, verwijderen...
• voorzie best altijd een eigen ToString() override

Stap 3: CRUD methodes implementeren

basisschema queries

Basisschema voor het uitvoeren van SQL queries:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) {
    conn.Open();
    // voer je SQL query hier uit
    // ...
}

Dankzij de using wordt de connectie automatisch gesloten. Dit is belangrijk, want aangezien een server maar een beperkt aantal gelijktijdige connecties toelaat, kunnen bij teveel openstaande connecties nieuwe connecties geweigerd worden en de databank onbereikbaar worden.

SELECT queries

voorbeeld 1: ophalen employees

We beginnen met de SELECT queries. Code voor de Employee.GetAll() methode:

public static List<Employee> GetAll() {
   List<Employee> emps = new List<Employee>();
   using (SqlConnection conn = new SqlConnection(connString)) {
      // open connectie
      conn.Open();

      // voer SQL commando uit
      SqlCommand comm = new SqlCommand("SELECT * FROM Employee", conn);
      SqlDataReader reader = comm.ExecuteReader();

      // lees en verwerk resultaten
      while (reader.Read()) emps.Add(new Employee() {
         Id = Convert.ToInt32(reader["id"]),
         FirstName = Convert.ToString(reader["firstname"]),
         LastName = Convert.ToString(reader["lastname"]),
         Email = Convert.ToString(reader["email"]),
         Gender = (GenderType)Convert.ToInt32(reader["gender"]),
         BirthDate = Convert.ToDateTime(reader["birthdate"]),
         AccessCode = reader["accesscode"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["accesscode"]),
         BossId = reader["boss_id"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["boss_id"])

      });
   }
   return emps;
}

• ExecuteReader(): geeft een SqlDataReader instantie terug waarmee je over de resultaten kan itereren
• Read(): leest het volgende resultaat uit de reader (null indien geen meer beschikbaar)
• merk op dat nullable velden een DBNull.Value teruggeven, geen null, waardoor de ternaire operator nodig is
• merk ook op dat de reader typeloze waarden teruggeeft; je moet ze dus nog converteren met Convert.To...()

voorbeeld 2: employee details opvragen

Code voor de Employee.GetById(int empId) methode:

public static Employee GetById(int empId) {
   using (SqlConnection conn = new SqlConnection(connString)) {
      // open connectie
      conn.Open();

      // voer SQL commando uit
      SqlCommand comm = new SqlCommand("SELECT * FROM Employee WHERE ID = @parID", conn);
      comm.Parameters.AddWithValue("@parID", empId);
      SqlDataReader reader = comm.ExecuteReader();

      // lees en verwerk resultaten
      if (!reader.Read()) return null;
      return new Employee() {
         Id = Convert.ToInt32(reader["id"]),
         FirstName = Convert.ToString(reader["firstname"]),
         LastName = Convert.ToString(reader["lastname"]),
         Email = Convert.ToString(reader["email"]),
         Gender = (GenderType)Convert.ToInt32(reader["gender"]),
         BirthDate = Convert.ToDateTime(reader["birthdate"]),
         AccessCode = reader["accesscode"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["accesscode"]),
         BossId = reader["boss_id"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["boss_id"])
      };
   }
}

• merk op: we lezen maar één record (de employee)

data reader

ExecuteReader() is geen lijst of array, maar een data reader, i.e. een pointer, waarvan met Read() telkens het volgende record ingelezen wordt. Als er geen (meer) zijn krijg je een null terug.

SqlDataReader reader = comm.ExecuteReader();
while (reader.Read() != null) { // of gewoon while (reader.Read()) {
   ...
}

Je hoeft overigens geen lus te gebruiken: als je maar één record verwacht, kan het ook als volgt:

SqlDataReader reader = comm.ExecuteReader();
reader.Read();
if (reader != null) ...

De databank geeft typeloze waarden terug; je moet ze dus nog converteren:

SqlDataReader reader = comm.ExecuteReader();
reader.Read();
string email = Convert.ToString(reader["email"]); // converteer naar string
int gender = Convert.ToInt32(reader["gender"]); // converteer naar int
DateTime birthdate = Convert.ToDateTime(reader["birthdate"]);  // converteer naar DateTime
...

DBNull.Value

Bij velden die NULL's kunnen bevatten, moet je in C# nullable types gebruiken:

// int? is int + null
int? accesscode = reader["accesscode"] == DBNull.Value ? null : (int?) Convert.ToInt32(reader["accesscode"]);

Let op dat de reader geen null gebruikt, maar DBNull.Value. Je moet dus expliciet nulls omzetten van en naar DBNull.Value.
Voor het schrijven naar de databank krijg je dan typisch zoiets:

comm.Parameters.AddWithValue("@par6", AccessCode == null ? DBNull.Value : AccessCode);

named parameters

In theorie had je de tweede query ook als volgt kunnen samenstellen:

SqlCommand comm = new SqlCommand($"SELECT * FROM Employee WHERE ID = {employeeId}", conn);

Dit is zeer onveilig en vatbaar voor SQL injectie (lees meer hier)! Maak daarom altijd gebruik van named parameters:

SqlCommand comm = new SqlCommand("SELECT * FROM Employee WHERE ID = @parID", conn);
comm.Parameters.AddWithValue("@parID", employeeId); // named parameter @parID

DELETE query

voorbeeld 3: verwijderen employee

De DELETE syntax is eenvoudig. Code voor Employee.DeleteFromDb(). Het enige waar je op moet letten bij het verwijderen van data, is dat je eerst ook alle gekoppede gegevens verwijdert, zodat je in praktijk hier drie DELETE queries nodig hebt:

public void DeleteFromDb() {
   // verwijder gerelateerde jobs
   using (SqlConnection conn = new SqlConnection(connString)) {
      conn.Open();
      SqlCommand comm = new SqlCommand("DELETE FROM Job WHERE employee_id = @parID", conn);
      comm.Parameters.AddWithValue("@parID", Id);
      comm.ExecuteNonQuery();
   }

   // zet eventuele ondergeschikten zonder baas
   using (SqlConnection conn = new SqlConnection(connString)) {
      conn.Open();
      SqlCommand comm = new SqlCommand("UPDATE Employee SET boss_id = @par1 WHERE boss_id=@parID", conn);
      comm.Parameters.AddWithValue("@par1", DBNull.Value);
      comm.Parameters.AddWithValue("@parID", Id);
      comm.ExecuteNonQuery();
   }

   // verwijder tenslotte de werknemer zelf
   using (SqlConnection conn = new SqlConnection(connString)) {
      conn.Open();
      SqlCommand comm = new SqlCommand("DELETE FROM Employee WHERE ID = @parID", conn);
      comm.Parameters.AddWithValue("@parID", Id);
      comm.ExecuteNonQuery();
   }
}

• ExecuteNonQuery(): voer de query uit zonder iets terug te geven

INSERT query

voorbeeld 4: toevoegen employee

Code voor Employee.InsertInDb():

public int InsertInDb() {
   using (SqlConnection conn = new SqlConnection(connString)) {
      // open connectie
      conn.Open();

      // voer SQL commando uit
      SqlCommand comm = new SqlCommand(@"
         INSERT INTO Employee(firstname,lastname,email,gender,birthdate,accesscode,boss_id)
         output INSERTED.ID VALUES(@par1,@par2,@par3,@par4,@par5,@par6,@par7)", conn);
      comm.Parameters.AddWithValue("@par1", FirstName);
      comm.Parameters.AddWithValue("@par2", LastName);
      comm.Parameters.AddWithValue("@par3", Email);
      comm.Parameters.AddWithValue("@par4", Gender);
      comm.Parameters.AddWithValue("@par5", BirthDate);
      comm.Parameters.AddWithValue("@par6", AccessCode == null ? DBNull.Value: AccessCode);
      comm.Parameters.AddWithValue("@par7", BossId == null ? DBNull.Value : BossId);

      // return de id van het nieuwe record
      Id = (int)comm.ExecuteScalar();
      return Id;
   }
}

• ExecuteScalar(): voer een query uit en geef een getal terug (i.e. id van ingevoegde record)

UPDATE query

voorbeeld 5: bewerken employee

Code voor Employee.UPdateInDb():

public void UpdateInDb() {
   using (SqlConnection conn = new SqlConnection(connString)) {
      // open connectie
      conn.Open();

      // voer SQL commando uit
      SqlCommand comm = new SqlCommand(
         @"UPDATE Employee
               SET firstname=@par1,lastname=@par2,email=@par3,gender=@par4,birthdate=@par5,accesscode=@par6,boss_id=@par7
               WHERE ID = @parID" , conn);
      comm.Parameters.AddWithValue("@par1", FirstName);
      comm.Parameters.AddWithValue("@par2", LastName);
      comm.Parameters.AddWithValue("@par3", Email);
      comm.Parameters.AddWithValue("@par4", Gender);
      comm.Parameters.AddWithValue("@par5", BirthDate);
      comm.Parameters.AddWithValue("@par6", AccessCode == null ? DBNull.Value : AccessCode);
      comm.Parameters.AddWithValue("@par7", BossId == null ? DBNull.Value : BossId);
      comm.Parameters.AddWithValue("@parID", Id);
      comm.ExecuteNonQuery();
   }
}

• gebruik ExecuteNonQuery() (enkel uitvoeren, niks teruggeven)

optioneel: views en stored procedures

Voor wie vertrouwd is met database views en stored procedures en daar gebruik van wil maken in WPF, hier een paar codevoorbeelden.

Views

De code voor het ophalen van gegevens uit een view is identiek aan een normale SELECT query. Veronderstel een view vwEmployeesSimple:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) {
   conn.Open();
   SqlCommand comm = new SqlCommand("SELECT * FROM vwEmployeesSimple", conn); // view in plaats van tabel
   SqlDataReader reader = comm.ExecuteReader();
   lbxResults.Items.Clear();
   while (reader.Read()) {
      int id = Convert.ToInt32(reader["ID"]);
      string name = Convert.ToString(reader["name"]);
      string tel = Convert.ToString(reader["telephone"]);
      lbxResults.Items.Add($"{id}: {name} ({tel})");
   }
}

Stored procedures

De code verschilt lichtjes naargelang de stored procedure een returnwaarde heeft of niet.

Voorbeeld zonder return, stored procedure spAddEmployee met parameters @fn, @ln en @email:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) {
   conn.Open();
   SqlCommand comm = new SqlCommand("spAddEmployee", conn);
   comm.CommandType = CommandType.StoredProcedure;
   SqlParameter param1 = new SqlParameter("@fn", SqlDbType.VarChar);
   SqlParameter param2 = new SqlParameter("@ln", SqlDbType.VarChar);
   SqlParameter param3 = new SqlParameter("@email", SqlDbType.VarChar);
   param1.Value = txtFirst.Text;
   param2.Value = txtLast.Text;
   param3.Value = txtEmail.Text;
   comm.Parameters.Add(param1);
   comm.Parameters.Add(param2);
   comm.Parameters.Add(param3);
   comm.ExecuteNonQuery(); // zonder return: gebruik ExecuteNonQuery()
}

• duid aan dat het om een stored procedure gaat met comm.CommandType
• vul waarden voor de parameters van de stored procedure in met SqlParameter
• gebruik ExecuteNonQuery() als de stored procedure geen return heeft

Voorbeeld met return, stored procedure spSearchEmployee, die een lijst employees teruggeeft op basis van parameters @parSearch en @parLimit:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) {
   conn.Open();
   SqlCommand comm = new SqlCommand("spSearchEmployee", conn);
   comm.CommandType = CommandType.StoredProcedure;
   SqlParameter parSearch = new SqlParameter("@parSearch", SqlDbType.NVarChar);
   SqlParameter parLimit = new SqlParameter("@parLimit", SqlDbType.Int);
   parSearch.Value = "An";
   parLimit.Value = 3;
   comm.Parameters.Add(parSearch);
   comm.Parameters.Add(parLimit);
   SqlDataReader reader = comm.ExecuteReader(); // met return: gebruik ExecuteReader()
   while (reader.Read()) {
      // do something with the found employees
   }
}

• gebruik ExecuteReader() als de stored procedure een return heeft

connection pooling

Als je meerdere SQL instructies wil uitvoeren in de loop van je programma's, zijn er twee strategieën:

• één enkele connectie bij het begin van de eerste SQL instructie, en open houden tot na de laatste SQL instructie
• telkens bij elke SQL instructie een nieuwe connectie openen en zo snel mogelijk weer sluiten

De tweede optie is de juiste: in praktijk zal de server om tijd te winnen een aantal connecties permanent open houden zodat die snel kunnen toegewezen worden aan een programma dat die op dat moment nodig heeft. Door deze connectie zo snel mogelijk weer vrij te geven, kunnen andere databank gebruikers sneller weer aan de beurt komen. Dit heet connection pooling.

Eén connectie vroeg openen en laat sluiten is dus fout:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) { // open in het begin
   conn.Open();
   // doe andere taken
   // ...
   // voer eerste SQL query uit
   // ...
   // doe andere taken
   // ...
   // voer tweede SQL query uit
   // ...
   // doe andere taken
   // ...
}  // sluit op het einde

Open beter meerdere connecties zo laat mogelijk en sluit ze zo vroeg mogelijk:

string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
// doe andere taken
// ...
using (SqlConnection conn = new SqlConnection(connString)) { // open zo laat mogelijk
   conn.Open();
   // voer eerste SQL query uit
   // ...
} // sluit zo vroeg mogelijk
// doe andere taken
// ...
using (SqlConnection conn = new SqlConnection(connString)) { // open zo laat mogelijk
   conn.Open();
   // voer tweede SQL query uit
   // ...
} // sluit zo vroeg mogelijk
// doe andere taken
// ...

Dus het advies voor connecties: open late, close early!

Stap 4: aggregaties toevoegen

We hebben het in een eerder hoofdstuk reeds over aggregatie gehad: het gebruik van classes in classes. Als we naar de tabellen Employee en Job kijken, zijn er twee aggregaties:

1. elke Employee kan 0 of meer Jobs hebben
2. elke Employee kan hooguit 1 Employee als baas hebben

Schematisch (diagram gegenereer in SSMS):

In de toepassing zijn deze koppelingen op verschillende plaatsen zichtbaar:

In code implementeer je deze aggregaties best als read-only properties Employee.Boss en Employee.Jobs:

public class Employee {
   ...
   public Employee Boss {
      get {
         return BossId == null ? null : Employee.GetById((int)BossId);
      }
   }
   public List<Job> Jobs {
      get {
         List<Job> jobs = new List<Job>();
         // voer een SELECT query uit om alle jobs van deze employee op te vragen
         // ...
         return jobs;
      }
   }
   ...
}

WPF Application bouwen

We bouwen nu stap voor stap onze WPF application:

• XAML code van MainWindow
• code-behind van MainWindow
• andere vensters als EmployeeEditWindow, EmployeeNewWindow...

Stap 1: MainWindow

XAML

De XAML code van MainWindow:

<DockPanel LastChildFill="True">
   <StackPanel DockPanel.Dock="Bottom" Orientation="Horizontal" HorizontalAlignment="Right" Margin="10,0">
      <Button x:Name="btnEdit" Content="bewerk" Margin="10,0,10,10" IsEnabled="False" Padding="10,5" Click="BtnEdit_Click"/>
      <Button x:Name="btnRemove" Content="verwijder" Margin="10,0,10,10" IsEnabled="False" Padding="10,5" Click="BtnRemove_Click"/>
      <Button x:Name="btnNew" Content="nieuw..." Margin="10,0,10,10" Padding="10,5" Click="BtnNew_Click"/>
   </StackPanel>
   <Grid DockPanel.Dock="Right" Width="250" Margin="20,10" Background="#FFFCFAFA">
      <Grid.ColumnDefinitions>
         <ColumnDefinition Width="auto" />
         <ColumnDefinition Width="*" />
      </Grid.ColumnDefinitions>
      <Grid.RowDefinitions>
         <RowDefinition Height="30"/>
         <RowDefinition Height="30"/>
         <RowDefinition Height="30"/>
         <RowDefinition Height="30"/>
         <RowDefinition Height="30"/>
         <RowDefinition Height="*"/>
      </Grid.RowDefinitions>
      <Label Content="Email" FontWeight="Bold" />
      <Label Grid.Column="1" x:Name="lblEmail" />
      <Label Grid.Row="1" Content="Geslacht" FontWeight="Bold" />
      <Label Grid.Row="1" Grid.Column="1" x:Name="lblGender" />
      <Label Grid.Row="2" Content="Geb.datum" FontWeight="Bold" />
      <Label Grid.Row="2" Grid.Column="1" x:Name="lblBirthdate" />
      <Label Grid.Row="3" Content="Toeg.code" FontWeight="Bold" />
      <Label Grid.Row="3" Grid.Column="1" x:Name="lblCode" />
      <Label Grid.Row="4" Content="Baas:" FontWeight="Bold" />
      <Label Grid.Row="4" Grid.Column="1" x:Name="lblBoss" />
      <Label Grid.Row="5" Content="Jobs:" FontWeight="Bold" />
      <TextBlock Grid.Row="5" Grid.Column="1" x:Name="txtJobs"  TextWrapping="Wrap" Margin="5" />
   </Grid>
   <ListBox x:Name="lbxResults" Margin="20,10,0,10" SelectionChanged="LbxResults_SelectionChanged"/>
</DockPanel>

code-behind skelet

Voeg in elke C# file waar je de class library wil gebruiken een using directive toe:

Het skelet van MainWindow.xaml.cs (we implementeren de methodes straks):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using CLEmployees;

namespace WpfCompany {
   public partial class MainWindow : Window {
      // methode voor weergave employees
      private void ShowEmployees(int? selectedId = null) { /* TODO */ }

      // button events
      private void BtnEdit_Click(object sender, RoutedEventArgs e) { /* TODO */ }
      private void BtnNew_Click(object sender, RoutedEventArgs e) { /* TODO */ }
      private void BtnRemove_Click(object sender, RoutedEventArgs e) { /* TODO */ }

      // selectie employee uit de lijst
      private void LbxResults_SelectionChanged(object sender, SelectionChangedEventArgs e) { /* TODO */ }

      // MainWindow() constructor; start met tonen alle employees
      public MainWindow() {
         InitializeComponent();
         ShowEmployees();
      }
   }
}

Stap 3: EmployeeEditWindow en EmployeeNewWindow

We voegen nieuwe WPF windows toe aan WpfCompany voor toevoegen en bewerken van een Employee via Add, Window (WP)...

XAML

De XAML code voor EmployeeEditWindow en EmployeeNewWindow is bijna identiek. De XAML van EmployeeNewWindow:

<Grid Margin="20,20">
   <Grid.ColumnDefinitions>
      <ColumnDefinition Width="120" />
      <ColumnDefinition />
   </Grid.ColumnDefinitions>
   <Grid.RowDefinitions>
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="auto" />
      <RowDefinition Height="*" />
   </Grid.RowDefinitions>
   <Label Content="Voornaam *: "/>
   <TextBox Grid.Column="1" x:Name="txtFirst" Margin="0,0,0,10" Padding="5" />
   <Label Grid.Row="1" Content="Achternaam *: "/>
   <TextBox Grid.Column="1" Grid.Row="1" x:Name="txtLast" Margin="0,0,0,10" Padding="5" />
   <Label Grid.Row="2" Content="Email *: "/>
   <TextBox Grid.Column="1" Grid.Row="2"  x:Name="txtEmail" Margin="0,0,0,10" Padding="5" />
   <Label Grid.Row="3" Content="Geslacht *: "/>
   <StackPanel Grid.Column="1" Grid.Row="3">
      <RadioButton GroupName="grpGender" x:Name="rbnMale" Content="man" />
      <RadioButton GroupName="grpGender" x:Name="rbnFemale" Content="vrouw" />
      <RadioButton GroupName="grpGender" x:Name="rbnUnknown" Content="onbekend" />
   </StackPanel>
   <Label Grid.Row="4" Content="Geb.datum: *"/>
   <DatePicker x:Name="datBirth" Grid.Column="1" Grid.Row="4" HorizontalAlignment="Left" Margin="0,0,0,10" VerticalAlignment="Top"/>
   <Label Grid.Row="5" Content="Toegangscode: *"/>
   <TextBox Grid.Column="1" Grid.Row="5"  x:Name="txtCode" Margin="0,0,0,10" Padding="5"  HorizontalAlignment="Left" HorizontalContentAlignment="Right" Width="50" />
   <Label Grid.Row="6" Content="Baas: "/>
   <ComboBox Grid.Column="1" Grid.Row="6"  x:Name="cmbBoss" Margin="0,0,0,10" Padding="5"  HorizontalContentAlignment="Left">
      <ComboBoxItem Content="selecteer..." Tag="-1" IsSelected="True"></ComboBoxItem>
   </ComboBox>
   <StackPanel Grid.Column="1" Grid.Row="7" Orientation="Horizontal" HorizontalAlignment="Right">
      <Button x:Name="btnSave" Content="opslaan" Padding="10,5" VerticalAlignment="Bottom" Click="btnSave_Click" />
      <Button x:Name="btnCancel" Content="annuleren" Margin="10,0,0,0" Padding="10,5" VerticalAlignment="Bottom" Click="btnCancel_Click" />
   </StackPanel>
</Grid>

code-behind skelet

Het skelet van EmployeeEditWindow.xaml.cs en EmployeeNewWindow.xaml.cs lijkt ook sterk op elkaar:

using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;

namespace WpfCompany {
   public partial class EmployeeEditWindow : Window {
      // EmployeeEditWindow() constructor
      public EmployeeEditWindow(int id) {
         InitializeComponent();
         // TODO: haal employee op en vul alle velden in
         // ...
      }

      // button events
      private void btnCancel_Click(object sender, RoutedEventArgs e) { this.Close(); }
      private void btnSave_Click(object sender, RoutedEventArgs e) { /* TODO */ }
   }
}

using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;

namespace WpfCompany {
   public partial class EmployeeNewWindow : Window {
      // EmployeeNewWindow() constructor
      public EmployeeNewWindow() {
         InitializeComponent();
         // TODO: genereer de lijst van bazen
         // ...
      }

      // button events
      private void btnCancel_Click(object sender, RoutedEventArgs e) { this.Close(); }
      private void btnSave_Click(object sender, RoutedEventArgs e) { /* TODO */ }
   }
}

openen vanuit MainWindow()

Een nieuw venster openen vanuit het hoofdvenster kan via de constructor, b.v. de code om EmployeeNewWindow te openen met BtnNew_Click wordt dan:

Let op: voor EmployeeEditWindow is het iets moeilijker, omdat we op één of andere manier ook de id van de te bewerken gebruiker moeten meegeven. Hoe dit gebeurt wordt beschreven in het deel Gegevens persisteren en doorgeven

Stap 3: code uitwerken m.b.v. de class library methodes

Je kan nu de klassen Employee en Job uit de class library gebruiken in je hoofdproject.

voorbeeld 1: vullen lijst employees (MainWindow)

We schrijven een methode ShowEmployees(), die we oproepen in MainWindow():

public MainWindow() {
    InitializeComponent();
    ShowEmployees();
}

public void ShowEmployees(int? selectedId = null) {
    // wis lijst en labels
    lbxResults.Items.Clear();
    lblEmail.Content = lblGender.Content = lblBirthdate.Content = lblCode.Content = lblBoss.Content = "";

    // laad alle werknemers in
    List<Employee> allEmployees = Employee.GetAll();
    foreach (Employee emp in allEmployees) {
        lbxResults.Items.Add(new ListBoxItem() {
            Content = $"{emp.Id}: {emp.FirstName} {emp.LastName}",
            Tag = emp.Id, // onthoud de id in de Tag attribuut voor later
            IsSelected = selectedId == emp.Id
        });
    }
}

• merk op hoe eenvoudig en kort onze code is dankzij de class library!

voorbeeld 2: ophalend details employee (MainWindow)

We schrijven een methode ShowEmployees(), die we oproepen in MainWindow():

private void LbxResults_SelectionChanged(object sender, SelectionChangedEventArgs e) {
   // vraag employee op en stel button states in
   ListBoxItem item = (ListBoxItem)lbxResults.SelectedItem;
   btnEdit.IsEnabled = item != null;
   btnRemove.IsEnabled = item != null;
   if (item == null) return;
   Employee employee = Employee.GetById(Convert.ToInt32(item.Tag)); // haal de id weer op uit de tag

   // toon details
   lblEmail.Content = employee.Email;
   lblGender.Content = employee.Gender == GenderType.Man ? "man" : employee.Gender == GenderType.Vrouw ? "vrouw" : "onbekend";
   lblBirthdate.Content = employee.BirthDate.ToShortDateString();
   lblCode.Content = employee.AccessCode == null ? "-" : employee.AccessCode.ToString();
   lblBoss.Content = employee.Boss == null ? "-" : employee.Boss.ToString();
   List<string> jobTitles = employee.Jobs.Select(j => j.JobTitle).ToList<string>();
   txtJobs.Text = jobTitles.Count == 0
      ? "geen opgegeven"
      : (string.Join(Environment.NewLine, jobTitles) + (employee.Jobs.LastOrDefault().EndDate == null ? "(huidig)" : " (gestopt)"));
}

• merk op dat nergens SQL code in je hoofdprogramma voorkomt, alles zit verborgen in de class library

voorbeeld 3: lijst bazen vullen (EmployeeNewWindow)

De code voor het vullen van de lijst bazen:

List<Employee> allEmployees = Employee.GetAll();
   allEmployees.Sort((x, y) => x.LastName.CompareTo(y.LastName));
   foreach (Employee emp in allEmployees) {
      cmbBoss.Items.Add(new ComboBoxItem() {
         Content = emp,
         Tag = emp.Id // onthoud weer de id in een Tag attribuut
      });
   }
   

voorbeeld 4: nieuwe employee opslaan (EmployeeNewWindow)

De code voor het opslaan van de employee:

private void btnSave_Click(object sender, RoutedEventArgs e) {
   // werknemer opslaan
   int bossId = Convert.ToInt32(((ComboBoxItem)cmbBoss.SelectedItem).Tag); // haal boss id op uit Tag attribuut
   Employee employee = new Employee() {
      FirstName = txtFirst.Text,
      LastName = txtLast.Text,
      Email = txtEmail.Text,
      Gender = rbnMale.IsChecked == true ? GenderType.Man : rbnFemale.IsChecked == true ? GenderType.Vrouw : GenderType.Onbekend,
      BirthDate = (DateTime)datBirth.SelectedDate,
      AccessCode = txtCode.Text == String.Empty ? null : (int?)Convert.ToInt32(txtCode.Text),
      BossId = bossId == -1 ? null : (int?)bossId
   };
   employee.InsertToDb();

   // herlaad hoofdvenster, en sluit dit venster
   ((MainWindow)Application.Current.MainWindow).ShowEmployees(employee.Id);
   this.Close();
}

Gegevens persisteren en doorgeven

Bewaren in de Tag property

De Tag property van WPF controls is ideaal om de id van de gerelateerde records te onthouden:

ListBoxItem item = new ListBoxItem();
...
item.Tag = id; // onthoud de id in de ListBoxItem
lbxResults.Items.Add(item);

Bij selectie van een control kan je de id dan weer opvragen uit de Tag property:

ListBoxItem item = (ListBoxItem)lbxResults.SelectedItem;
int employeeId = Convert.ToInt32(item.Tag); // vraag de id weer op
...

Doorgeven via de Window constructor

Bij het openen van EmployeeEditWindow geven we de id van de employee mee aan de constructor:

private void BtnEdit_Click(object sender, RoutedEventArgs e) {
   ...
   EmployeeEditWindow editWin = new EmployeeEditWindow(employeeId);
   editWin.Show();
}

In EmployeeEditWindow.xaml.cs vangen we dit weer op in de constructor, en bewaren we het in een globale variabele employeeId:

public partial class EmployeeEditWindow : Window {
   int employeeId;

   public EmployeeEditWindow(int id) {
      InitializeComponent();
      employeeId = id;
   }
   ...
}

Je kan via de Window constructor vanalles meegeven, zelfs een referentie naar het hoofdvenster zelf, en bijvoorbeeld een methode van het hoofdvenster uitvoeren vanuit een popup. Codevoorbeeld:

public partial class MainWindow : Window {
   public void Test() { }
   public MainWindow() {
      InitializeComponent();
      SomeWindow win = new SomeWindow(this); // geef referentie naar dit venster mee
   }
}

public partial class SomeWindow : Window {
   public SomeWindow(MainWindow mainWin, int nr) {
      InitializeComponent();
      mainWin.Test(); // roep een methode op van MainWindow()
   }
}

Via App.config

Er zijn veel manieren om te communiceren tussen vensters. Algemene configuratieparameters voor je project kan je definiëren in App.config:

<configuration>
  <appSettings>
    <add key="someKey" value="ABC123" />
  </appSettings>
  ...
</configuration>

Oproepen vanuit je C# code:

string someKey = ConfigurationManager.AppSettings["someKey"];

Via Application.Current.Properties

Je kan ook gegevens over alle vensters heen onthouden in de Application.Current.Properties dictionary (een soort cookies maar dan voor WPF zeg maar):

Application.Current.Properties["someKey"] = "ABC123";

 string someKey = Application.Current.Properties["someKey"].ToString();

→ wees hier zuinig mee; voor gegevens die voor één enkel ander venster bedoeld zijn gebruik je beter de constructor parameters (zie eerder)

Via Application.Current.MainWindow

Via Application.Current.MainWindow kan je aan het hoofdvenster en alle publieke properties en methodes, zoals we in de Edit- en New windows gedaan hebben:

((MainWindow)Application.Current.MainWindow).ShowEmployees(employeeId);

Met "hoofdvenster" wordt het venster bedoeld waarmee de app opgestart is, zoals vastgelegd in App.xaml:

<Application x:Class="WpfAdmin.App" ... StartupUri="MainWindow.xaml">
   ...
</Application>

Exception handling

Net zoals bij bestanden kunnen bij databanken onverwachte fouten optreden: de connectie kan geweigerd worden, de databank kan offline zijn… Er zijn verschillende momenten en manieren om aan exception handling te doen. Het basisprincipe is: vang excepties zo laat mogelijk op. Vuistregels:

• De class library gooit excepties op met throw, liefst met een aangepaste melding
• De code-behind vangt excepties op met catch
• Toon waar mogelijk foutmeldingen in een TextBlock, niet via een MessageBox: dit is veel gebruiksvriendelijker
• Implementeer exception handling zo laat mogelijk tijdens ontwikkeling, als je code bijna af is; dit vermijdt overbodig werk bij code die de eindversie niet haalt.

Een mogelijk voorbeeld van een throw Employee.cs uit de class library:

public static Employee GetById(int empId) {
   using (SqlConnection conn = new SqlConnection(connString)) {
      // open connectie en voer SQL commando uit
      // ...

      // lees en verwerk resultaten
      if (!reader.Read()) throw new KeyNotFoundException($"employee met id {empId} niet gevonden");
      return new Employee() {
         // ...
      };
   }
}

De catch in de code-behind van onze WPF toepassing:

private void LbxResults_SelectionChanged(object sender, SelectionChangedEventArgs e) {
   // vraag employee op
   Employee employee = null;
   try {
      employee = Employee.GetById(Convert.ToInt32(item.Tag));
   } catch (KeyNotFoundException e) {
      // ... toon foutmelding 1
   } catch (SqlException ex) {
      // ... toon foutmelding 2
   } catch (Exception ex) {
      // ... toon foutmelding 3
   }

   // toon details
   // ...
}

Gebruik AI

Beschouw AI als een nieuwe collega die komt meehelpen aan je project. Het is belangrijk dat je de juiste context schetst en zoveel mogelijk informatie meegeeft.

Zet vooraf zelf al het project op:

1. voorbereiding (doe dit vooraf zelf): opzetten databank, opzetten solution en configureren solution

Bereid dan chatGPT voor:

2. chatGPT Project: open chatGPT (plus), en maak een nieuw project , b.v. OOAD project
3. bestanden: voeg alle nuttige bestanden toe, b.v. SQL dump (zonder data), projectopgave...
4. context: leg chatGPT uitvoerig uit wie je bent, waar het vak en het project over gaat, welke technieken zullen gebruikt worden, een link naar de online cursus enz...; alles wat je ook een nieuwe collega zou uitleggen
5. beginsituatie: zeg dat je al een solution hebt met drie projecten, de packages die je toegevoegd hebt, de databank die je opgezet hebt enz...

Dan pas begin je met de uitvoering:

5. eerste klasse aanmaken: vraag aan de hand van de SQL b.v. Company.cs aan te maken, met alle properties en typische CRUD methodes als Update(), GetById(), GetAll() enz...
6. controle: controleer de gegenereerde code, en pas/vul aan waar nodig.
7. andere klassen: herhaal voor de andere klassen
8. WPF app: werk nu verder met AI de WPF app uit
9. ...

Succes!