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