Databanken
voor C# programmeurs

Philip van Oosten   vanoosten.be

syntra

2019

Databanken

Deze module:

  • Relationele databanken en SQL
  • SQL Server Database projects (SSDT)
  • Voorbeeld EF Core, Database First

Vragen stellen

Op Moodle komt er

  • Forum voor vragen aan de groep
  • Dialoog voor individuele vragen

Tools

Je hebt enkele tools nodig om van start te kunnen gaan in deze module:

SQL Server

Express of Developer edition, versie 2017

Downloadsite → scroll naar beneden tot Of download een gratis speciale editie

→ Developer of Express editie downloaden

Visual Studio 2019

Heb je later ook nodig voor C#

SSDT (SQL Server Data Tools)

  • Visual Studio Installer

SSMS

SQL Server Management Studio

De link voor de installatie verschijnt ook tijdens installatie van SQL Server.

Visual Studio extensie voor mooie SQL

Poor Man’s T-Sql Formatter VS Extension

GitHub account

  • Maak je eigen account aan op GitHub

GitHub account

  • Maak een private repository met README.md.
  • Nodig mij (pvoosten) uit als Collaborator
  • Zet een link naar je repository in de dialoog op Moodle.

Private repository voor Visual Studio maken op GitHub

Ter info: GitHub repo openen in Visual Studio

Inleiding tot sql

  • SQL = Structured Query Language
  • Relationele databanken ontwikkeld vanaf 1970, SQL pas later
  • Er bestaat een SQL standaard, maar in de praktijk verschillende dialecten

SQL is verdeeld in DML en DDL

  • DML = Data Manipulation Language, daar beginnen we mee
  • DDL = Data Definition Language

Voorbeelden SQL

SELECT * from artists
where name='Aerosmith'

Voorbeelden SQL

select * from albums where artistid=3

Voorbeelden SQL

SELECT *
FROM tracks
WHERE AlbumId = 5

Voorbeelden SQL

SELECT *
  FROM tracks
 WHERE AlbumId = 1
UNION
SELECT *
  FROM tracks
 WHERE AlbumId = 2;

Voorbeelden SQL

SELECT customers.firstname AS voornaam,
       firstname ookVoornaam,
       'constante waarde' const,
       11 * 55 veelvoud
  FROM customers;

Online documentatie

https://docs.microsoft.com

T-SQL reference

SQL

Structured Query Language

Beschrijvende taal om met een (relationele) database te communiceren

DML ⇆ DDL

DML

Data Manipulation Language

→iets doen met de inhoud van een databank

  • SELECT: ophalen
  • INSERT: toevoegen
  • UPDATE: wijzigen
  • DELETE: verwijderen

DDL

Data Definition Language

→iets doen met de structuur van een databank

  • CREATE
  • ALTER
  • DROP

DDL

Structuur bestaat uit objecten:

  • Tabel
  • View
  • Procedure
  • Sleutel

SQL

is een beschrijvende taal (↔︎ imperatieve talen)

WAT, niet HOE.

Statement en Clause

Statement

Volledige opdracht in SQL, bv

SELECT * FROM TABELLEKE

Clause

Specifiek onderdeel van een statement, bv. SELECT * en FROM TABELLEKE zijn 2 clauses in het statement hierboven

Online referentie DML

T-SQL reference

  • Data types
  • Language elements
    • Expressions
    • Operators
    • Reserved keywords → [] waar nodig
  • Functions (zie verder voor meer)
  • Queries
  • Statements

SELECT statement

SELECT <kolomdefinities>
FROM <tabel of databron>
WHERE <filter records>
GROUP BY <kolom gefinieerd in FROM-clause>
HAVING <filter groepen gemaakt door GROUP BY>
ORDER BY <kolom uit SELECT list>

SELECT statement

SELECT <kolomdefinities>
  • op basis van resultaat uit FROM-clause
  • OF letterlijk ingegeven kolomwaarden
  • functies, operatoren en expressies mogelijk
    • SELECT met 1 rij en 1 kolom als resultaat mag ook
  • alias mogelijk

SELECT statement: alias

Mogelijkheden:

  • Zonder alias: SELECT abc
  • Met AS: SELECT abc AS def
  • Zonder AS: SELECT abc def
  • Met alias-uitdrukking: SELECT def = abc

SELECT statement, FROM clause

