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.

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