Jak na to?
Microsoft SQL server
V dnešním článku vás naučíme základy spolupráce Visual Basicu a Microsoft SQL serveru.
Nebudeme však pracovat s daty, to je doména objektové technologie ADO. Zaměříme se spíše
na ovládání SQL serveru, na práci s databázemi, uživateli atd., a to pomocí objektů SQL-DMO.
Tyto objekty jsou instalovány jako součást MS SQL serveru, jak klienta tak i serveru.
V žádném případě nemůžete tyto objekty používat, nemáte-li licenci na SQL server.
Pokud vlastníte komplet MS SQL server, je pro vás používání těchto objektů vcelku
zbytečné, protože umí v podstatě to samé jako Enterprise manager. Máte-li však pouze
MSDE (Microsoft Data Engine), což je MS SQL server použitelný max. pro 5 uživatelů bez
nástrojů pro správu, můžete si sami vytvořit nástroje pro administraci serveru.
My budeme pro příklady v tomto článku používat MSDE verze 8 neboli 2000, což je vlastně
MS SQL server 2000 s výše popsanými omezeními.
Protože objekty SQL-DMO umí spoustu věcí, které nejsme schopni obsáhnout
v jednom článku, pojmeme tento text spíše jako referenční přiručku nápadů a triků,
nejdříve vás však musíme naučit základní činnosti, jako je např. připojení
k serveru. U čtenářů článku předpokládáme alespoň minimální znalosti MS SQL Serveru a databází.
Příprava projektu
Abyste mohli začít pracovat s objekty SQL-DMO, musíte si na ně ve Visual Basicu
vytvořit odkaz. Otevřete nový projekt (Standard EXE), zvolte menu
Project -> References a v otevřeném okně vyberte položku Microsoft SQLDMO Object Library.
Pokud ji tam nemáte, nainstalujte si nejdříve SQL server nebo MSDE. Tímto je projekt
připraven a už vám nic neschází k tomu, abyste začali programovat.
Připojení k serveru
Pokud znáte název serveru, nic nebrání tomu, abyste se hned připojili. Chcete-li však
dát uživateli na výběr, kam se připojit, bude se vám hodit následující kód, který
zobrazí dostupné SQL servery ve Vaší síti.
Private Sub ShowServers(cmb As ComboBox)
Dim sqlApp As New SQLDMO.Application
Dim nList As SQLDMO.NameList
Dim i As Long
Set nList = sqlApp.ListAvailableSQLServers
For i = 0 To nList.Count - 1
cboServers.AddItem nList.Item(i)
Next i
Set nList = Nothing
Set sqlApp = Nothing
End Sub
Procedura ShowServers naplní zadaný ComboBox seznamem serverů. Tento seznam
vrací metoda ListAvailableSQLServers objektu SQLDMO.Application .
SQL server je reprezentován objektem SQLDMO.sqlServer . Chcete-li se
k němu připojit, zavolejte metodu Connect . Předávané parametry
se různí podle způsobu připojení. Chcete-li použít Windows NT autentikaci, musíte nejdřív
nastavit vlastnost LoginSecure na hodnotu True
(v tomto případě zadáváte při připojení pouze název serveru). Jde-li
o autentikaci MS SQL serveru, nastavte tuto vlastnost na False (zde
musíte metodě Connect zadat navíc ještě uživatele a heslo).
Dim sqlSrv As SQLDMO.sqlServer
'Windows NT autentikace
sqlSrv.LoginSecure = True
sqlSrv.Connect "server"
'SQL server autentikace
sqlSrv.LoginSecure = False
sqlSrv.Connect "server", "uživatel", "heslo"
Výše uvedený kód vás tedy připojí na SQL server. Chcete-li se odpojit, zavolejte
metodu Disconnect .
sqlSrv.Disconnect
Seznam objektů
SQL server obsahuje spoustu objektů, např. Databáze, Tabulky, Uložené procedury atd.
Každý takový objekt má svého reprezentanta také v objektech SQLDMO, uloženého
v nějaké kolekci. Protože přístup k nim je víceméně stejný, uvedeme
si jejich výpis jen u některých a k tomu přidáme seznam
nejdůležitějších objektů a kolekcí.
Dim sqlDB As SQLDMO.Database
For Each sqlDB In sqlSrv.Databases
Debug.Print sqlDB.Owner & "." & sqlDB.Name
Next sqlDB
Dim sqlLogin As SQLDMO.Login
For Each sqlLogin In sqlSrv.Logins
Debug.Print sqlLogin.Name
Next sqlLogin
Dim sqlTB As SQLDMO.Table
For Each sqlTB In sqlSrv.Databases("master").Tables
Debug.Print sqlTB.Owner & "." & sqlTB.Name
Next sqlTB
První část kódu zobrazí všechny databáze na připojeném MS SQL serveru. Druhá
část zobrazí seznam uživatelů serveru (ne databáze). Třetí seznam tabulek databáze master.
Některé objekty mají vlastnost SystemObject , která je typu Boolean
a vrací True v případě, že je objekt vytvořen jako součást MS SQL serveru
při instalaci (např. systémové tabulky apod.). Protože ne každý uživatel chce
tyto objekty vidět, je dobré je při výpisu odfiltrovat.
Dim sqlTB As SQLDMO.Table
For Each sqlTB In sqlSrv.Databases("master").Tables
If Not sqlTB.SystemObject Then Debug.Print sqlTB.Owner & "." & sqlTB.Name
Next sqlTB
Samozřejmě nemá smysl tento výpis dělat na databázi master, která standardně
jiné než systémové tabulky neobsahuje, ale jde jen o příklad, vy si jej můžete
vyzkoušet na jiné databázi.
A teď již slíbený seznam nejdůležitějších objektů. Ke každému objektu
přidáme také kolekci, která tyto objekty obsahuje.
Objekt | Kolekce | Popis |
Database | sqlServer.Databases | Jeden z hlavních objektů, který
reprezentuje jednu databázi SQL serveru. |
Login | sqlServer.Logins | Uživatel SQL serveru. |
ServerRole | sqlServer.ServerRoles | Skupiny pro administraci SQL serveru. |
Table | Database.Tables | Tabulka v databázi. |
StoredProcedure | Database.StoredProcedures | Uložené procedury v databázi |
User | Database.Users | Uživatel databáze (nezaměňovat s Login ). |
View | Database.Views | Pohled v databázi. |
Column | Table.Columns | Sloupec (atribut) tabulky. |
Index | Table.Indexes | Index tabulky. |
Triggers | Table.Triggers | Trigger tabulky. |
Více informací včetně kompletního
objektového modelu najdete v MSDN (zkuste zadat např. klíč SQL server objekt
a v zobrazeném seznamu vyberte SQL-DMO Object Tree).
Vytváření, editace a mazání objektů
Objekty v databázi můžete vytvářet velmi jednoduše. Stačí pouze nadefinovat proměnnou
pro daný objekt, nastavit jí vlastnosti které má mít a přidat do patřičné kolekce pomocí metody
Add . Tzn. chcete-li přidat tabulku, nadefinujete proměnnou typu Table ,
nastavíte vlastnosti a přidáte do kolekce Tables objektu Database .
Každý objekt má samozřejmě různé vlastnosti, ale tím se zde zabývat nebudeme.
V tomto příkladu vytvoříme novou databázi DBPriklad:
Dim sqlDB As New SQLDMO.Database
Dim sqlFile As New SQLDMO.DBFile
Dim sqlLogFile As New SQLDMO.LogFile
sqlDB.Name = "DBPriklad"
sqlFile.Name = "DBPriklad"
sqlFile.PhysicalName = "c:\data\dbpriklad.mdf"
sqlFile.PrimaryFile = True
sqlFile.FileGrowthType = SQLDMOGrowth_MB
sqlFile.FileGrowth = 1
sqlDB.FileGroups("PRIMARY").DBFiles.Add sqlFile
sqlLogFile.Name = "DBPrikladLog"
sqlLogFile.PhysicalName = "c:\data\dbpriklad.ldf"
sqlDB.TransactionLog.LogFiles.Add sqlLogFile
sqlSrv.Databases.Add sqlDB
V dalším příkladu vytvoříme tabulku v databázi DBPriklad, která bude mít dva sloupce.
První je typu int a druhý typu char s délkou 20 znaků.
Dim sqlDB As SQLDMO.Database, sqlTB As New SQLDMO.Table, sqlCol As New SQLDMO.Column
Set sqlDB = sqlSrv.Databases("DBPriklad")
With sqlTB
.Name = "Zamestnanci"
.FileGroup = "PRIMARY"
End With
Set sqlCol = New SQLDMO.Column
With sqlCol
.Name = "ID"
.Datatype = "int"
.Identity = True: .IdentityIncrement = 1: .IdentitySeed = 1
.AllowNulls = False
End With
sqlTB.Columns.Add sqlCol: Set sqlCol = Nothing
Set sqlCol = New SQLDMO.Column
With sqlCol
.Name = "Jmeno"
.Datatype = "char"
.Length = 20
End With
sqlTB.Columns.Add sqlCol: Set sqlCol = Nothing
sqlDB.Tables.Add sqlTB
Stejně tak jednoduché jako přidávání je i rušení objektů. Pro přidávání
jsme použili metodu Add , pro mazání použijeme metodu Remove .
V příkladu si smažeme výše vytvořenou tabulku Zamestnanci.
sqlSrv.Databases("DBPriklad").Tables.Remove ("Zamestnanci")
Editace objektů už není tak jednoznačná, přesto je u některých objektů podobná.
Např. pro změnu tabulky musíte nejdříve zavolat metodu BeginAlter ,
vykonat potřebné změny, např. přidání cizího klíče a nakonec zavolat metodu
DoAlter . Změna uložené procedury, pohledu nebo triggeru je jednodušší. Stačí
zavolat metodu Alter , které zadáte nový Transact-SQL příkaz, definující
daný objekt.
V tomto příkladu si přidáme sloupec typu smalldatetime do tabulky Zamestnanci.
Sloupci nastavíme default hodnotu, která bude rovna datu a času vkládání.
Dim colNew As New SQLDMO.Column, sqlTB As SQLDMO.Table
With colNew
.Name = "created"
.Datatype = "smalldatetime"
.DRIDefault.Text = "getdate()"
End With
Set sqlTB = sqlSrv.Databases("DBPriklad").Tables("Zamestnanci")
sqlTB.BeginAlter
sqlTB.Columns.Add colNew
sqlTB.DoAlter
Chcete-li znát Transact-SQL příkaz, který vytváří daný objekt, zavolejte jeho metodu
Script (tak si můžete také vizuálně ověřit správnost vytvoření objektu).
Tuto metodu můžete použít např. pro databázi, tabulku, pohled, uloženou proceduru atd.
Zavoláte-li tuto metodu pro naši tabulku Zamestnanci,
Dim sqlTB As SQLDMO.Table
Set sqlTB = sqlSrv.Databases("DBPriklad").Tables("Zamestnanci")
Debug.Print sqlTB.Script
získáte tento text:
CREATE TABLE [Zamestnanci] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Jmeno] [char] (20) COLLATE Czech_CI_AS NOT NULL ,
[Created] [smalldatetime] NOT NULL CONSTRAINT [DF_Zamestnanc_created_1__51] DEFAULT (getdate())
) ON [PRIMARY]
GO
Spouštění Transact-SQL příkazů
Velkou výhodou SQL-DMO je možnost spouštění Transact-SQL příkazů. Ve spojení
s metodou Script tak můžete provádět např. zálohu databáze nebo
zobrazovat, editovat a mazat data, aniž byste museli použít ADO (samozřejmě jsou možnosti
oproti ADO omezené, ale na základní úkony to stačí). Metody pro spouštění existují tři.
ExecuteImmediate spustí zadaný příkaz, ale nevrací žádné výsledky,
proto se hodí pro příkazy DDL (Data Definition Language CREATE , ALTER atd.)
a DML (Data Manipulation Language INSERT apod.). Tuto metodu mají objekty SQLServer
a Database .
ExecuteWithResults spustí příkaz a vrátí výsledky do objektu
QueryResults . Tuto metodu mají oba výše uvedené objekty i LinkedServer a RemoteServer .
ExecuteWithResultsAndMessages je stejná jako ExecuteWithResults ,
navíc však vrací do proměnné typu String zprávy SQL serveru.
V příkladu vytvoříme tabulku Zamestnanci pomocí Transact-SQL příkazu, který
jsme získaly metodou Script . Hned potom do ní vložíme
jeden záznam, který si zobrazíme.
Dim sqlDB As SQLDMO.Database, sql As String
Dim qRes As SQLDMO.QueryResults
Set sqlDB = sqlSrv.Databases("DBPriklad")
'vytvoření tabulky
sql = "CREATE TABLE [Zamestnanci] (" & _
"[ID] [int] IDENTITY (1, 1) NOT NULL ," & _
"[Jmeno] [char] (20) COLLATE Czech_CI_AS NOT NULL ," & _
"[Created] [smalldatetime] NOT NULL CONSTRAINT [DF_Zamestnanc_created_1__51] DEFAULT (getdate())" & _
") ON [PRIMARY]"
sqlDB.ExecuteImmediate sql
'vložení jednoho záznamu
sql = "INSERT INTO Zamestnanci (jmeno) VALUES ('martin')"
sqlDB.ExecuteImmediate sql
'přečtení hodnot vloženého záznamu
sql = "SELECT * FROM Zamestnanci"
Set qRes = sqlDB.ExecuteWithResults(sql)
Debug.Print "Počet řádků: " & qRes.Rows
Debug.Print "Počet sloupců: " & qRes.Columns
Debug.Print "ID=" & qRes.GetColumnString(1, 1) & vbCrLf & _
"Jmeno=" & qRes.GetColumnString(1, 2) & vbCrLf & _
"Vytvořeno=" & qRes.GetColumnString(1, 3)
Příklad zobrazí tyto hodnoty (vytvořeno závisí na Vašem datu a času):
ID=1 Jmeno=martin Vytvořeno=2001-10-23 21:16:00.000
|