Windows-Anwendungen mit PowerShell und Windows Presentation Framework (WPF) – Teil 5: Ausführen von SQL-Skripten

Bei der Programmierung von Windows-Anwendungen, egal in welcher Programmiersprache geschrieben, steht man immer wieder vor der Herausforderung, strukturierte Daten sicher und wiederverwendbar zu speichern. Bei kleineren Datenmengen tut es meistens eine Datei im Textformat (CSV, XML). Sobald die Datenmenge aber größer wird und komplexe Auswertungen gefordert werden, stößt man bei der Speicherung und Analyse von Daten in Textdateien schnell an Grenzen. Genau um solche Grenzen zu überwinden, wurden Datenbanken erfunden.
Das Windows Presentation Framework ist, wie der Name vermuten lässt, darauf abgestimmt, Oberflächen schnell und optimal zu gestalten, jedoch stellt es für die Zusammenarbeit mit Datenbanken keine Funktionalität zur Verfügung – ist ja auch ein “Presentation Framework” und kein “Data Access Framework”. Doch gemeinsam mit dem .NET-Framework sind Sie in der Lage, sehr leistungsfähige Anwendung zur Darstellung und Bearbeitung von Datenbanken zu erstellen. Um Ihnen dies unter Verwendung der PowerShell zu zeigen, habe ich diese Artikelserie begonnen. In dieser Artikelserie wurden folgende Teile bereits veröffentlicht bzw. sind geplant:

• Teil 1: Einleitung/Grundlagen
• Teil 2: Anwendungsmenüs und Steuerelemente
• Teil 3: Zugriff auf Steuerelemente mit PowerShell
• Teil 4: Dynamisches Erstellen von Steuerelementen
• Teil 5: Ausführen von SQL-(oder anderen) Skripten (dieser Blogeintrag)
• Teil 6: Zusammenfassung und Ende

Wie bereits im Teil 1 der Artikelserie beschrieben, geht es mir nicht darum, dieses Thema abstrakt zu behandeln, sondern ich habe mir eine konkrete Aufgabenstellung dafür gesucht (siehe Abschnitt “Aufgabenstellung” im ersten Teil). In den vorangegangenen Blog-Artikeln habe ich die Anwendung jeweils unter Verwendung des Windows Presentation Framework erstellt und weiterentwickelt. Nachdem Sie am Ende des vierten Teils dazu in der Lage waren, die Windows Anwendung mit den erforderlichen Steuerelementen zu gestalten und Funktionalität/Interaktivität zu zuweisen, möchte ich Ihnen in diesem Blogartikel zeigen, welche Möglichkeiten Sie haben, von PowerShell auf Datenbanken zuzugreifen. Primäres Ziel ist, wie der Titel bereits verspricht, das Ausführen von SQL-Abfragen und die Darstellung der Ergebnisse in einer WPF-Anwendung. Doch damit dies möglich ist, sollte ich Ihnen erst einmal anhand von einfachen Statements den Zugriff erklären. Da sich diese Blog-Serie als Schwerpunkt das Windows Presentation Framework gesetzt hat, möchte ich das Thema Datenbanken auch aus diesem Blickwinkel betrachten und habe dafür eine kurze Gliederung für diesen Artikel zusammengestellt:

• Zugriff Datenbanken
  • SQLCMD
  • Invoke-Sqlcmd
  • SQL Server Management Objects (SMO)
  • Data Access Layer aus dem .NET Framework
• Tipps zum Schluss
• Hinweis in eigener Sache
• Zusammenfassung

Damit Sie die Beispiele nachvollziehen und selbst ausprobieren können, benötigen Sie somit ein Windows System (Client oder Server; für PowerShell und Datenbanktreiber) und einen SQL Server (beides lässt sich auch auf einem System installieren). Da ich keine ausgefallenen Funktionen verwenden möchte, können Sie prinzipiell für Entwicklung und Test jede beliebige SQL Server-Version/Edition (ab SQL Server 2000) verwenden. Empfehlen würde ich Ihnen jedoch die neuste Version von SQL Server zu verwenden (SQL Server 2014). Dazu bietet Microsoft auch eine kostenlose Testversion (Evaluation Edition), die Sie 180 Tage kostenlos nutzen können, bzw. die ebenfalls kostenlose Express Edition oder LocalDB an. Nach dieser kurzen Vorrede möchte ich mit der Beschreibung des Zugriffs auf den SQL Server beginnen. Sie werden sich nun sicherlich fragen, warum ich mich als Beispiel-Datenbanksystem für den SQL Server entschieden habe. Das liegt nicht daran, dass dies am einfachsten wäre, sondern einfach nur, weil ich mich damit am Besten auskenne. Der Zugriff auf andere Datenbanksysteme (Oracle, mySQL, PostgreSQL, …) oder andere strukturierte Datenformat-Dateien wie z. B. Excel und Access  ist mit der PowerShell über entsprechende Datenbankschnittstellen auch möglich, jedoch würde eine weitere Beschreibung der entsprechenden Schnittstellen, diesen Artikel nur weiter verkomplizieren (und unendlich in die Länge ziehen). Ich beschränke mich hierbei auch bewusst auf das Datenbankmodul von SQL Server. Ein Zugriff auf andere Module von SQL Server wie z. B. Analysis Services ist zwar auch mit PowerShell möglich, jedoch soll dies nicht Inhalt dieses Blogartikels sein.

Zugriff auf Datenbanken

Der Zugriff auf Datenbanken stellt für den Anwendungsentwickler immer eine große Herausforderung dar, da es eine Vielzahl von Schnittstellen und Möglichkeiten dafür gibt. Jede dieser Schnittstellen hat Vor- und Nachteile. Neben den nativen Schnittstellen wie OleDB, SQLClient oder ODBC/JDBC, gibt es außerdem datenbankspezifische Schnittstellen wie SQL Server Management Objects oder auch vorgefertigte Frameworks wie z. B. NHibernate oder Data Access Application Block aus der Microsoft Enterprise Library. Ich möchte mich in diesem Artikel auf die nativen Schnittstellen fokussieren.  Auf die Verwendung der Frameworks möchte ich verzichten, da dies den Artikel weiter verkomplizieren würde (möglicherweise ist das ja Stoff für eine weitere Artikelserie). Zu den Schnittstellen zu Datenbanken gibt es keine prinzipielle Empfehlung, welche besser oder schlechter ist. Jede hat, entsprechend Ihres Anwendungsszenarios, Vor- und Nachteile. Diese möchte ich Ihnen anhand der Beispiele erklären und Sie müssen dann selbst entscheiden, welche Schnittstelle für Ihren Anwendungsfall die bessere Wahl ist. Explizit ausnehmen möchte ich Zugriffe, bei denen sowieso bereits eine grafische Benutzeroberfläche (wie z. B. das SQL Server Management Studio) zum Einsatz kommen. Warum sollte man hier noch mit PowerShell nachbearbeiten wollen?

SQLCMD

Die wohl einfachste Möglichkeit auf SQL Server-Datenbanken zuzugreifen, ist die Verwendung des Kommandozeilen-Tools SQLCMD: Voraussetzung für die Verwendung dieses Tools ist, dass Sie die SQL Server-Clientkomponenten von dem SQL Server-Installationsdatenträger installiert haben. Alternativ finden Sie dieses kostenlose Tool auch auf den Microsoft Download-Seiten. SQLCMD ist ein sehr mächtiges Werkzeug im SQL Server, mit dem Sie beliebige TSQL-Abfragen an den SQL Server senden können. Was leider sehr wenig bekannt ist, das dieses Tool innerhalb von Skripten auch Befehle unterstützt. Diese haben eine eigene Syntax und bestehen aus folgenden Schlüsselworten:

• [!!:]GO[count]
• !! <command>
• :exit(statement)
• :Quit
• :r <filename>
• :setvar <var> <value>
• :connect server[\instance] [-l login_timeout] [-U user [-P password]]
• :on error [ignore|exit]
• :error <filename>|stderr|stdout
• :out <filename>|stderr|stdout

Auch im SQL Server Management Studio ist ein SQLCMD-Mode enthalten, der für jedes Abfragefenster aktiviert werden kann: Hinweis: Der Vorgänger von sqlcmd ist osql. Dieses Tool wurde bereits mit SQL Server 2012 abgekündigt, wird aber aus Kompatibilitätsgründen auch mit der aktuellen Version von SQL Server (2014) noch ausgeliefert. Wenn Sie das Tool osql /? aufrufen, erscheint jedoch der Hinweis: “osql unterstützt nicht alle Funktionen von SQL Server 2014. Verwenden Sie stattdessen ’sqlcmd‘.” Dieses sollten Sie beachten, um auch langfristig Ihre Programme und TSQL-Skripte verwenden zu können. Eine einzelne Abfrage oder ein längeres Skript lassen sich mit sqlcmd sehr einfach ausführen. Dazu ein Beispiel: sqlcmd /S . /Q ‚Select database_id,name from sys.databases‘ /E Wie Sie am Ergebnis sehen, ist die Ausgabe suboptimal für eine weitere Verarbeitung mit PowerShell – Header-Zeilen, keine saubere Trennung der Spalten, Zusammenfassung am Ende. Um dies noch ein wenig zu optimieren, kann man diese Einstellungen durch Parameter von SqlCmd bzw. im TSQL ändern: sqlcmd /S . /Q ‚Set nocount on;Select database_id,name from sys.databases‘ /E /h -1 /s „|“ Noch sind die einzelnen Zeilen (Rows) nur Zeichenketten (Strings) und werden nicht als Spalten (Columns) bzw. Zellen interpretiert. Damit wir dies tun können, müssen wir noch ein wenig tiefer in die PowerShell-Trickkiste greifen und z. B. mit dem Select-Object die Zeilen in Spalten auftrennen:

sqlcmd /S . /Q 'Set nocount on;Select database_id,name from sys.databases' /E /h -1 /s "|" |             
    Select-Object -property @{Name="Database ID"; Expression={$PSitem.Split("|")[0]}},            
        @{Name="Name"; Expression={$PSitem.Split("|")[1]}}

