Lekce 7 - Databáze ve VB.NET - INSERT, UPDATE, DELETE a COUNT

V minulé lekci, SqlDataReader a připojená databáze ve VB.NET, jsme se naučili číst data z databáze pomocí třídy SqlDataReader a používat parametrizované dotazy, které znemožňují útok SQL injection.

V dnešním Databáze - ADO.NET tutoriálu se naučíme přidávat, mazat, editovat a počítat záznamy v MS-SQL databázi ve VB.NET pomocí třídy SqlCommand.

Základní databázové operace se často označují zkratkou CRUD. Jedná se o operace:

• Create (vytvořit),
• Read (číst),
• Update (aktualizovat),
• Delete (odstranit).

Z předchozích lekcí již známe operaci Read, čtení dat z databáze příkazem SELECT a třídu SqlDataReader. Dnes se naučíme zbývající databázové operace.

Pracujeme s aplikací z lekce Vytvoření lokální databáze ve Visual Studio ve VB.NET, kterou v jednotlivých lekcích upravujeme. 

Operace Create

Operaci Create si ukážeme na vložení nového slovíčka do databáze SQL příkazem INSERT. O anglické a české znění slovíčka požádáme uživatele:

Using pripojeni As SqlConnection = New SqlConnection(connectionString)
    pripojeni.Open()
    Console.WriteLine("Zadej nové slovíčko anglicky")
    Dim anglicky As String = Console.ReadLine()
    Console.WriteLine("Zadej nové slovíčko česky")
    Dim cesky As String = Console.ReadLine()
    Dim dotaz As String = "INSERT INTO Word (English, Czech) VALUES (@anglicky, @cesky)"

    Using sqlDotaz As SqlCommand = New SqlCommand(dotaz, pripojeni)
        sqlDotaz.Parameters.AddWithValue("@anglicky", anglicky)
        sqlDotaz.Parameters.AddWithValue("@cesky", cesky)
        Dim radku As Integer = sqlDotaz.ExecuteNonQuery()
        Console.WriteLine(radku)
    End Using
    pripojeni.Close()
End Using

SQL dotaz pro vložení nového řádku do tabulky začíná příkazem INSERT INTO, následuje název tabulky. V závorkách vypíšeme názvy sloupců, do kterých vkládáme hodnoty. Za klauzuli VALUES vypíšeme opět do závorky hodnoty pomocí nám již známých zástupných značek chránicích před SQL injekcí.

Dotaz sqlDotaz poté vložíme do instance třídy SqlCommand. Dodáme mu potřebné parametry a spustíme ho metodou ExecuteNonQuery(). Metoda ExecuteNonQuery() vrací počet ovlivněných řádků. Pro ověření, že vložení proběhlo, si je vypišme do konzole:

Konzolová aplikace
Zadej nové slovíčko anglicky
keyboard
Zadej nové slovíčko česky
klávesnice
1

Metoda ExecuteNonQuery() vrátila hodnotu 1.

Pro kontrolu můžeme spustit náš kód pro výpis všech slovíček nebo v okně SQL Server Object Explorer nahlédnout do naší databáze SlovnicekDB, tabulky dbo.Word.

v okně SQL Server Object Explorer vidíme, že slovíčko klávesnice se mezi slovíčky již nachází:

Operace Delete

Pomocí operace Delete si implementujeme mazání záznamů SQL příkazem DELETE:

Using pripojeni As SqlConnection = New SqlConnection(connectionString)
    pripojeni.Open()
    Console.WriteLine("Zadej anglické slovíčko, které chceš vymazat")
    Dim anglicky As String = Console.ReadLine()
    Dim dotaz As String = "DELETE FROM Word WHERE English=@anglicky"

    Using sqlDotaz As SqlCommand = New SqlCommand(dotaz, pripojeni)
        sqlDotaz.Parameters.AddWithValue("@anglicky", anglicky)
        Dim radku As Integer = sqlDotaz.ExecuteNonQuery()
        Console.WriteLine(radku)
    End Using
    pripojeni.Close()
End Using

VB.NET kód se vůbec nezměnil. SQL dotaz začíná DELETE FROM a pokračuje názvem tabulky. Následuje podmínka WHERE, kterou již dobře známe.

Na podmínku WHERE v příkazu DELETE nesmíme nikdy zapomenout. Došlo by k vymazání všech záznamů v tabulce!

Aplikaci opět vyzkoušejme:

Konzolová aplikace
Zadej anglické slovíčko, které chceš vymazat
keyboard
1

Zase si zkontrolujme, že klávesnice mezi slovíčky již opravdu není. Můžeme zkusit i zadat neexistující slovíčko k vymazání, počet ovlivněných řádků pak bude 0:

Konzolová aplikace
Zadej anglické slovíčko, které chceš vymazat
binary tree
0

Operace Update

Poslední databázovou operací je operace Update, kterou aplikujeme SQL příkazem UPDATE. Naučme aplikaci měnit český překlad nějakého anglického slova:

Using pripojeni As SqlConnection = New SqlConnection(connectionString)
    pripojeni.Open()
    Console.WriteLine("Zadej anglické slovíčko, u kterého chceš upravit překlad")
    Dim anglicky As String = Console.ReadLine()
    Console.WriteLine("Zadej nový překlad")
    Dim cesky As String = Console.ReadLine()
    Dim dotaz As String = "UPDATE Word SET Czech=@cesky WHERE English=@anglicky"

    Using sqlDotaz As SqlCommand = New SqlCommand(dotaz, pripojeni)
        sqlDotaz.Parameters.AddWithValue("@anglicky", anglicky)
        sqlDotaz.Parameters.AddWithValue("@cesky", cesky)
        Dim radku As Integer = sqlDotaz.ExecuteNonQuery()
        Console.WriteLine(radku)
    End Using
    pripojeni.Close()
End Using

VB.NET kód je opět velmi podobný. SQL dotaz začíná UPDATE, následuje název tabulky a dále klauzule SET. Po ní název sloupce, rovnítko a nová hodnota. Pokud bychom potřebovali aktualizovat více sloupců, zapíšeme to např. takto:

UPDATE Word SET Czech=@cesky, English=@anglicky WHERE Id=@id

Stejně jako u příkazu DELETE nikdy nesmíme zapomenout na klauzuli WHERE, jinak se změní všechny řádky v tabulce!

Aplikaci opět vyzkoušejme:

Konzolová aplikace
Zadej anglické slovíčko, u kterého chceš upravit překlad
dog
Zadej nový překlad
stopovat
1

Zkontrolujeme si, že se překlad změnil.

Hranaté závorky

Ještě jsme nezmínili použití hranatých závorek v dotazech. Pokud název nějakého sloupce koliduje s klíčovým slovem z SQL (např. sloupec se jmenuje Where), dáme ho do hranatých závorek. Často se setkáme s konvencí, kde se takto označují všechny názvy:

UPDATE [Word] SET [Czech]=@cesky, [English]=@anglicky WHERE [Id]=@id

Závěr

Výhodou připojeného přístupu k databázi je aktuálnost zpracovávaných dat i ve víceuživatelských aplikacích. Nevýhodou naopak může být časté připojování ke vzdálené databázi, větší objem přenesených dat i sekvenční čtení komponenty SqlDataReader, tedy například bez možnosti přechodu na předcházející záznam.

V následujícím cvičení, Řešené úlohy k 1.-7. lekci Databáze ve VB.NET - ADO.NET, si procvičíme nabyté zkušenosti z předchozích lekcí.

Měl jsi s čímkoli problém? Stáhni si vzorovou aplikaci níže a porovnej ji se svým projektem, chybu tak snadno najdeš.