FROM <databron>
  • Databron:
    • 1 tabel of view uit de database
    • Een SELECT statement (subquery), eventueel met alias
      • Gelinkte vs niet-gelinkte subquery
      • Diep nesten van SELECT statements mogelijk; performantie moeilijk te achterhalen → profiler nodig

SELECT statement, FROM clause

FROM <databron>
  • Databron:
    • Een combinatie van databronnen
      • JOIN: INNER JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
      • De combinatie kan een alias krijgen

SELECT statement, FROM clause

FROM <databron>
  • Subquery vs JOIN: performantie
    • Veel gejoinde tabellen slecht voor performantie
    • Gelinkte subquery + veel records slecht voor performantie

SELECT statement, WHERE clause

WHERE <filter records>
  • Gebruik predicaten (uitdrukking die waar of vals zijn) om records al dan niet toe te voegen aan het resultaat +Het record kan (maar moet niet) gebruikt worden in een predicaat
    • Subqueries mogelijk
  • AND, OR, NOT logische operatoren
  • Predicaten groeperen met haakjes

SELECT statement, WHERE clause

WHERE <filter records>
  • IN, ANY, ALL
    • Met lijst waarden
    • Met SELECT statement met 1 kolom als resultaat
  • LIKE ‘%XX_X%’
  • IS NULL, IS NOT NULL

SELECT statement, GROUP BY clause

GROUP BY <kolommen gedefinieerd in FROM-clause>
  • Groepeer resultaten per zelfde set waarden
  • Zonder GROUP BY geen HAVING mogelijk
  • SELECT en ORDER BY beperkt tot
    • Kolommen in GROUP BY
    • Resultaat van aggregate functions
    • Expressies op basis van de 2 bovenstaande
    • Expressies volledig los van de FROM clause

SELECT statement, GROUP BY clause

GROUP BY <kolommen gedefinieerd in FROM-clause>
  • WHERE heeft voorrang op GROUP BY

SELECT statement, HAVING clause

HAVING <filter groepen gemaakt door GROUP BY>
  • Lijkt op WHERE, maar voor groepen gemaakt in GROUP BY statement
  • WHERE is meestal performanter dan HAVING, als er een keuze is
  • WHERE heeft voorrang op HAVING en GROUP BY

SELECT statement, ORDER BY clause

ORDER BY <kolom uit select list>
  • ASC (default) of DESC
  • Sorteren op meerdere kolommen mogelijk
  • Met GROUP BY: enkel sorteren op gegroepeerde kolommen

SELECT statement, ORDER BY clause

ORDER BY <kolom uit select list>
  • Optioneel: OFFSET n FETCH m ONLY → paginering
  • SELECT TOP (… rest van sql query)

Ingebouwde functies (zie MS documentatie)

  • Eenvoudiger
    • Aggregate (meestal met GROUP BY)
    • Date and time → werken met datum en tijd
    • Mathematical → wiskundig
    • Logical
    • String → Merk op: niet || maar + om strings aan elkaar te plakken

Ingebouwde functies (zie MS documentatie)

  • Iets moeilijker qua syntax
    • Analytic
    • Ranking

INSERT statement

Toevoegen van records

INSERT INTO schemanaam.tabelnaam(kolom1, kolom2, …)
VALUES (waarde1, waarde2, …)

INSERT statement

Toevoegen van records

Vanuit C# zal je meestal parameters gebruiken:

INSERT INTO schemanaam.tabelnaam(kolom1, kolom2, …)
VALUES (@param1, @param2, …)

In plaats van VALUES een SELECT statement

INSERT INTO schemanaam.tabelnaam(kolom1, kolom2, …)
SELECT

Voorbeeld INSERT met SELECT statement

INSERT INTO GrieksAlfabet(GriekseLetter, PlaatsInAlfabet)
SELECT GriekseLetter, PlaatsInAlfabet
FROM GrieksAlfabet;

UPDATE statement

Wijzigen van records

UPDATE tabelnaam
SET
kolom1 = waarde1,
kolom2 = waarde2,

WHERE <predicaat>

UPDATE statement

Wijzigen van records

  • WHERE is optioneel, maar wijzigt ALLE records indien niet aanwezig
  • Waarden kunnen ook expressies zijn, bv. CASE

DELETE statement

Verwijderen van records