Im Ergebnis erhalten Sie nun zwei Zeichenketten pro Zeile. Nun kommen wir der Möglichkeit einer Weiterverarbeitung innerhalb der PowerShell schon näher, da durch entsprechende CmdLets das Ergebnis so aufbereitet werden kann, dass man es beispielsweise auch als WPF-Anwendung darstellen kann. Die folgende kleine WPF-Anwendung macht genau dies möglich. Zur Vereinfachung und zur besseren Übersicht habe ich den “Abfrage”-Bereich in eine eigene Funktion (Get-SQLResult) ausgelagert.

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = ""            
    )            
    $SQLQuery = "Set nocount on;$Query"            
    $SQLCMD = "sqlcmd /S . /Q '$SQLQuery' /E /h -1 /s '|'"            
    return Invoke-Command([Scriptblock]::Create($SQLCMD)) | 
        Select-Object -property 
            @{Name="Database ID"; Expression={$PSitem.Split("|")[0]}},
            @{Name="Name"; Expression={$PSitem.Split("|")[1]}}            
}            
            
[xml] $xaml = @"
<Window 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Test-Anwendung" Height="200" Width="200">
    <Grid>
        <DataGrid Name="DG1"/>
    </Grid>
</Window>           
Add-Type -Assembly PresentationFramework            
$Form=[Windows.Markup.XamlReader]::Load( (New-Object System.Xml.XmlNodeReader $xaml))            
$dg = $Form.FindName("DG1")            
$dg.ItemsSource = Get-SQLResult -SQLInstance "." -Query "Select database_id,name from sys.databases"            
            
$Form.ShowDialog()

Im Ergebnis sieht dieses kurze Programm wie folgt aus: Das lässt sich schon sehen.

Vor-/Nachteile

Der Befehl SQLCMD ist ein Windows-Kommandozeilentool. Erster Nachteil der dadurch entsteht ist, dass ich aus der PowerShell eine Anwendung aufrufe und die Steuerung damit aus den Händen gebe. Der zweite Nachteil ist, dass das Ergebnis für die Ausgabe nicht weiter aufbereitet ist und das oben beschriebene Verfahren zwar gut funktioniert, wenn ich genau weiß, was ich als Ergebnis erwarte – wie in diesem Beispiel zwei Spalten. Versuche ich aber mit dynamischen Abfragen zu arbeiten, wie das in unser geplanten Beispielanwendung geplant ist, wird dieser Weg nicht funktionieren. Betrachten wir also den nächsten Weg.

Invoke-SqlCmd

In PowerShell wurde von Microsoft ein Modulkonzept implementiert, mit dem Sie in der Lage sind, entsprechend Ihren Anforderungen bestimmte Komponenten (oft anwendungsspezifisch) zu laden. Seit SQL Server 2008 liefert Microsoft ein PowerShell-Modul für den SQL Server mit aus. Das SQL Server-PowerShell Modul ist seit SQL Server 2012 eine vollwertige PowerShell-Konsole. Vor SQL Server 2012 handelte es sich bei dieser Konsole nur um eine sogenannte Minishell. Sie können dies auch sehr einfach überprüfen, indem Sie die Variable $ShellID innerhalb der jeweiligen “normalen” PowerShell und von SQLPS abfragen. Wenn die Variable “Microsoft.PowerShell” zurückgibt, handelt es sich um eine standardmäßige PowerShell. Liefert die Variable jedoch “Microsoft.SQLServer.Management.sqlps” handelt es sich noch um die Minishell aus SQL Server 2008/2008R2. Sollten Sie auf eine ältere Version von SQL Server (2000 und höher) zugreifen wollen, bietet es sich an, trotzdem das PowerShell-Modul für SQL Server 2014 zu verwenden. Dies ist abwärtskompatibel und bietet Ihnen den vollen Funktionsumfang (und viele Fehlerbehebungen). Jedoch sollten Sie dann beachten, dass bei älteren Versionen nur ein begrenzter Satz aus CmdLets dieses Moduls funktionieren wird wie z. B. die CmdLets für Verfügbarkeitsgruppen auf einem SQL Server 2008 R2 nicht funktionieren – diese Funktion gab es dort noch nicht. Um festzustellen, welche Module bereits in eine PowerShell-Umgebung geladen wurden, können Sie folgenden Befehl (CmdLet) ausführen: Get-Module Durch den zusätzlichen Parameter ListAvailable liefert Ihnen dieses CmdLet eine Liste aller bereits installierten Module: Get-Module -ListAvailable Damit Sie dieses SQL Server-PowerShell verwenden können, müssen Sie vorher

1. die erforderlichen Komponenten auf Ihrem Computer (bzw. dort wo Sie die PowerShell starten) installieren und
2. das entsprechende Modul in der PowerShell laden.

Die Installation der SQL Server-PowerShell erfolgt entweder gemeinsam mit den SQL Server-Datenbankmodul, manuell aus den entsprechenden Komponenten des SQL Server Feature Pack oder von dem SQL Server-Installationsmedium. Dazu benötigen Sie folgende Komponenten:

1. SQLSysClrTypes.msi
2. SharedManagementObjects.msi
3. PowerShellTools.msi

Insofern Sie kein SQL Server-Installationsmedium im Zugriff haben, können Sie diese Komponenten entsprechend der SQL Server Version vom Microsoft Download-Portal aus dem SQL Feature Pack unter folgenden Links herunterladen:

SQL Server Version	Downloads
SQL 2008 R2 SP2 Feature Pack	http://www.microsoft.com/en-us/download/details.aspx?id=30440SqlClrTypes_x86.msi oder SqlClrTypes_amd64.msi SharedManagementObjects_x86.msi oder SharedManagementObjects_amd64.msi PowerShellTools_x86.msi oder PowerShellTools_amd64.msi
SQL 2012 SP1 Feature Pack	http://www.microsoft.com/en-us/download/details.aspx?id=35580ENU\x86\SQLSysClrTypes.msi oder ENU\x64\ ENU\x86\SharedManagementObjects.msi oder ENU\x64\ ENU\x86\PowerShellTools.msi oder ENU\x64\
SQL 2014 Feature Pack	http://www.microsoft.com/en-us/download/details.aspx?id=42295 ENU\x86\SQLSysClrTypes.msi oder ENU\x64\ENU\x86\SharedManagementObjects.msi oder ENU\x64\ ENU\x86\PowerShellTools.msi oder ENU\x64

Hinweis: Bitte beachten Sie, dass sich die Downloads nach 32 bzw. 64bit-Komponenten unterscheiden (bei der Auswahl ist der Client entscheidend, auf dem Sie das PowerShell-Programm starten wollen und nicht der Server). Außerdem handelt es sich bei den in der obigen Liste angegebenen Downloads um die englischsprachigen Komponenten. Möchten Sie die deutschsprachigen Komponenten herunterladen, so brauchen Sie in dem Link nur die Zeichenfolge “en-us” durch “de-de” zu ersetzen bzw. auf der Webseite die erforderliche Sprache auszuwählen. Wenn Sie auf die Schaltfläche Download klicken, werden Ihnen die im Feature Pack verfügbaren Komponenten angezeigt und Sie müssen dann die erforderlichen auswählen: Nachdem Sie die Tools von der Webseite heruntergeladen haben, müssen Sie diese in der angegebenen Reihenfolge installieren (entweder mit dem Assistenten oder automatisiert im Hintergrund mit dem Parameter /qn). Für die automatische Installation können Sie folgende Befehlszeilen verwenden: msiexec /i SQLSysClrTypes.msi msiexec /i SharedManagementObjects.msi /qn msiexec /i PowerShellTools.msi /qn   Nachdem Sie die Komponenten installiert haben, müssen Sie das PowerShell-Modul zuerst in einer PowerShell-Umgebung laden. Der einfachste Weg dies zu tun besteht darin, dieses Modul direkt aus dem SQL Server Management Studio heraus zu starten: Doch jedes Mal das SSMS zu starten, nur um einen Zugriff auf die SQL Server-PowerShell zu haben, ist für viele Anwendungsfälle sicherlich zu aufwendig. Deshalb können Sie dieses Tool auch direkt starten: „C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe“ Diese Shell befindet sich standardmäßig in dem angegebenen Verzeichnis und kann direkt gestartet werden, da es sich um eine eigenständige Anwendung handelt. Doch auch dieser Weg macht nur dann Sinn, wenn Sie diese Umgebung auf Ihrem eigenen System starten wollen. Haben Sie ein PowerShell-Skript geschrieben, das auch auf verschiedenen Systemen ausgeführt werden kann, ist es sinnvoller das SQL Server-PowerShell-Modul direkt aus diesem Skript zu laden. Dazu benötigen Sie den folgenden Skriptblock:

#SQL Server 2012 und 2014            
if (-not(Get-Module –name 'SQLPS')) {
    if (Get-Module –ListAvailable -Name 'SQLPS') {
        Push-Location
        Import-Module –Name 'SQLPS' –DisableNameChecking
        Pop-Location
    }
}

