Co budeme potřebovat?
Protože budeme komunikovat s SQL serverem, použijeme SQL jazyk. A abych nenosil dříví do lesa, budu předpokládat, že jeho úplné základy umíte (založení databáze, příkazy SELECT, INSERT, DELETE, UPDATE - pokud ne, tak doporučuji přečíst článek Úvod do jazyka SQL).
K testování budeme používat Visual Studio 2005, ale vše by mělo fungovat i na verzi 2008. Jako server Microsoft SQL Server 2005 Express Edition s Microsoft SQL Server Management Studio Express k administraci a založení databáze (ke stáhnutí - Microsoft SQL Server Express With Advanced Services SP2), která je zdarma.
Založíme si ukázkovou databázi
Pokud máme již vše potřebné nainstalované, spustíme si SQL Management Studio a objeví se nám přihlašovací obrazovka:
Všimněte si položky Server name. Skládá se z jména nebo IP adresy fyzického serveru + jména instance SQL serveru (pokud si nainstalujete MSSQL Express, jeho defaultní jméno je SQLEXPRESS). Tečka je zástupný symbol pro počítač na kterém se zrovna pracuje (localhost), proto by fungovalo i localhost\SQLEXPRESS, či 127.0.0.1\SQLEXPRESS. Pro nás bude tento údaj brzy důležitý při vytváření připojení.
Další položka, které je dobré si povšimnou je způsob autentifikace. MSSQL podporuje dva: Windows Authentication (přihlašováním stejným účtem jako jsme přihlášení ve Windows) a SQL Authentication (přihlášení jménem a heslem - účet nemusí existovat ve Windows). A protože jsme přihlášení jako administrátoři (pokud nejste, tak to udělejte, jinak vás server do aministrace nepustí), použijeme Windowsí autentifikaci.
Když se nám otevře hlavní okno programu (úspěšně jsme se přihlásili), klepneme pravým tlačítkem na Databases a vybereme New Database:
Databázi pojmenujeme kamaradi a potvrdíme klepnutím na OK. Rozbalíme si větev databází a pravým tlačítkem vyvoláme kontextové menu, kde vybereme otevření okna pro vykonání nového databázového scriptu New Query:
Tady je kód na vytvoření tabulky a několika záznamů, který vložíme do hlavního okna a stiskneme F5 pro spuštění:
CREATE TABLE [dbo].[SeznamKamaradu] (
[ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[DatumNarozeni] DATETIME,
[Jmeno] NVARCHAR(100),
[Prijmeni] NVARCHAR(100),
[Mesto] NVARCHAR(100)
)
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1988-7-2','Martin','Krejc','Praha')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1989-5-2','Eva','Slánská','Praha')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1989-7-14','Jakub','Petrovský','Liberec')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1987-5-24','Míša','Petrovská','Liberec')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1989-12-22','Martin','Petřválský','Liberec')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1986-1-12','Aneta','Lerchová','Hradec Králové')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1986-9-13','Petr','Žák','Praha')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1987-5-4','Lucie','Malá','Liberec')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1988-12-24','Adam','Rychnovský','Brno')
INSERT INTO [dbo].[SeznamKamaradu] ([DatumNarozeni],[Jmeno],[Prijmeni],[Mesto]) VALUES ('1989-8-16','Markéta','Jelínková','Liberec')
Pokud se nezobrazí žádné chyby, máme založenou databázi s tabulkou SeznamKamaradu a několika ukázkovými záznamy.
Co je to Connection String?
Connection String (česky připojovací řetězec) slouží k popisu připojení do databáze. Určuje přihlašovací údaje, adresu serveru, způsob zabezpečení a řadu dalších nastavení... Pamatujete na přihlašovací okno MSSQL Server Management Studia? Není to nic jiného než interaktivní a pohodlnější zadávání Connection Stringu. Stejně když klepnete na Connect, tak se ze zadaných údajů sestaví připojovací řetězec.
Nejdřív malé shrnutí: Máme databázový servertypu MSSQL (adresa: localhost\SQLEXPRESS), máme databázi (kamaradi) a pro přihlašování použijeme práva účtu se kterým jsme přihlášeni ve Windows (měl by to být administrátor). Náš connection string je:
Server=localhost\SQLEXPRESS;Initial Catalog=kamaradi;Trusted_Connection=yes;
Jednotlivé položky nastavení jsou oddělené středníkem (jméno1=hodnota1;jméno2=hodnota2;jméno3=hodnota3; ... atd), jejich význam je následující:
- Server=localhost\SQLEXPRESS - určuje cílový databázový stroj
- Database=kamaradi - jméno databáze, do které se po přihlášení přejde
- Trusted_Connection=yes - definuje, zda se má použít přihlašování přes účty ve Windows (pokud je nastaven na false, je nutné zadat i přihlašovací údaje: User Id=jméno;Password=heslo;)
Pokud máte zájem o podrobnější informace o připojovacích řetězcích, doporučuji tento web, který se jimi podrobně zabývá (bohužel in English): http://www.connectionstrings.com/?carrier=sqlserver2005 nebo konkrétněji podstránku přímo s popisem jednotlivých klíčových slov: http://www.connectionstrings.com/article.aspx?article=allsqlconnectionconnectionstringproperties
Pozor: Bohužel se nepodařilo 100% standartizovat připojovací řetězce pro všechny technologie a proto bývá často pro jednu věc více možných zápisů (například místo Server je možné napsat i Data Source, Address, Addr nebo Network Address) a tak se neleknětě pokud uvidíte někde zápis trochu jinak.
Založení projektu
Spustíme Visual Studio a založíme nový projekt typu Console Application. Protože by bylo dobré uchovávat Connection string pro celou aplikaci na jednom místě, vepíšeme ho do konfiguračního XML souboru app.config. Pokud jej v projektu ještě nemáte, přidejte nový soubor typu Application Configuration File a vložte do něj tuto definici:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="DatabazeKamaradu" value="Server=localhost\SQLEXPRESS;Initial Catalog=kamaradi;Trusted_Connection=yes;" />
</appSettings>
</configuration>
V kódu aplikace pak můžeme přistupovat k řetězci takto:
VB.NET
Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DatabazeKamaradu")
C#
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["DatabazeKamaradu"];
Abych nemusel pokaždé psát celý kód, tak tady je jeho základ:
VB.NET
Imports System.Data.SqlClient
Module Module1
Public Sub Main()
Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DatabazeKamaradu")
' > sem budeme vkládat funkční kód <
Console.ReadKey() ' vyčká na stisknutí klávesy, ať hned nezmizí výpis
End Sub
End Module
C#
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["DatabazeKamaradu"];
// > sem budeme vkládat funkční kód <
Console.ReadKey(); // vyčká na stisknutí klávesy, ať hned nezmizí výpis
}
}
Připojení do databáze
Vše je nyní připravené a můžeme se pustit konečně na samotný kód komunikace se serverem. Budeme využívat objektů ze jmeného prostoru System.Data.SqlClient.
System.Data.SqlClient.SqlConnection reprezentuje spojení s databází. Předává se mu výše popisovaný připojovací řetězec. Ukážeme si jak se připojit, zjistit o serveru pár informací a zase se odpojit:
VB.NET
Dim sqlConnection As New SqlConnection(ConnectionString) ' inicializace objektu spojení na databázi
sqlConnection.Open() ' pomocí dat z ConnectionStringu spojení otevřeme
Console.WriteLine(String.Format("Verze serveru: {0}", sqlConnection.ServerVersion)) ' zjištění verze serveru
Console.WriteLine(String.Format("Identifikace serveru: {0}", sqlConnection.WorkstationId)) ' a ID počítače na kterém běží
sqlConnection.Close() ' a zase uzavřeme
C#
SqlConnection sqlConnection = new SqlConnection(ConnectionString); // inicializace objektu spojení na databázi
sqlConnection.Open(); // pomocí dat z ConnectionStringu spojení otevřeme
Console.WriteLine(String.Format("Verze serveru: {0}", sqlConnection.ServerVersion)); // zjištění verze serveru
Console.WriteLine(String.Format("Identifikace serveru: {0}", sqlConnection.WorkstationId)); // a ID počítače na kterém běží
sqlConnection.Close(); // a zase uzavřeme
Pokud jste provedli všechno správně, odměnou vám bude informace o verzi a názvu stanice serveru.
Pozor: Připojení na server se snažte mít otevřené na co nejkratší dobu. Jejich počet je totiž omezen a pokud ho přesáhnete, bude se čekat než se uzavře jiné. Proto je dobré nenechávat při vykonávání delších operací připojení otevřené.
Vykonání dotazu
Dostáváme se prakticky k nejdůležitějšímu objektu. A to System.Data.SqlClient.SqlCommand, který popisuje dotaz proti databázi. Obsahuje textový SQL dotaz a odkaz na připojení pomocí kterého se příkaz provede.
Teď si ukážeme jak je možné zjistit počet položek v tabulce SeznamKamaradu příkazem:
SELECT COUNT(*) FROM [SeznamKamaradu]
VB.NET
Dim sqlConnection As New SqlConnection(ConnectionString) ' inicializace objektu spojení na databázi
Dim sqlCommand As New SqlCommand("SELECT COUNT(*) FROM [SeznamKamaradu]", sqlConnection) ' vytvoření dotazu a předání připojení
sqlConnection.Open() ' pomocí dat z ConnectionStringu spojení otevřeme
Console.WriteLine(String.Format("Počet kamarádů: {0}", CInt(sqlCommand.ExecuteScalar()))) ' provedení příkazu
sqlConnection.Close() ' a zase uzavřeme
C#
SqlConnection sqlConnection = new SqlConnection(ConnectionString); // inicializace objektu spojení na databázi
SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM [SeznamKamaradu]", sqlConnection); // vytvoření dotazu a předání připojení
sqlConnection.Open(); // pomocí dat z ConnectionStringu spojení otevřeme
Console.WriteLine(String.Format("Počet kamarádů: {0}", (int)(sqlCommand.ExecuteScalar()))); // provedení příkazu
sqlConnection.Close(); // a zase uzavřeme
Pokud se příkaz provedl správně, výstup nám oznámí, že počet kamarádů v databázi je 10.
Druhy provedení dotazu
V předchozím příkladě jsme viděli použití příkazu SqlCommand.ExecuteScalar(). To je však jen jedna ze 4 variant:
- sqlCommand.ExecuteNonQuery() - vykoná příkaz, ale nevrátí výsledek, jen počet řádků, které se příkazem změnili (proto je vhodný převážně pro vkládání, mazaní a upravování záznamů)
- sqlCommand.ExecuteScalar() - vykoná příkaz a vrátí jen první hodnotu, která je na výstupu (ideální pro dotazy vracející jen jednu hodnotu - viz. předchozí příklad, co zobrazí počet záznamů v tabulce)
- sqlCommand.ExecuteReader() - po vykonání příkazu vrací objekt SqlDataReader díky kterému máme možnost číst více řádků i více sloupců (využívá se pro čtení dotazů, které vrací celé řádky)
- sqlCommand.ExecuteXmlReader() - funguje podobně jako ExecuteReader, jen vrací data ve formátu XML (konkrétně objektem XmlReader)
Čtení více řádků
V minulé kapitolce jsme si popsali k čemu jsou jednotlivé funkce na vykonání příkazů. Teď si přiblížíme ExecuteReader pro přečtení více řádků výstupu. V ukázce si necháme vypsat všechny jména a města z naší tabulky SeznamKamaradu. Použijete dotaz:
SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu]
Zdrojový kód není o moc složitější než u minulého příkladu:
VB.NET
Dim sqlConnection As New SqlConnection(ConnectionString) ' inicializace objektu spojení na databázi
Dim sqlCommand As New SqlCommand("SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu]", sqlConnection) ' vytvoření dotazu a předání připojení
sqlConnection.Open() ' pomocí dat z ConnectionStringu spojení otevřeme
Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader() ' spuštění dotazu a vytvoření objektu na čtení řádků
' smyčka na čtení záznamů
Do While dataReader.Read ' posun na další řádky, dokud jsou k dispozici
Console.WriteLine(String.Format("Záznam: {0} ({1})", dataReader("Jmeno"), dataReader("Mesto"))) ' zobrazí záznam
Loop
dataReader.Close() ' nejdříve uzavřeme aktuální dotaz
sqlConnection.Close() ' a pak i spojení
C#
SqlConnection sqlConnection = new SqlConnection(ConnectionString); // inicializace objektu spojení na databázi
SqlCommand sqlCommand = new SqlCommand("SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu]", sqlConnection); // vytvoření dotazu a předání připojení
sqlConnection.Open(); // pomocí dat z ConnectionStringu spojení otevřeme
SqlDataReader dataReader = sqlCommand.ExecuteReader(); // spuštění dotazu a vytvoření objektu na čtení řádků
// smyčka na čtení záznamů
while (dataReader.Read()) // posun na další řádky, dokud jsou k dispozici
{
Console.WriteLine(String.Format("Záznam: {0} ({1})", dataReader["Jmeno"], dataReader["Mesto"])); // zobrazí záznam
}
dataReader.Close(); // nejdříve uzavřeme aktuální dotaz
sqlConnection.Close(); // a pak i spojení
Výsledek je sice jen jednoduchý výpis, ale myslím, že na demostrační účely to stačí:
Pozor: Do objektu SqlDataReader není možné přistupovat jako do pole. Čtení se provádí po řádcích (na další se přejde příkazem Read). To proto, že řádky se čtou z databáze postupně. Celé je to z důvodu, aby se v případě velkého množství dat nemuselo všechno načítat do paměti. A nezapomeňte uzavřít po dokončení čtení SqlDataReader! Pokud to neuděláte, zůstane čtecí kanál otevřený a některé další operace můžou skončit s chybou.
Parametry
Dejme tomu, že chceme zobrazit všechny osoby, které jsou z jednoho určitého města. Dotaz je jednoduchý:
SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu] WHERE [Mesto]='Město'
Pokud ale předem město neznáme a chceme aby si jej mohl uživatel sám zadat, nabízí se možnost SQL dotaz složit z řetězců. Například něco jako:
dotaz = "SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu] WHERE [Mesto]='" + mesto + "'"
To je ale nepraktické a hlavně velmi nebezpečné. Případný útočník má totiž možnost ovlivnit přímo to, jak dotaz vypadá a tím pádem se mu otevírá řada cest jak uškodit. Co takhle kdyby zadal, že chce zobrazit lidi z města 
? Celkový složený dotaz pak bude vypadat takhle:
Tím se vytvoří složená podmínka, která bude vždy pravdivá (protože 'a' = 'a'). A tak se zobrazí uplně všichni z tabulky, což zajisté nechceme. Možná se to zdá nyní jako banalita, ale pokud by nastalo něco podobného u mazání záznamů, tak můžeme přijít úplně o všechny data v tabulce.
Jak to ale řešit?
Obecně existují 2 možnosti. První z nich je zdvojovat ukončovací znaky (v našem případě apostrof ' ). Bohužel již bylo dokázáno, že to není jediná slabina skládaných dotazů. A navíc se může celkem snadno stát, že na to zapomenete a tím otevřete útočníkovi virtuální dveře do své databáze. Naštěstí ale .NET a MSSQL nabízejí mnohem elegantnější a bezpečnější řešení. Je to používání parametrů. Díky nim můžete předat hodnotu přímo bez nutnosti ji začlenit do dotazovacího SQL řetězce. Takové proměnné označujeme znakem @. Ukážeme si jak se bude řešit výběr podle města pomocí parametrů:
SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu] WHERE [Mesto]=@mesto
A zástupnou hodnotu pak přidáme do kolekce SqlCommand.Parameters. Toto řešení je naprosto bezpečné a celkem úhledné. Celý kód je zde:
VB.NET
Dim sqlConnection As New SqlConnection(ConnectionString) ' inicializace objektu spojení na databázi
Dim sqlCommand As New SqlCommand("SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu] WHERE [Mesto]=@Mesto", sqlConnection) ' vytvoření dotazu a předání připojení
Console.Write("Zadejte město: ") ' vyzvat k zadání
sqlCommand.Parameters.AddWithValue("@Mesto", Console.ReadLine()) ' přidat parametr do dotazu
sqlConnection.Open() ' pomocí dat z ConnectionStringu spojení otevřeme
Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader() ' spuštění dotazu a vytvoření objektu na čtení řádků
' smyčka na čtení záznamů
Do While dataReader.Read ' posun na další řádky, dokud jsou k dispozici
Console.WriteLine(String.Format("Záznam: {0} ({1})", dataReader("Jmeno"), dataReader("Mesto"))) ' zobrazí záznam
Loop
dataReader.Close() ' nejdříve uzavřeme aktuální dotaz
sqlConnection.Close() ' a pak i spojení
C#
SqlConnection sqlConnection = new SqlConnection(ConnectionString); // inicializace objektu spojení na databázi
SqlCommand sqlCommand = new SqlCommand("SELECT [Jmeno],[Mesto] FROM [SeznamKamaradu] WHERE [Mesto]=@Mesto", sqlConnection); // vytvoření dotazu a předání připojení
Console.Write("Zadejte město: "); // vyzvat k zadání
sqlCommand.Parameters.AddWithValue("@Mesto", Console.ReadLine()); // přidat parametr do dotazu
sqlConnection.Open(); // pomocí dat z ConnectionStringu spojení otevřeme
SqlDataReader dataReader = sqlCommand.ExecuteReader(); // spuštění dotazu a vytvoření objektu na čtení řádků
// smyčka na čtení záznamů
while (dataReader.Read()) // posun na další řádky, dokud jsou k dispozici
{
Console.WriteLine(String.Format("Záznam: {0} ({1})", dataReader["Jmeno"], dataReader["Mesto"])); // zobrazí záznam
}
dataReader.Close(); // nejdříve uzavřeme aktuální dotaz
sqlConnection.Close(); // a pak i spojení
Výsledný program by se měl při spuštění dotázat na město a pak vypsat všechny záznamy, které mu odpovídají. Možná města jsou: Praha, Liberec, Hradec Králové, Brno. Jen si dejte pozor na velikost písmen, pokud nenapíšete jméno města přesně, nenajde se z něj žádná odpovídající osoba.
Závěrem
To je pro dnešek asi všechno. Pokud máte jekékoliv dotazy, nestyďte se na ně zeptat v diskuzi. V příštím díle zkusím probram problematiku datových kolizí (zámky, izolace, transakce) a možná se dostane i na nejrůznější vychytávky MSSQL Serveru jako uložené procedury, triggery atp.