DELETE
FROM tabelnaam
WHERE <predicaat>
  • WHERE kan je weglaten, maar dan worden alle records verwijderd
  • FROM keyword is optioneel, tabelnaam verplicht

DELETE statement

Verwijderen van records

  • Soms beter een kolom DELETED (timestamp) aanmaken dan effectief verwijderen
  • Gebruik liever TRUNCATE als het de bedoeling is een tabel leeg te maken

JOIN

  • In FROM clause in SELECT query
    • INNER JOIN
    • LEFT OUTER JOIN of LEFT JOIN
    • RIGHT OUTER JOIN of RIGHT JOIN
    • FULL OUTER JOIN
    • NATURAL JOIN (in andere DBMS systemen)

Documentatie

Subqueries

  • Select statements kunnen in verschillende clauses gebruikt worden:
    • SELECT → single-value resultaat
    • FROM → result set
    • WHERE (icm IN, ANY, ALL is er sprake van een subquery) → 1 kolom
    • ON

Subqueries

  • Gelinkte subquery
    • Wordt per record uitgevoerd om records te filteren (zware belasting van DBMS)
  • Niet-gelinkte subquery
    • Kan 1 keer worden uitgevoerd voor de volledige resultset
    • Bij voorkeur niet-gelinkte ipv gelinkte subqueries gebruiken

Documentatie

Subqueries vs Joins

Zware belasting van de server kan door

  • Veel tabellen te joinen (door Cartesisch product)
  • Subqueries te gebruiken die vaak moeten uitgevoerd worden

Subqueries vs Joins

Queries met subqueries kunnen herschreven worden als queries met joins

  • performantie analyseren om keuze te maken
    • Soms evident, soms zeer complex

JOIN

Installeer de database uit het sql-script JoinVoorbeelden.sql

Cartesisch product

Alles uit de ene tabel combineren met alles uit de tweede tabel Dit is een “cross join” of “Cartesisch Product”

SELECT *
FROM GrieksAlfabet,
 WoordEnGetal;

Cartesisch product + WHERE

Alles uit de ene tabel combineren met alles uit de tweede tabel, op voorwaarde dat de waarden in de getalkolommen gelijk zijn

SELECT *
FROM GrieksAlfabet,
 WoordEnGetal
WHERE PlaatsInAlfabet = Getal;

Joinconditie buiten WHERE

De vorige query is duidelijker als het predicaat binnen een joinconditie wordt geschreven.

SELECT *
FROM GrieksAlfabet
JOIN WoordEnGetal ON PlaatsInAlfabet = Getal
WHERE Woord IN (
  'Een',
  'Twee',
  'Drie'
  );

Joinconditie buiten WHERE

Waarom duidelijker?

Predicaten voor samenvoegen tabel tot 1 set worden gescheiden van predicaten in de WHERE clause, waar records van de samengevoegde set gefilterd worden.

Sleutelwoord INNER

Het sleutelwoord INNER is optioneel bij een inner join

SELECT *
FROM GrieksAlfabet
INNER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal
WHERE Woord IN (
  'Een',
  'Twee',
  'Drie'
  );

Left outer join

SELECT *
FROM GrieksAlfabet
LEFT OUTER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;

Right outer join

SELECT *
FROM GrieksAlfabet
RIGHT OUTER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;

Full outer join

SELECT *
FROM GrieksAlfabet
FULL OUTER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;

Outer join

Outer join zonder sleutelwoord om te bepalen welk soort outer join is geen geldige syntax. Je moet dus LEFT, RIGHT of FULL gebruiken bij OUTER JOIN

/*
SELECT *
FROM GrieksAlfabet
OUTER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;
*/

Inner join

SELECT *
FROM GrieksAlfabet
INNER JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;

Zonder het sleutelwoord ‘INNER’ wordt een inner join gemaakt.

Inner join

Het sleutelwoord INNER wordt meestal weggelaten

SELECT *
FROM GrieksAlfabet
JOIN WoordEnGetal ON PlaatsInAlfabet = Getal;

Natural join

  • Is een inner join waarbij de joinconditie automatisch wordt bepaald.
  • Inner join voluit schrijven is meestal duidelijker
  • en robuuster (beter bestand tegen wijzigingen, migraties in de databank)
  • Niet in alle RDBMS beschikbaar.

Oefeningen SQL

DML