Ein paar kurze Erläuterungen zu diesem Skriptblock: Im ersten if-Statement wird überprüft, ob das Modul bereits geladen ist. Ist dies nicht der Fall, wird im zweiten if-Statement nachgesehen, ob dieses Modul überhaupt verfügbar ist. In diesem Beispiel ist der Fall, dass das SQL Server-PowerShell Modul nicht installiert ist, nicht berücksichtigt. Existiert es, wird es mit dem Import-Module CmdLet geladen. Der zusätzliche Parameter DisableNameChecking beim cmdlet Import-Module dient nur dazu, diese verwirrende Warnung beim Laden des Moduls zu unterdrücken: “WARNUNG: Die Namen einiger importierter Befehle auf Modul „sqlps“ enthalten nicht genehmigte Verben, sodass deren Auffindbarkeit erschwert werden kann. Wenn Sie die Befehle mit nicht genehmigten Verben finden möchten, führen Sie den Import-Module-Befehl erneut mit dem Verbose-Parameter aus. Sie können durch Eingeben von „Get-Verb“ eine Liste der genehmigten Verben anzeigen.” Diese Warnung tritt auf, weil sich die Entwickler von Microsoft nicht an das vorgegebene Konzept der Benennung von CmdLets gehalten haben. Die falschen Bezeichnungen kommen durch die CmdLets Encode- und Decode-SQLName. Prinzipiell nicht weiter schlimm, doch meiner persönlichen Meinung nach schon ein wenig nervig. Das seit SQL Server 2014 außer dieser Warnung trotz des Parameters DisableNameChecking noch eine Reihe weiterer nicht weniger verwirrender Warnungen angezeigt werden, scheint die Entwickler von Microsoft jedoch nicht weiter zu stören und sie haben wahrscheinlich auch nicht dazu gelernt: Denn statt einer Warnung in SQL Server 2012 erscheinen nun zwölf Warnungen. Sie sollten sich aber davon nicht abschrecken lassen. Eine richtig schöne Lösung zum Unterdrücken der Warnungen habe ich auch noch nicht gefunden. Es gibt zwar bereits einige Hinweise darauf, was man dagegen tun könnte, doch viele dieser Empfehlungen erfordern Eingriffe in den SQL Server PowerShell-Provider – wovon ich persönlich aber nur abraten kann. Somit bleibt Ihnen wohl nur mit den Warnungen zu leben oder zu einem anderen Lösungsszenario zu wechseln. In SQL Server 2008 und 2008 R2 handelt es sich bei den Komponenten zum Zugriff auf SQL Server um SnapIns (keine Module), die entsprechend anders geladen werden müssen:

#SQL Server 2008 und 2008 R2            
Get-PSSnapin –Registered            
Add-PSSnapin SqlServerCmdletSnapin             
Add-PSSnapin SqlServerProviderSnapin

Das eigentliche PowerShell-Modul für den SQL Server 2012/2014 befindet sich in dem Programmverzeichnis unter C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1 Hinweis: Wir die meisten Skripte, Module und weitere Komponenten der PowerShell handelt es sich auch bei diesem Modul um eine Textdatei, die Sie z. B. mit einem Texteditor (Notepad) ansehen können. Auch diese Komponente lässt sich in einer Standard-PowerShell ebenfalls mit dem CmdLet Import-Module laden: Import-Module „C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1“

Wenn man SQL Server Cmdlets öfter aus der normalen PowerShell aufrufen möchte, macht es Sinn das Modul bereits beim Starten der PowerShell mit zu laden. Da die PowerShell über verschiedene Profile verfügt, kann man das entsprechende Modul einfach in einem solchen Profil z. B. dem Benutzerprofil laden. Der Weg dazu ist sehr einfach „Import-Module SQLPS -DisableNameChecking“ | Out-File $Profile -Append

Zur Steuerung der Verbindung zum SQL Server gibt es noch einige Variablen (auch als Befehlszeilenergänzung bezeichnet), die Sie kennen sollten. Für die meisten Anforderungen ist es nicht erforderlich, diese explizit zu setzen. Wenn Sie diese nicht setzen, werden die im folgenden Beispiel abgebildeten Standardwerte verwendet.

Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

Die Funktionalität dieses SQL Server 2014-PowerShell Moduls (soll heißen, die integrierten CmdLets) haben sich über die verschiedenen Versionen von SQL Server nur wenig geändert (okay, ein paar neue CmdLets für die neuen SQL Server-Funktionen sind hinzugekommen). In der folgenden Übersicht habe ich Ihnen die CmdLets aus dem PowerShell-Modul für SQL Server 2014 zusammengestellt:

Add-SqlAvailabilityDatabase Add-SqlAvailabilityGroupListenerStaticIpAdd-SqlFirewallRule Backup-SqlDatabase Convert-UrnToPath Decode-SqlName Disable-SqlAlwaysOn Enable-SqlAlwaysOn Encode-SqlName Get-SqlCredential Get-SqlDatabase Get-SqlInstance Get-SqlSmartAdmin Invoke-PolicyEvaluation Invoke-Sqlcmd

Join-SqlAvailabilityGroup New-SqlAvailabilityGroupNew-SqlAvailabilityGroupListener New-SqlAvailabilityReplica New-SqlBackupEncryptionOption New-SqlCredential New-SqlHADREndpoint Remove-SqlAvailabilityDatabase Remove-SqlAvailabilityGroup Remove-SqlAvailabilityReplica Remove-SqlCredential Remove-SqlFirewallRule Restore-SqlDatabase Resume-SqlAvailabilityDatabase Set-SqlAuthenticationMode

Set-SqlAvailabilityGroup Set-SqlAvailabilityGroupListenerSet-SqlAvailabilityReplica Set-SqlCredential Set-SqlHADREndpoint Set-SqlNetworkConfiguration Set-SqlSmartAdmin Start-SqlInstance Stop-SqlInstance Suspend-SqlAvailabilityDatabase Switch-SqlAvailabilityGroup Test-SqlAvailabilityGroup Test-SqlAvailabilityReplica Test-SqlDatabaseReplicaState Test-SqlSmartAdmin

Eine ausführliche Beschreibung der CmdLets dieses PowerShell-Moduls ist bei Microsoft nicht zu finden – nur verstreut in den Books Online. Insofern Sie eine vollständige Hilfe zu diesen Befehlen benötigen, sollten Sie folgendes PowerShell-Skript ausführen: get-command -Module sqlps -CommandType cmdlet | ForEach-Object {get-help $PSItem.Name -full} Soweit zur “kurzen” Vorrede! Doch was können Sie nun mit diesem PowerShell-Modul anfangen? Versuchen wir also einfach mal das gleiche Ergebnis zu erreichen, wie in dem vorangegangenen Beispiel. Von den vielen mit Modul SQLPS gelieferten CmdLets und sonstigen Erweiterungen benötigen wir eigentlich nur Invoke-Sqlcmd. Die Syntax dazu sieht wie folgt aus: Invoke-Sqlcmd [[-Query] <String>] [-AbortOnError] [-ConnectionTimeout <Int32>] [-Database <String>] [-DedicatedAdministratorConnection] [-DisableCommands] [-DisableVariables] [-EncryptConnection] [-ErrorLevel <Int32>] [-HostName <String>] [-IgnoreProviderContext] [-IncludeSqlUserErrors] [-InputFile <String>] [-MaxBinaryLength <Int32>] [-MaxCharLength <Int32>] [-NewPassword <String>] [-OutputSqlErrors <Boolean>] [-Password <String>] [-QueryTimeout <Int32>] [-ServerInstance <PSObject>] [-SeverityLevel <Int32>] [-SuppressProviderContextWarning] [-Username <String>] [-Variable <String[]>] [<CommonParameters>] Schaut man sich die Syntax genauer an, wird man feststellen, dass diese teilweise mit der des Kommandozeilentools sqlcmd übereinstimmen. Somit stimmt nicht nur der Name teilweise überein, sondern auch viele Optionen sind in beiden “Tools” enthalten. Dies sieht man beispielsweise auch, wenn man die Hilfe zu Invoke-Sqlcmd aufruft: “…Führt ein Skript aus, das die vom SQL Server-Hilfsprogramm sqlcmd unterstützten Programmiersprachen und Befehle enthält…” Sie sollten jedoch beachten, dass nur ein Teil der Parameter von sqlcmd im Cmdlet Invoke-SqlCmd unterstützt werden. Probieren wir es also gleich mit der Abfrage aus dem vorhergehenden Beispiel aus:

Invoke-Sqlcmd -ServerInstance . -Query "Select database_id,name from sys.databases"

Das Ergebnis sieht nun sehr ähnlich wie in dem vorhergehenden Beispiel aus, als wir mit SQLCMD die Abfrage ausgeführt haben. Dies ist jedoch nur scheinbar so, denn der gravierende Unterschied besteht darin, dass Sie nun keine Zeichenketten zurückerhalten, sondern ein PowerShell-Objekt (System.Data.DataRow), das Sie ohne Zwischenschritte mit anderen CmdLets weiterverarbeiten können. Binde ich dies also nun in die vorher definierte Powershell-Funktion ein, vereinfacht sich einiges:

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = ""            
    )            
    return Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $Query            
}

Doch wenn wir uns nun das Ergebnis ansehen, werden Sie sehen, dass Sie mehr geliefert bekommen haben, als Sie eigentlich erwartet hätten (oft nicht das schlechteste, doch in diesem Fall wahrscheinlich störend). Microsoft ist der Meinung, dass das normal ist, da es sich bei den zusätzlich dargestellten Spalten um Eigenschaften des Objektes handelt. Diese lassen sich ausblenden, indem die nicht benötigten Spalten durch ein Select-Object CmdLet ausgeschlossen (excludiert) werden.

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = ""            
    )            
    return Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $Query |
       Select-Object –Property * 
   -ExcludeProperty RowError,RowState,HasErrors,Table,ItemArray            
}

Nun sieht das Ergebnis genauso aus, wie im vorangegangenem Beispiel. Alternativ können Sie die Spalten auch über die XAML-Definition der Anwendung ausblenden und wenn Sie meine vorhergehenden Artikel fleißig gelesen haben, sollten Sie ja auch wissen, wie das geht. Als kleine Ergänzung möchte ich Ihnen noch zeigen, wie man mit diesem CmdLet auch Abfragen über Parameter steuern kann. Dazu gibt es einen zusätzlichen Parameter “Variable” dieses CmdLet, dem man eine Liste von Parametern übergeben kann. Aus Vereinfachungsgründen verwende ich nur einen Parameter “PageVerifyOption” und übergebe den Wert “Checksum” an diesen Parameter. Die Abfrage müssen Sie auch anpassen, damit der Parameter verwendet werden kann (in dem Beispiel in der Where-Bedingung). Dabei ist abweichend zum normalen TSQL darauf zu achten, dass der Parameter mit $(Parametername) in die Abfrage eingebaut werden muss. Da es sich bei dem $-Zeichen um ein reserviertes Zeichen in der PowerShell handelt (erstes Zeichen einer Variable), muss zusätzlich noch ein Backtick-Zeichen (`) vorangestellt werden. Ich verwende nun wieder das gleiche PowerShell-Skript wie im vorhergehenden Kapitel und habe daran nur einige kleiner Anpassungen vorgenommen (gelb markiert):

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = "",            
        [String[]]$SQLParameter = ""            
    )            
    return Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $Query -Variable $SQLParameter |             
        select –property * -ExcludeProperty RowError, RowState, HasErrors, Table, ItemArray            
}            
            
[xml] $xaml = @"
<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Test-Anwendung" Height="200" Width="200">
    <Grid>
        <DataGrid Name="DG1" />
    </Grid>
</Window>
"@            
Add-Type -Assembly PresentationFramework            
$Form=[Windows.Markup.XamlReader]::Load( (New-Object System.Xml.XmlNodeReader $xaml))            
$dg = $Form.FindName("DG1")            
[String[]]$SQLParameter = @("PageVerifyOption='Checksum'")            
$dg.ItemsSource = Get-SQLResult -SQLInstance "." `
    -Query "Select database_id,name from sys.databases where page_verify_option_desc <> `$(PageVerifyOption)" -SQLParameter $SQLParameter            
$Form.ShowDialog()

Anstatt mit dem Invoke-SqlCmd zu arbeiten, bietet Ihnen das SQL Server-PowerShell-Modul noch eine weiter Möglichkeit zum Abfragen von Daten an, die wohl nur selten verwendet wird, jedoch auch eine valide Möglichkeit ist, die gewünschten Informationen aus unserem Beispiel abzufragen. Um Ihnen dies zu demonstrieren, werde ich den Weg wieder vom Ansatz beschreiben. Erster Schritt ist auch in diesem Fall wieder das Laden des SQL Server-PowerShell-Modul, wie bereits vorher beschrieben. Im nächsten Schritt wechseln Sie mit Set-Location auf das PSDrive für den SQL Server und von dort auf die Default-Instanz des lokalen SQL Server und lassen sich mit dem CmdLet Get-ChildItem alle unter Databases liegende Objekte (Benutzer-Datenbanken) anzeigen. Möchten Sie zusätzlich noch die System-Datenbanken auflisten, müssen Sie an das CmdLet Get-ChildItem noch den Parameter –Force anhängen.

Set-Location "SQLSERVER:\sql\$($Env:Computername)\Default"            
Get-ChildItem –Name databases

Hinweis: Nachdem ich im vorhergehenden Beispiel mit den Original-CmdLets gearbeitet habe, möchte ich Ihnen im folgenden Beispiel zeigen, wie einfach es doch ist mit PowerShell zu arbeiten, auch wenn Sie nur einige grundlegende Befehle des Windows-Kommandoprozessors (CMD) beherrschen. Die CmdLets im vorhergehenden Beispiel habe ich in diesem Beispiel nur durch Ersetzungen (Aliases) ausgetauscht. Das Ergebnis, obwohl das Skript jetzt mehr nach einer Windows-Batch-Datei aussieht, liefert aber exakt das gleiche Ergebnis:

cd "SQLSERVER:\sql\$($Env:Computername)\Default"            
dir databases

Da Alias-Bezeichnungen individuell gesetzt und geändert werden können, habe ich mir angewöhnt, ausschließlich mit den CmdLet-Name und nicht mit Alias-Bezeichnungen zu arbeiten. Die CmdLet-Namen sind zwar länger (mehr Zeichen), doch bei der Verwendung von Alias-Bezeichnungen können Sie nicht immer davon ausgehen, dass diese in der PowerShell-Umgebung verfügbar sind, speziell dann, wenn Ihr Skript auf mehreren Systemen ausgeführt werden soll, auf die Sie nur bedingt Zugriff haben.

Führen Sie nun folgenden Befehl aus:

get-childitem "SQLServer:\\sql\$($Env:COMPUTERNAME)\Default\databases" -Force | select id,name

sieht das Ergebnis eigentlich genauso aus, wie in den anderen vorhergehenden Beispielen: Doch auch hier trügt der Schein. Denn was sie hier erhalten ist nicht das Ergebnis einer Abfrage, sondern eine Liste von Datenbankobjekten mit allen deren Eigenschaften und Methoden: Sollten Sie nur Informationen von Objekten aus einer Instanz wie z. B. Datenbanken, aus einer Datenbank wie z. B. Tabellen oder von Tabellen wie z. B. Indizes haben wollen, ist dieser Weg perfekt. Doch wenn Sie Daten aus Tabellen bekommen wollen, müssen Sie leider noch ein Stück weiterlesen. Im nächsten Schritt greife ich mir einfach eine Datenbank aus der Liste heraus z. B. master und weise diese Datenbank einer Variablen zu. Wenn Sie sich anschließend die Eigenschaften und Methoden dieser Variablen ausgeben lassen (Get-Member), sehen Sie, dass sie vom Objekttyp Microsoft.SqlServer.Management.Smo.Database ist. Eine der Methoden dieses Objekts ist ExecuteWithResults, der Sie als einen Parameter ein SQL Abfrage mitgeben können. Dazu mehr im nächsten Kapitel… Noch ein kleiner Hinweis zu Invoke-SqlCmd: Wenn Sie sich bereits mit dem Set-Location CmdLet mit dem SQL Server verbunden haben, können Sie das CmdLet Invoke-SqlCmd ohne Instanznamen aufrufen. Er nimmt dann automatisch die geöffnete Instanz:

Push-Location -WarningAction SilentlyContinue            
Set-Location "SQLSERVER:\\SQL\$($Env:COMPUTERNAME)\Default"            
Invoke-Sqlcmd -Query "Select database_id,name from sys.databases"            
Pop-Location

Vor-/Nachteile

Das PowerShell-CmdLet Invoke-SqlCmd ist eigentlich eine coole Geschichte, da es Ihnen zum Abfragen von Daten alle notwendigen Funktionen zur Verfügung stellt. Doch wenn Sie längere Zeit damit gearbeitet haben, werden Sie sich irgendwann fragen, warum Sie so ein dickes Modul wie SQLPS mit einer großen Anzahl von CmdLets und sonstigen Komponenten laden müssen, um nur eine kleine Abfrage auszuführen. Irgendwie “Oversized”. Dass dies auch einfacher geht, möchte ich Ihnen in den nächsten Kapiteln zeigen.

SQL Server Management Objects (SMO)

Wenn Sie eine Anwendung entwickeln möchten, die SMO verwendet, sollten Sie beim Installieren vom SQL Server-Installationsmedium das Clienttools SDK auswählen.  Um SMO ohne SQL Server-Installationsmedium zu installieren, können Sie, ähnlich wie im vorherigen Beispiel, die folgenden Komponenten aus dem SQL Server Feature Pack installieren:

1. SQLSysClrTypes.msi
2. SharedManagementObjects.msi

Hinweis: Die SQL Server-PowerShell-Komponente, wie im vorherigen Abschnitt, ist für die Verwendung von SMO nicht erforderlich. Nachdem Sie die erforderlichen Komponenten installiert haben, gibt es prinzipiell mehrere Methoden zum Laden von SMO

1. Add-Type -AssemblyName „Microsoft.SqlServer.Smo“Dieser Weg ist der präferierte Weg. Doch möglicherweise schlägt er fehl, wenn Sie verschiedene SQL Server Versionen bzw. Client SDK auf Ihrem System installiert haben (nicht schön, aber manchmal halt nicht zu umgehen). In diesem Fall müssen Sie auf einen der folgenden Wege (2/3) ausweichen.
2. Add-Type –Path „C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll“  Bei diesem Weg sollten Sie auf die Verwendung der richtigen Version achten. Standardmäßig werden die SMO-Assemblies für SQL Server 2014 im Verzeichnis C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\ installiert. Dies ist jedoch nur dann verfügbar, wenn Sie auch die Client SDK-Komponenten installiert haben.
3. Add-Type –Path „C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll“Als erstes müssen Sie dafür rausfinden, welche Versionen sind überhaupt installiert get-childitem -path C:\Windows\assembly\GAC_MSIL\Microsoft.SQLServer.smo und können dann im zweiten Schritt die für Ihre Aufgabe erforderliche Version auswählen und in dem Path-Parameter eintragen.
4. [System.Reflection.Assembly]::LoadWithPartialName („Microsoft.SqlServer.Smo“); Diese Variante ist von Microsoft abgekündigt (LoadWithPartialName) und sollte nicht mehr verwendet werden. Ist jedoch für alte PowerShell-Versionen (< 2.0) die einzige Möglichkeit, um ein Assembly einzubinden. Im Internet scheint dies jedoch immer noch die bevorzugte Variante zu sein. Trotzdem würde ich empfehlen, davon Abstand zu nehmen.
5. Sollte alle vorhergehenden Methoden nicht funktioniert haben, sollten Sie dann jetzt besser doch die SQL Server-PowerShell-Komponenten installieren. Nachdem Sie die geladen haben (wie im vorherigen Abschnitt beschrieben), stehen Ihnen automatisch alle erforderlichen Assemblies zur Verfügung.
6. …vielleicht fällt Ihnen ja noch etwas ein –> dann lassen Sie es mich einfach wissen.

Aus Vereinfachungsgründen werde ich im Folgenden mit der Variante 1 arbeiten. Neben dem vorher genannten Assembly, gibt es jedoch noch eine ganze Reihe weiterer Assemblies, die für die eine oder andere Aktion erforderlich sind. Auch jedes dieser Assemblies können Sie entsprechend der vorgeschlagenen Varianten (hier am Beispiel der Variante 1) laden. Das folgende Beispiel zeigt Ihnen, wie Sie alle Assemblies auf einmal laden können:

get-childitem -Path "C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\microsoft.sqlserver.*.dll" |             
    ForEach-Object {add-type -Path $PSItem.Fullname }

Normalerweise sollten Sie aber nur solche Assemblies in die PowerShell-Umgebung laden, die Sie auch wirklich benötigen. Eine Beschreibung der einzelnen Assemblies spare ich mir, da Sie jeweils umfangreiche Informationen dazu in der Online Bibliothek (http://msdn.microsoft.com) finden. Eine Alternative für die Beschreibung der Interfaces ist unter “C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\de” zu finden. In dem Verzeichnis existiert pro Assembly eine XML-Datei, mit der Beschreibung des Assembly. Wer daraus eine gut lesbare Webseite erstellen möchte, kann dafür das Beispiel aus Formatting .NET Assembly Summary Documentation verwenden. Zum Abfragen von Daten aus einer Datenbank steht Ihnen nun jedoch nicht mehr das leicht zu verwendende CmdLet Invoke-SQLCmd zur Verfügung, sondern Sie müssen nun entsprechend Ihrer Aufgabenstellung die Daten direkt mit den Klassen dieser Assemblies abfragen, die ich Ihnen im folgenden Schritt für Schritt beschreiben werde. Zum Aufbauen einer Verbindung zum SQL Server müssen Sie eine neue ServerConnection erstellen. Dazu ist es erforderlich eine neue Instanz des Objektes Server zu instanzieren und als Parameter den Servernamen zu übergeben. Arbeiten Sie mit einer anderen als der Default-Instanz müssen Sie zusätzlich noch den Instanznamen an den Servernamen anhängen (getrennt durch Backslash).

Add-Type –AssemblyName [Microsoft.SQLServer.SMO]
$servername = "Testserver\Instanz1"            
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($Servername)

Die Verbindung wird sofort geöffnet und der Variable $server zugewiesen. In diesem Fall wird die Windows-Authentifizierung verwendet. Nach Deinstanzierung (Freigabe) der Variable $server wird die Verbindung wieder geschlossen.

$server = $null 
# oder
Remove-Variable $server

Für den Fall, dass Sie SQL Server-Authentifizierung verwenden müssen z. B. beim Zugriff auf SQL Azure, können Sie zusätzlich im ConnectionContext der Verbindung noch Benutzername und Kennwort übergeben:

$SQLUser = "TestUser"            
[Security.SecureString]$SQLUserPwd = ConvertTo-SecureString -AsPlainText "P@ssw0rd" -Force            
if ($SQLUser) {
    $server.ConnectionContext.LoginSecure=$false
    $server.ConnectionContext.set_Login($SQLUser)
    $server.ConnectionContext.set_SecurePassword($SQLUserPwd)
}            
$server = $null

Das .NET-Framework bietet Ihnen noch ein zweites Assembly ConnectionInfo zum Zugriff auf SQL Server. Zum Öffnen der Verbindung steht Ihnen dort das ServerConnection-Objekt zur Verfügung: Add-Type –Path „C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll“ $ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SQLInstance) $Reader = $ServerConnection.ExecuteReader($Query) return $Reader Doch nun zurück zum eigentlichen Thema: Abfragen von Daten aus einer Datenbank. Zur Vereinfachung verwende ich wieder das gleiche Beispiel (Abfrage) wie vorher:

Add-Type -AssemblyName "Microsoft.SQLServer.SMO"            
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($($env:COMPUTERNAME))            
$r = $server.ConnectionContext.ExecuteWithResults('Select database_id,name from sys.databases')            
$r.Tables.Item(0) | Select-Object -property * -excludeproperty RowError,RowState,Table,ItemArray,HasErrors

Das Ergebnis dieser Abfrage sieht nun genauso aus, wie das Ergebnis der Abfrage mit Invoke-Sqlcmd (was ja auch nicht weiter verwunderlich ist). Da uns jedoch das CmdLet Invoke-Sqlcmd einige Funktionen wie z. B. den Verbindungsaufbau und kleinere Aufbereitungen am Ergebnis bereits liefert, müssen wir dies bei Verwendung von SMO selbst implementieren. Dadurch haben Sie zwar ein wenig mehr Aufwand, jedoch bietet Ihnen dieses Vorgehen auch mehr Möglichkeiten. Einige davon möchte ich Ihnen im Folgenden zeigen. Doch zuerst möchte ich zu unserem Ausgangsbeispiel und der Einbindung dieses Skriptblocks in unsere WPF-Anwendung demonstrieren:

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = "",            
        [String[]]$SQLParameter = ""            
    )            
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLInstance)            
    foreach ($p in $SQLParameter) {            
        $pName = ($p.Split("="))[0]            
        $pValue = ($p.Split("="))[1]            
        $Query = $Query.Replace($pName,$pValue)            
    }            
    $r = $server.ConnectionContext.ExecuteWithResults($Query)            
    $server = $null            
    return $r.Tables[0].DefaultView            
}            
[xml] $xaml = @"
<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Test-Anwendung" Height="200" Width="200">
    <Grid>
        <DataGrid Name="DG1" />
    </Grid>