Met Chinook database

Chinook database, ook voor SQL Server

https://github.com/lerocha/chinook-database

==> Chinook_SqlServer.sql

Het database-diagram

Diagrammen

Je eigen oefeningen

Die kan je vinden in je private repository in de map Opdrachten

Met Chinook database.

Per cursist wordt een reeks oefeningen beschikbaar gemaakt in GitHub (eigen repository).

Examen DML verloopt op dezelfde manier.

Oefeningen SQL

  • Oefeningen maak je best met SSDT
  • In een .sql-bestand, met de opgave in commentaar.

Zorg voor propere, mooi geformatteerde, leesbare SQL.

Normalisatie

Databases maken met SSDT

Je kan met SSDT rechtstreeks met een SQL server DB verbinden en de databasestructuur wijzigen.

Volgende slides tonen screencasts van hoe je dat kan doen.

Verbinden met database server en Zoo database aanmaken

Nieuwe tabel aanmaken - indeling Design/SQL/Constraints view

Tabelnaam aanpassen, kolommen toevoegen en de database updaten

Data Tools Operations: Bekijken welke DDL statements precies worden uitgevoerd

Data Tools Operations

In de screencast uit de vorige slide zie je alleen een CREATE TABLE statement, in andere gevallen, als je iets wijzigt aan een tabel zal je ALTER TABLE statements te zien krijgen.

Primary key constraint

Primaire sleutel

De primaire sleutel van een tabel is een unieke sleutel. Dat betekent dat het een kolom of een combinatie van kolommen is waarvoor de waarde in elk record van de tabel uniek is.

De primaire sleutel kan op verschillende manieren in DDL genoteerd worden:

  • 1 naamloze PK door PRIMARY KEY na de kolomdefinitie te zetten
  • CONSTRAINT PK_naam PRIMARY KEY (kolom1,kolom2,...) met meestal maar 1 kolom

Automatisch de Id verhogen

Default values

Niet verplicht in te geven bij INSERT

Default values

  • Je mag kolommen bij INSERT weglaten als ze ofwel een default waarde hebben, ofwel NULL mogen zijn, of beiden samen.
  • Bij toevoegen van kolommen aan een nieuwe tabel die al records bevat: ofwel NULL ofwel DEFAULT ofwel beiden moeten opgegeven worden.

Unique constraint

Unique constraint

  • Een kolom (of combinatie van meerdere kolommen) waarvan de waarde voor elk record in de tabel uniek moet zijn
    • Voorbeeld in de screencast: de naam van een dier moet uniek zijn.

Check constraint

Bv. Een geboortedatum of datum wanneer een dier tot leven komt mag niet ingevuld worden vooraleer het zover is.

Foreign key constraint

Foreign key constraint

  • We bepalen een one-to-many of een-op-veel-relatie tussen Dier en Diersoort.
    • Een dier kan maar tot 1 diersoort behoren
    • Tot een diersoort kunnen meerdere dieren behoren.

Foreign key constraint

  • Als de tabel Diersoort nog niet bestond op het moment dat je dieren wil beginnen indelen in diersoorten:
    • Maak een tabel Diersoort met een Id-kolom die primaire sleutel is en automatisch verhoogt

Foreign key constraint

  • Als de tabel Diersoort nog niet bestond op het moment dat je dieren wil beginnen indelen in diersoorten:
    • Maak een kolom DiersoortId in de tabel Dier met zelfde type als Diersoort.Id (dus int).

Foreign key constraint

  • Als de tabel Diersoort nog niet bestond op het moment dat je dieren wil beginnen indelen in diersoorten:
    • Maak een foreign key van Dier.DiersoortId naar Diersoort.Id. Om die foreign key te kunnen maken moeten ofwel alle referenties al correct zijn ingevuld, ofwel moet Dier.DiersoortId NULL als waarde accepteren.

Foreign key constraint

  • Als de tabel Diersoort nog niet bestond op het moment dat je dieren wil beginnen indelen in diersoorten:
    • Uiteindelijk krijg je een structuur genormaliseerd tot 3NF (de derde normaalvorm).

Transacties

Meerdere DML-queries (CRUD) als 1 enkele operatie in de database uitvoeren

ACID

  • Atomic: meerdere statements worden in een transactie gezien als 1 ondeelbare, atomische eenheid