</Window>
"@    
Add-Type -Assembly PresentationFramework            
Add-Type -path "C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"             
            
$Form=[Windows.Markup.XamlReader]::Load( (New-Object System.Xml.XmlNodeReader $xaml))            
$dg = $Form.FindName("DG1")            
[String[]]$SQLParameter = @("@PageVerifyOption='Checksum'")            
$dg.ItemsSource = @(Get-SQLResult -SQLInstance "." `
    -Query "Select database_id,name from sys.databases where page_verify_option_desc <> @PageVerifyOption" -SQLParameter $SQLParameter)            
$Form.ShowDialog()

Herausforderungen gab es bei diesem Beispiel (im Skript gelb markiert) mehrere:

1. Parameterauflösung Die Methode ExecuteWithResults unterstützt keine Parameter. Deshalb haben Sie entweder die Möglichkeit die Abfrage, bevor Sie an die Funktion übergeben wird, zu manipulieren bzw. das Ergebnis der Abfrage nachzubearbeiten (z. B. mit dem Where-Object cmdlet). Die Manipulation außerhalb der Funktion ist sicherlich einfacher, doch nachdem ich im vorhergehenden Abschnitt auch nach diesem Verfahren gearbeitet habe, möchte ich das auch weiterhin beibehalten.
2. Zuordnung des Ergebnisses zum Grid Bisher war es recht einfach die zurückgegebenen Werte der Abfrage an das Grid zu übergeben, da es meistens Aufzählungen waren. Doch nun haben wir ein komplexes Objekt  als Rückgabewert, das wir erst in das für die ItemsSource-Eigenschaft des Grid-Controls aufbereiten müssen. Dazu müssen wir zuerst eine bestimmte Tabelle aus dem Resultset auswählen und anschließend eine View (z. B. die DefaultView) verwenden. Im letzten Schritt müssen wir auch das Ergebnis wieder in eine Aufzählung umwandeln.

Die Methode ExecuteWithResults dient normalerweise zum Ausführen eines einzelnen Statements. Versuchen Sie aber mehrere TSQL-Befehle z. B. aus einer Skript-Datei zusammen mit der Methode auszuführen, erhalten Sie folgenden Fehler. Ausnahme beim Aufrufen von „ExecuteWithResults“ mit 1 Argument(en): „Fehler bei Mit Ergebnissen ausführen für Datenbank <dbname>.“ Nach einiges ausprobieren habe ich festgestellt, dass das “GO” in diesem Skript die Ausführung verhindert. Deshalb habe ich dies einfach durch die Methode Replace aus dem Skript entfernt – und schon funktioniert es.

$SQLScript = @"
    Select @@Version; 
    GO; 
    Select @@Version; 
    GO; 
    Select @@Version;
    GO;
"@             
[Microsoft.Sqlserver.management.smo.server]$s = New-Object -TypeName Microsoft.SQLServer.Management.SMO.Server -ArgumentList @($Env:Computername)            
$result = $s.databases.Item("TempDB").ExecuteWithResults($SQLScript.Replace("GO;",""))            
$result.Tables

Um Ihnen zu zeigen wie mächtig das SMO ist, habe ich Ihnen noch ein anderes Beispiel vorbereitet. Daran möchte ich Ihnen demonstrieren, wofür Management Objekte auch gedacht sind – administrative Aufgaben in der Datenbank auszuführen. Konkret heißt es in diesem Fall eine neue Datenbank anlegen, diese als Skript ausgeben und zum Schluss wieder löschen:

$db= new-object Microsoft.SqlServer.Management.Smo.Database            
$db.Name = "Sylvio2"            
$db.Parent = new-Object Microsoft.SqlServer.Management.Smo.Server $($Env:Computername)            
$db.Create($false)            
$db.Initialize()            
$db.Script()            
$db.Drop()

Okay, das ist jetzt wirklich nicht dass sinnvollste Skript, doch es soll Ihnen ja auch nur an einem weiteren Beispiel die Verwendung von SMO zeigen.

Vor-/Nachteile

Wie bereits beschrieben ist SMO eine Möglichkeit um Daten aus einer Datenbank abzufragen. Sie haben damit mehr Möglichkeiten als mit den vorangegangenen Zugriffsvarianten auf SQL Server-Datenbanken. Jedoch ist dieser Weg eigentlich eher als administrativer Zugang zur Verwaltung und Administration einer SQL Server-Instanz gedacht, als zum Abfragen von Daten. Leider wird im Internet oft dieser Weg beschrieben – auch für Aktionen, die damit eigentlich nicht sinnvoll sind.

Data Provider

Nun wollen wir noch eine Ebene tiefer in den Windows-API-Sumpf hinabsteigen (keine Angst) und betrachten uns das .NET-Framework genauer. Im .NET-Framework gibt es den Namespace System.Data, der die ADO.NET-Architektur und –Datenanbieter enthält. ADO.NET, ursprünglich aus den ActiveX Data Objects (ADO) hervorgegangen (hat jedoch nichts mehr mit ActiveX zu tun) und wurde als managed provider auf Basis der .NET common language runtime (CLR) implementiert. In diesem Namespace sind (unter anderem) die untergeordnete Namespaces für den Zugriff auf verschiedene Quellen enthalten. Dazu gehören:

• .NET Framework-Datenanbieter für SQL Server (System.Data.SqlClient)SQL Server Native Client ist eine eigenständige Datenzugriffs-API, die sowohl für OLE DB als auch für ODBC verwendet wird und in SQL Server 2005 eingeführt wurde. SQL Server Native Client (SQL Native Client) ist eine DLL (Dynamic Link Library), die den SQL-OLE DB-Anbieter und den SQL-ODBC-Treiber enthält.
• .NET Framework-Datenanbieter für OLE DB (System.Data.OleDb)wurde basierend auf dem Windows Component Object Model (COM) implementiert. Eine ausführliche Dokumentation dieser Schnittstelle finden Sie unter http://msdn.microsoft.com/en-us/library/windows/desktop/ms722784.aspx.
• .NET Framework-Datenanbieter für ODBC (System.Data.Odbc)Bei dieser Schnittstelle handelt es sich um den Standard zum Abfragen von relationalen Datenbanksystemen. Für alle möglichen Datenbanksysteme auf den verschiedensten Betriebssystemen gibt es entsprechende Treiber.
• .NET Framework-Datenanbieter für Oracle (System.Data.OracleClient)Der System.Data.OracleClient-Namespace ist der .NET Framework-Datenanbieter für Oracle. Diese Typen in System.Data.OracleClient werden als veraltet eingestuft und in einer zukünftigen Version von .NET Framework entfernt.

Diese können durch Provider von Fremdanbietern oder eigene Provider ergänzt werden. Zusätzlich befinden sich in untergeordneten Namespaces noch Klassen des ADO.NET Entity Data Model (EDM) und WCF Data Services.

Der Microsoft SQL Server Native Client-Provider (SNAC) wurde von Microsoft mit Veröffentlichen von SQL Server 2012 abgekündigt – wird jedoch noch weitere 7 Jahre unterstützt. Dies betrifft ausschließlich diesen Provider! Die OLEDB-Technologie, SQL Server Native Client für ODBC und auch alle anderen Provider werden weiterhin von Microsoft unterstützt – leider gibt es dazu im Internet sehr viele fehlerhafte und widersprüchliche Aussagen.

Eine Liste der verfügbaren Treiber (ADO.NET, ODBC/JDBC) für die verschiedene Systeme in Abhängigkeit von der Schnittstelle finden Sie unter http://www.databasedrivers.com. Eine Liste der Treiber für OLEDB finden Sie unter http://technet.microsoft.com/en-us/library/ms187072.aspx. Teilweise gibt es für ein Datenbanksystem mehrere Treiber (manche davon kostenpflichtig) mit unterschiedlichem Funktionsumfang. Bei der Auswahl des Treibers sollten Sie deshalb darauf achten, einen auszuwählen (und zu testen), der Ihren Anforderungen am nächsten kommt.

Nachdem einige den JDBC-Treiber verwenden, habe ich für Euch hier noch einen Link, wie man auch diesen von PowerShell aus verwenden kann: http://doanvublog.wordpress.com/2014/11/17/connecting-to-sql-server-with-the-jdbc-driver/

Um festzustellen, welchen .NET-Schnittstellen für Datenbanken auf Ihrem Windows-System installiert sind, können Sie folgendes Script verwenden:

[System.Data.Common.DBProviderFactories]::GetFactoryClasses() | ForEach-Object {            
    $output = @{            
        'Name'=$PSItem.Name;             
        'Invariantname'=$PSItem.InvariantName;             
        'Description'=$PSItem.description            
    }            
    New-Object -TypeName PSCustomObject -Property $output            
} | fl

In der folgenden Tabelle habe ich Ihnen aus den verschiedenen Namespaces des System.Data-Namespaces einige Klasse die für den Zugriff auf Datenbanken gegenübergestellt:

Namespace Klasse	OLEDB	SQLClient	ODBC	Beschreibung
Connection	OleDbConnection	SqlConnection	OdbcConnection	Verbindungsaufbau
Command	OleDbCommand	SqlCommand	OdbcCommand	Ausführen von Abfragen
DataReader	OleDbDataReader	SqlDataReader	OdbcDataReader	lesender Zugriff auf die Datenquelle
DataAdapter	OleDbDataAdapter	SqlDataAdapter	OdbcDataAdapter	Brücke zwischen Datenquelle und Dataset
DataSet	OleDbDataSet	SqlDataSet	OdbcDataSet	Container für Daten

Wie Sie sehen können, sind die einzelnen Klassen vom Namenskonzept gleich aufgebaut (<Namespace_kürzel><Klassenname>). Ich habe in der Tabelle nur einige Klassen herausgegriffen, die ich teilweise auch in dem Artikel verwenden werde. Im Folgenden werde ich mich primär auf die Klassen des SQL Server Client-Provider konzentrieren. Die Klassen der anderen Namespaces sind aber ähnlich und lassen sich ebenfalls einfach verwenden. Weitere Beispiele (auch für andere Datenquellen) finden Sie unter http://msdn.microsoft.com/de-de/library/dw70f090(v=vs.110).aspx bzw. auf vielen anderen Seiten im Internet. Doch wofür benötigen Sie jetzt plötzlich so viele verschiedene Klassen, wo in den vorhergehenden Beispielen oft nur ein kleiner Befehl (CmdLet) ausgereicht hat: Das hat damit zu tun, dass “ein kleiner Befehl” aus der PowerShell im Hintergrund auch nichts anderes tut, als die erforderlichen Namenspaces/Klassen zu verwenden. Der Unterschied besteht aber darin, dass das CmdLet oft nur einen kleinen Teil der Möglichkeiten unterstützt, die Sie auf durch die direkte Verwendung der APIs haben. Somit ist es wieder ganz von Ihren Anforderungen abhängig, welches das beste Werkzeug für Ihre Aufgabe ist. Doch nun wollen wir uns ansehen, wie das mit der API genau funktioniert.

Laden des Provider

In den vorangegangenen Beispielen musste immer eine Komponente installiert sein und zusätzlich in die PowerShell-Umgebung geladen werden. Dies ist hier nicht erforderlich, da der entsprechende Namespace “System.Data”, als Teil des .NET-Frameworks, beim Laden der PowerShell automatisch mit geladen wird und Sie ihn sofort verwenden können. Sie können dies überprüfen, indem Sie in der PowerShell ISE unter Verwendung von Intellisense den entsprechenden Namespace auswählen: Das spart Ihnen eine Menge Arbeit und somit können Sie sicher sein, dass die erforderlichen Komponenten auf allen Windows Computer vorhanden sind. Sicherheitshalber können Sie aber noch überprüfen, ob das für Ihre Anwendung erforderliche .NET-Framework auch tatsächlich installiert ist ($PSVersionTable).

Verbindungszeichenfolge

Die Verbindungszeichenfolge ist abhängig von der Art des Providers. Eine Übersicht des Aufbaus der Verbindungszeichenfolgen für verschiedene Provider finden Sie auf der Webseite http://www.connectionstrings.com/sql-server/. Prinzipiell haben Sie zwei Möglichkeiten die Verbindungszeichenfolge zu definieren:

[String]$ConnectionString="Data Source=.;Initial Catalog=master;Integrated Security=True"            
            
[System.Data.SqlClient.SqlConnectionStringBuilder]$s = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$s["Data Source"] = "."            
$s["Integrated Security"]=$true            
$s["Initial Catalog"] = "master"

Im ersten Beispiel verwende ich eine Zeichenkette, mit der ich die erforderliche Verbindungszeichenfolge als einzelnen Elementen (Aufbau: Property=Value;”) zusammensetze. Im zweiten Beispiel verwende ich die SqlConnectionStringBuilder-Klasse. Diese Klassen stehen auch für die anderen bereits genannten Namespaces zur Verfügung. Im Ergebnis sind die beiden Verfahren aber gleich, wenn auch unterschiedliche Objekte (String und SqlConnectionStringBuilder) dabei entstehen. Eine Übersicht der Eigenschaften für die Verbindungszeichenfolge des SQLClient-Providers finden Sie unter http://msdn.microsoft.com/de-de/library/System.Data.SqlClient.SqlConnectionStringBuilder_properties.aspx.

Connection

Im nächsten Schritt öffnen Sie eine Verbindung zur Datenbank. Hierzu können Sie entweder die Verbindungszeichenfolge der SqlConnectionStringBuilder-Klasse oder die Zeichenkette verwenden:

$con = New-Object System.Data.SqlClient.SqlConnection            
$con.ConnectionString = $s # oder $ConnectionString            
$con.open()

Bevor Sie eine Verbindung verwenden können, müssen Sie diese mit der Open-Methode noch öffnen. Den Status einer Connection (geöffnet oder geschlossen) lässt sich über die Eigenschaft Status des Connection-Objektes abfragen. $con.State -eq „Open“ Nachdem Sie die Verbindung zur Datenbank geöffnet haben, können Sie von dort z. B. auch Daten abfragen. Jedoch sollten Sie nicht vergessen, die Verbindung zur Datenbank, wieder zu schließen. Doch dazu später…

Aufgrund verschiedener Szenarien hat es sich als sinnvoll erwiesen, eine Verbindung nicht am Beginn einer Anwendung zu öffnen und diese beim Beenden wieder zu schließen, sondern Sie sollten entweder die Verbindung dann öffnen, wenn Sie mit der Datenbank kommunizieren möchten bzw. jeweils den Status der Verbindung abprüfen und, wenn diese geschlossen wurde, diese wieder zu öffnen.

Daten abfragen

Zum Abfragen von Daten aus einer offenen Verbindung benötigen Sie mehrere Objekte:

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand            
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter             
$DataSet = New-Object System.Data.DataSet

Ich habe mir angewöhnt, diese Objekte zusammen zu definieren (wie in diesem Beispiel). Natürlich können Sie sie auch erst definieren, wenn Sie benötigt werden. Damit Sie diese Objekte zum Abfragen von Daten aus einer SQL Server-Datenbank verwenden können, müssen Sie noch verschiedene Eigenschaften zuweisen:

1. Für das SQLCommand-Objekt müssen Sie den CommandType (Standard ist Text), den CommandText (die eigentliche Abfrage) und die Connection festlegen.
2. Für den SQLAdapter geben Sie an, um welche Art von Abfrage es sich handelt (Select, Insert, Update, Delete) und können danach den Adapter anweisen, das Dataset mit Daten zu befüllen.
3. Nachdem Sie das Dataset befüllt haben, und kein Fehler aufgetreten ist, können Sie die Tabellen des Datasets ausgeben bzw. anzeigen lassen. Ich spreche hier absichtlich von Tabellen. Doch dazu später…

$SqlCmd.CommandType = [System.Data.CommandType]::Text            
$SqlCmd.CommandText = "Select database_id,name from sys.databases"   
$SqlCmd.Connection = $con            
            
$SqlAdapter.SelectCommand = $SqlCmd             
$SqlAdapter.Fill($DataSet)             
$DataSet.Tables[0]

Wie Sie sich sicherlich denken können, sieht das Ergebnis, dieses bisher in diesem Abschnitt behandelten Programmschnipsel, genauso aus, wie in allen vorhergehenden Kapiteln: Als Ergebnis erhalten Sie nun jedoch ein Dataset-Objekt, das Sie aus Sicht der PowerShell weitestgehend genauso verarbeiten können, wie die Ergebnisse in den vorhergehenden Abschnitten. Wenn es darum geht Datensätze tiefergehend zu verarbeiten, bietet es sich an den SqlDataReader zu verwenden. Mit diesem Objekt holen Sie sich nicht alle Daten der Abfrage mit einmal, sondern jeweils nur einen einzelnen Datensatz. Der Vorteil dieser Lösung besteht darin, dass sie sehr schnell durch das Ergebnisset navigieren und Berechnungen/Transformationen mit den Daten anstellen können. Der Nachteil besteht jedoch darin, dass bei einem Abbruch der Verbindung zum SQL Server während des Lesevorgangs, das Lesen des nächsten Datensatzes auf einen Fehler läuft und ebenfalls abbricht.

[System.Data.SqlClient.SqlCommand]$cmd = $con.CreateCommand()            
$cmd.CommandType = "text"            
$cmd.CommandText = "Select database_id,name from sys.databases"            
[System.Data.SqlClient.SqlDataReader]$dr= $cmd.ExecuteReader()            
while ($dr.Read()) {            
    New-Object -TypeName PSObject -Property @{            
            ID = $dr.Item("database_id");            
            DatabaseName = $dr.Item("Name")            
        }            
}

Neben den in diesem Abschnitt beschriebenen Eigenschaften und Methoden der verwendeten Objekte. Gibt es noch weitere Eigenschaften und Methode und auch noch weitere Objekttypen für die Arbeit mit diesen Provider. Eine vollständige Übersicht aller Objekte, EIgenschaften und Methoden finden Sie unter http://msdn.microsoft.com/de-de/library/System.Data.SqlClient.aspx

Stored Procedure abfragen

Der Unterschied, ob Sie Daten über ein SQL-Statement oder durch eine Stored Procedure abfragen, ist sehr gering. Das einzige, dass Sie ändern müssen, ist der CommandType ([System.Data.CommandType]::StoredProcedure) und der CommandText (Name der StoredProcedure). Somit sind es nur zwei Zeilen, die Sie im vorhergehenden Beispiel austauschen müssen:

$SqlCmd.CommandType=[System.Data.CommandType]::StoredProcedure            
$SqlCmd.CommandText = "[sys].[sp_databases]"

Ich habe in dem Beispiel eine System Stored Procedure verwendet, die ohne Parameter auskommt und ein Ergebnis als Ausgabe hat. Doch zum Thema Parameter kommen wir sofort…

Parameter

Die Verwendung von Parametern ist über die API sehr einfach, da Ihnen entsprechende Objekte zur Verfügung stehen. Als Beispiel verwende ich nun wieder die Abfrage, die ich bereits weiter oben in diesem Artikel verwendet habe: Select database_id,name from sys.databases where page_verify_option_desc <> @PageVerifyOption Beachten sollten Sie bei der Abfrage, dass jetzt wieder das @-Zeichen als Kennzeichen für Variablen verwendet wird – so wie auch in TSQL üblich. Versuchen Sie nun diese Abfrage direkt auszuführen, ohne den Parameter zu definieren, erhalten Sie folgende Fehlermeldung: Ausnahme beim Aufrufen von „Fill“ mit 1 Argument(en):  „Die @PageVerifyOption-Skalarvariable muss deklariert werden.” Zur Definition der Parameter haben Sie wiederum verschiedene Möglichkeiten. Da diese meiner Meinung nach Selbsterklärend sind, werde ich auch nicht weiter darauf eingehen:

$p = New-Object "System.Data.SqlClient.SqlParameter"            
$p.ParameterName = "@PageVerifyOption"            
$p.SQLDBType = [System.Data.SQLDBType]::VarChar            
$p.Size = 20            
$p.SQLValue = "Checksum"            
            
$p = New-Object "System.Data.SqlClient.SqlParameter" -ArgumentList @{            
    Parametername = "@PageVerifyOption"            
    SQLDBType = [System.Data.SQLDBType]::VarChar            
    Size = 20            
    SQLValue = "Checksum"            
}

Nachdem Sie den Parameter definiert haben, müssen Sie ihn dem SqlCommand-Objekt zur Parameters-Auflistung hinzufügen: $SqlCmd.Parameters.Add($p) bzw. $SqlCmd.SelectCommand.Parameters.Add($p) Wenn Sie den Parameter direkt dem SelectCommand zuweisen wollen. Ein anderer, etwas kürzerer Weg, der Parameterzuweisung sieht folgendermaßen aus: $SqlCmd.Parameters.AddWithValue(„@PageVerifyOption“,“Checksum“) Ich würde jedoch immer den ersten Weg bevorzugen (auch wenn dieser ein klein wenig länger ist), denn damit können Sie den Parameter exakter definieren, um Fehleingaben zu verhindern.

Worin besteht nun der Unterschied, ob Sie mit einem definierten Parameter oder mit Ersetzen von Werten in der Abfrage arbeiten? Schauen wir uns dazu folgendes am Beispiel unserer Anwendung an. Um den Effekt zu verdeutlichen habe ich noch ein zusätzliches Eingabefeld (Textbox) für den Parameter vorgesehen: In dem ersten Beispiel habe ich ein SQLParameter-Objekt verwendet und das Ergebnis ist das, was ich erwartet habe. Gebe ich etwas Abweichendes ein wie z. B. in dem nächsten Beispiel, interpretiert der SQL Server dies immer nur als Parameter. Im zweiten Beispiel habe ich nun statt einem SQLParameter-Objekt eine Zeichenersetzung in die Abfrage integriert: $Query = $Query.Replace(„@PageVerifyOption“,“`’$PV`'“) Und schon kann jeder, auch ohne viel kriminelle Energie, aus Ihrer Datenbank abfragen und in dem SQL Server-Instanz ändern, was immer er möchte: Dieses Verfahren wird auch als SQL Injection bezeichnet und steht mit auf den ersten Plätzen bei Einbrüchen in Computersysteme. Deshalb besser mit SQLParameter-Objekten als mit Ersetzungen in der Abfragen arbeiten. Hinweis: Den Code zu den beiden oben abgebildeten Beispielen finden Sie am Ende des Artikels.

Verbindung beenden

Nachdem wir nun die entsprechenden Ergebnisse aus der Datenbank gelesen haben, sollten Sie die geöffnete Verbindung auch wieder beenden. Theoretisch kümmert sich die Anwendung auch selbst darum und schließt alle geöffneten Verbindungen beim Beenden der Anwendung bzw. der SQL Server beendet nach einer bestimmten Zeit (Connection Timeout) automatisch die geöffneten Verbindungen. Jedoch können aufgrund verschiedener Umstände gerade dadurch Probleme auftreten. Deshalb empfehle ich nicht benötigte Verbindungen explizit zu schließen. Dafür stellt Ihnen das Connection-Objekt die Close-Methode zur Verfügung. $con.Close() Sicherheitshalber sollten Sie jedoch auch hier zusätzlich die Variable wieder freigeben (entweder $con=$null oder Remove-Variable $con)

Einbindung in WPF

Nachdem ich Ihnen so viel Theorie vermittelt habe, möchte ich Ihnen jedoch auch die praktische Umsetzung nicht vorenthalten.

function Get-SQLResult() {            
    param (            
        [String]$SQLInstance=".",            
        [String]$Query = "",            
        [String]$PV = ""            
    )            
            
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand                        
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter                         
    $DataSet = New-Object System.Data.DataSet            
            
    $p = New-Object System.Data.SqlClient.SqlParameter            
    $p.Parametername = "PageVerifyOption"            
    $p.SqlDbType = [System.Data.SqlDbType]::VarChar            
    $p.Size = 20            
    $p.SQLValue = $PV            
    # $Query = $Query.Replace("@PageVerifyOption","`'$PV`'")            
            
    $SqlCmd.CommandType = [System.Data.CommandType]::Text              
    $SqlCmd.CommandText = $Query            
    $SqlCmd.Connection = $con            
            
    $SqlAdapter.SelectCommand = $SqlCmd            
    $SqlAdapter.SelectCommand.Parameters.Add($p)            
                            
    $records=$SqlAdapter.Fill($DataSet)             
    $ret = $DataSet.Tables[0].DefaultView              
                          
    $dg.ItemsSource = @($ret)            
}            

[xml] $xaml = @"
<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Test-Anwendung" Height="200" Width="200">
    <Grid>
        <Label Content="PageVerifyOption:" />
        <TextBox Name="PageVerifyOption" Text="Checksum" Margin="110,0,0,0" Width="400" HorizontalAlignment="Left"/>
        <DataGrid Name="DG1" Margin="0,20,0,0" />
    </Grid>
</Window>
"@
Add-Type -Assembly PresentationFramework            
            
[String]$ConnectionString="Data Source=.;Initial Catalog=master;Integrated Security=True"              
$con = New-Object System.Data.SqlClient.SqlConnection                        
$con.ConnectionString = $ConnectionString             
$con.open()            
            
$Form=[Windows.Markup.XamlReader]::Load($(New-Object System.Xml.XmlNodeReader $xaml))            
$dg = $Form.FindName("DG1")            
$PageVerifyOption=$Form.FindName("PageVerifyOption")            
$PageVerifyOption.Add_KeyDown({            
    If ([System.Windows.Input.Keyboard]::IsKeyDown([System.Windows.Input.Key]::Return)) {            
        Get-SQLResult -SQLInstance "." -PV $($PageVerifyOption.Text) -Query 'Select database_id,name from sys.databases where page_verify_option_desc <> @PageVerifyOption'            
    }             
})            
            