ACID

  • Consistent: een transactie kan de database niet in een ongeldige staat brengen. Alle constraints blijven altijd bewaakt

ACID

  • Isolated: tegelijk of parallel uitvoeren van verschillende transacties heeft hetzelfde effect alsof de transacties na elkaar, los van elkaar zouden worden uitgevoerd. De ene transactie ondervindt geen invloed van de andere.

ACID

  • Durable: eenmaal de transactie is uitgevoerd, blijft het effect van de transactie bewaard, zelfs in geval van stroom- of netwerkuitval.

ACID

  • Atomic
  • Consistent
  • Isolated
  • Durable

TRANSACTIES

DDL

T-SQL reference

CREATE

Aanmaken van nieuwe schema-objecten, zoals

  • Table
  • View
  • Procedure
  • Trigger

ALTER

Wijzigen van schema-objecten

DROP

DROP <soort object> <naam>

Kan falen als constraints na uitvoeren niet meer geldig zouden zijn.

Soorten database-objecten

Database

Beheerd door een DBMS + Voor SQL Server: een service + Services.msc (of tabblad Services in taakbeheer)  SQL Server … + SqlServerManager14.msc Connection string: nodig om verbinding te maken met de database via ADO.NET (o.a. in C#) + Database kan opgegeven worden in connection string (Initial Catalog=…) + Connection string kan ook naar DBMS instantie verwijzen

Table

  • Kolommen
  • Sleutels: primair, uniek
  • Constraints: check, foreign key
  • Triggers (procedure automatisch uitgevoerd na insert, update of delete, zie verder)
  • Indexen: manier om een kolom of een combinatie van kolommen snel te doorzoeken, dient om SELECT queries te optimaliseren. Kan negatief effect hebben op performantie van INSERT en UPDATE, omdat de index dan moet aangepast worden.

View

CREATE VIEW <naam> AS <select statement>

Opslaan van select statement in de database.

Voordeel: + Programma maakt gebruik van een view → database kan gewijzigd worden zonder programmacode te wijzigen + Kan gebruikt worden als subquery, kan SQL vereenvoudigen + Security: gebruiker kan enkel voor view, niet voor achterliggende tabellen toegang krijgen

Schema

Een schema kan gebruikt worden om tabellen en andere objecten te groeperen.

Aan een schema kan ook toegang voor een gebruiker of een rol ingesteld worden.

Overige

  • Synonym
  • Stored procedure
  • Function
  • Trigger
  • UDT (user defined data type)
  • Sequence (meestal IDENTITY ipv een expliciete sequence)
  • User, Role, permission

Aanvullingen SQL

  • GO in sqlcmd scripts
    • terzijde: sqlcmd.exe of Invoke-SqlCmd in PowerShell

  • SET IDENTITY_INSERT [dbo].[Verzorger] ON en ... OFF
    • Vermijden van foutmelding Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table ‘Verzorger’ when IDENTITY_INSERT is set to OFF.

  • INFORMATION_SCHEMA: schema met informatie over de structuur van de database

Configuratie van SQL Server

Zie bijgevoegd Word-document

Gebruiken van databases in C#

  • ADO.NET
  • ORM: object-relational mapping

ADO.NET

  • boilerplate code om verbinding te maken met een SQL Server database
    • Console applicatie
    • IDbConnection, CreateCommand, CommandText, ExecuteReader, …
    • Boilerplate voor een ORM met Dapper
    • Parameters toevoegen aan queries vanuit C#

ADO.NET boilerplate

using System;
using System.Data.SqlClient;

namespace VoorbeeldDbConnectieCSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            using(var con = new SqlConnection(@"Data Source=DESKTOP-OH6M77O\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "SELECT FirstName, LastName from Employee";
                var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    // DateTime tijd = reader.GetDateTime(0);
                    string firstName = reader.GetString(0);
                    string lastName = reader.GetString(1);
                    Console.WriteLine(firstName + " " + lastName);
                }
                con.Close();
            }
            Console.ReadLine();
        }
    }
}

Maken en deployen SQL Server database met SSDT

Project

Thema: nog te bepalen.

Maak een SQL Server Database project

Deploy naar SQL Server

Maak een EF Core DB-First model volgens recept.

Maak een Console-applicatie die iets eenvoudigs doet met het EF Core model.

Over deze slides

Philip van Oosten

vanoosten.be