$Form.ShowDialog()            
            
$con.Close()            
$con=$null            
            
$Form = $null

Vor-/Nachteile

Die Verwendung der API bietet Ihnen sehr viele Vorteile, da Sie damit sehr flexibel mit der Datenbank interagieren können und dieser Zugriff für die meisten Anwendungsfälle deutlich schneller ist. Es stehen Ihnen sehr mächtige Namespaces des .NET-Framework zur Verfügung, die bereits beim Start einer PowerShell-Umgebung geladen werden. Diese Vorteile erkaufen Sie sich aber durch einen größeren Aufwand bei der Programmierung. Somit gilt auch hier: Überlegen Sie sich genau, was Sie machen wollen (Definition der Aufgabe) und legen danach erst fest, welches Werkzeug Sie verwenden wollen. Theoretisch ist es zwar möglich, das Werkzeug während der Programmierung zu ändern. Doch entsteht Ihnen dadurch zusätzlicher Aufwand (Zeit und somit Kosten), der bei einer ordentlichen Planung zu vermeiden gewesen wäre.

Tipps zum Ende des Artikels

Wie auch bei den vorangegangenen Artikeln dieser Serie möchte ich Ihnen auch heute wieder einige Tipps mit auf den Weg geben, die ich bei der Recherche gefunden und als interessant befunden habe.

Seitenweise arbeiten

In unseren bisherigen Beispielen habe ich ein DataGrid zur Darstellung der Ergebnisse der Abfrage verwendet. Dieses Steuerelement hat den Vorteil (oder auch Nachteil), dass Sie alle Daten darin laden und sich dann mit einem Scrollbar durch das Dataset bewegen können. Bei sehr großen Datenmenge oder wenn Sie sich selbst um die Ausgabe der Ergebnisse kümmern wollen (z. B. in eine HTML-Tabelle), stoßen Sie jedoch schnell an Grenzen. In einem solchen Fall kann es sinnvoller sein, die Daten seitenweise zu verarbeiten. Eine solche Abfrage auf Basis der SalesOrderDetails-Tabelle aus der AdventureWorks-Datenbank habe ich zur Demonstration verwendet. Prinzipiell sollte es aber auch mit jeder anderen Tabelle/Abfrage funktionieren. Zur Aufteilung in verschiedene Seiten verwende ich die Rangfolgefunktion (Ranking) NTile (http://msdn.microsoft.com/de-de/library/ms175126.aspx).

Declare @Page int = 4
Declare @NumRows int = 20

Declare @Pages int
Select @Pages = Floor(count(*)/(@NumRows-1)) from [AdventureWorks2014].[Sales].[SalesOrderDetail]

Select * from (
SELECT 
    NTILE(@Pages) OVER (ORDER BY [SalesOrderDetailID] asc) AS Tile, *
FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]
) T1
Where Tile = @Page

Sinnvollerweise sollten Sie die Funktion in eine StoredProcedure packen und die beiden Variablen als Parameter definieren.

PowerShell-Programmierregeln

In den vorangegangenen Artikeln dieser Serie habe ich Ab und Zu darauf hingewiesen, dass man sich auch bei der Programmierung mit PowerShell an bestimmte Regeln (coding rules) halten sollte – genauso wie bei der Programmierung mit anderen Programmiersprachen. Da es sich bei PowerShell um eine vergleichsweise junge Sprache handelt, sind viele Regeln und Best Practices im Umgang mit dieser Sprache noch nicht bei den Entwicklern angekommen. Deshalb habe ich für Euch hier eine kurze Zusammenstellung von Links zu solchen Regeln:

• http://blogs.technet.com/b/heyscriptingguy/archive/2014/05/17/weekend-scripter-best-practices-for-powershell-scripting-in-shared-environment.aspx
• https://github.com/darkoperator/PSStyleGuide/blob/master/English.md
• http://msdn.microsoft.com/en-us/library/dd878238(v=vs.85).aspx

Es geht mir nicht um einen Anspruch auf Vollständigkeit und jeder sollte für sich selbst entscheiden, welche Regeln für er beachten möchte und welche nicht. Doch sobald Ihr in einem Team mit anderen Programmierern zusammenarbeitet oder Ihr die Ergebnisse Eurer Arbeit mit jemanden teilen wollt, wie z. B. im Internet, möchte ich Euch bitten, wenigstens ein paar grundlegende Regeln einzuhalten. Damit wird es für andere leichter zu verstehen, was das Skript soll und bietet Ihnen die Möglichkeit auch eigene Erweiterungen mit einfließen zu lassen. Leider gibt es nicht allzu viele technische Lösungen zur Überprüfung der Code-Qualität von PowerShell-Skripten. Ich persönlich verwende in der PowerShell ISE die Erweiterung Skript-Analyzer der als Download unter http://www.microsoft.com/de-de/download/details.aspx?id=42525 zur Verfügung steht: Leider lassen sich die fünf bestehenden Regeln in diesem Tool bisher nicht ändern oder durch eigene Regeln ergänzen. Doch vielleicht ist das ja mit einer der nächsten Versionen möglich. Weiterhin gibt es noch das Tool SkriptCop, mit dem einige Regeln der Programmierung überprüft werden können: http://scriptcop.start-automating.com/Get-ScriptCopRule/.

XAML von PS-Script trennen

In den vorangegangenen Beispielen dieses Artikels habe ich den XAML-Code immer in das PowerShell-Programm integriert. Dies ist ungünstig z. B. wenn Sie den XAML-Code in Visual Studio bearbeiten wollen. Einfacher wäre es den XAML-Block aus dem PowerShell-Skript zu extrahieren und in einer eigenen Datei z. B. als Teil einer Visual Studio Solution abzulegen. In diesem Fall ist es jedoch erforderlich, dass Sie den XAML-Block beim Ausführen des PowerShell-Skripts wieder laden. Dafür reicht folgende Zeile:

$Form=[Windows.Markup.XamlReader]::Load([IO.File]::OpenText('c:\scripts\window.xaml').basestream)

Hinweis in eigener Sache

Ich habe dieser Blogserie bereits viele größere und kleinere Code-Beispiele beigefügt. Auch wenn ich den Code ausführlich erkläre und hinreichend auf meinem PC getestet habe, kann ich nicht sicherstellen, dass dieser auch in Ihrer Umgebung funktionieren wird. Sollten Sie Probleme damit haben, dürfen Sie sich jederzeit gerne bei mir beschweren und ich werde versuchen, gemeinsam mit Ihnen eine Lösung zu finden. Weiterhin habe ich den Code auf das Minimum begrenzt, um Ihnen die Verwendung anschaulich zu beschreiben. Dies bedeutet jedoch auch, dass ich mit Themen wie Fehlerbehandlung, Inline-Dokumentation, Unit-Tests u. ä. sehr sparsam umgegangen bin bzw. dies gänzlich vernachlässigt habe. Sollte Sie jedoch PowerShell-Skripte für einen breiteren Anwenderkreis erstellen wollen, würde ich Ihnen empfehlen alle Regeln und Vorgaben der Programmierung auch bei der Erstellung von PowerShell-Skripten zu beachten. Meine Erfahrung hat gezeigt, dass aus einem Einzeiler in der PowerShell, schnell eine komplexe Anwendung werden kann.

Zusammenfassung

Ich hoffe, ich konnte Ihnen auch mit diesem Artikel wieder meine Begeisterung für dieses Thema vermitteln und Ihnen anhand von leicht nachvollziehbaren Beispielen zeigen, wie Sie auch selbst eigene Anwendungen mit PowerShell+WPF+SQL entwickeln können. Auch wenn nicht alles so einfach ist, wie es auf dem ersten Blick scheinen mag, und auch ich bereits über viele kleine Hürden gestolpert und in Stolperfallen hinein getappt bin (vielen Dank Microsoft), so bin ich immer wieder sehr zufrieden mit meiner Arbeit, wenn es meine Kunden auch sind. Vielleicht haben Sie sich schon gewundert, warum zwischen den letzten Teil dieser Serie und diesem neuen Artikel so viel Zeit vergangen ist. Doch das Thema war doch etwas komplexer als ich dachte und da ich Ihnen die Informationen mit meinem Anspruch auf Verständlichkeit und Vollständigkeit präsentieren wollte, habe ich mir etwas mehr Zeit gelassen. Ich hoffe, Sie sind zufrieden mit dem Ergebnis und können es für Ihre tägliche Arbeit verwenden.

Über sylvioh

Die IT-Welt wird immer komplexer und zwischen den einzelnen Komponenten gibt es immer mehr Abhängigkeiten. Nachdem ich durch meine tägliche Arbeit immer wieder vor der Herausforderung stehe, komplexe Probleme zu lösen, möchte ich diese Seite dafür verwenden, Euch den einen oder anderen Tipp zu geben, wenn Ihr vor ähnlichen Aufgabenstellungen steht.

‹ Merkwürdige Fehlermeldungen

Power BI-Designer ›

Veröffentlicht in PowerShell, SQL Server, WPF