Zugriff auf relationale Datenbanken in PowerShell via ADO.NET

Mit ADO.NET bietet das .NET-Framework eine Sammlung von Klassen für den Zugriff auf relationale Datenbanken an. Vorhandene Datenprovider wie z. B. OleDB oder Odbc ermöglichen dabei die Arbeit mit verschiedensten relationalen Datenbanken - von lokalen Microsoft Access Datenbanken und Excel-Spreadsheets über Microsoft SQL Server bis hin zu MySQL und Oracle Datenbanken. Da PowerShell den Zugriff auf .NET-Klassen ermöglicht, kann ADO.NET auch über PowerShell angesprochen werden.

In diesem Blogbeitrag stelle ich das von mir entwickelte PowerShell Modul PowerADO.NET [1] resp. das darin enthaltene Cmdlet Invoke-SqlQuery vor, welches das Ausführen von SQL Queries gegen von ADO.NET unterstützte Datenbanken ermöglicht und somit eine rudimentäre, fragmentarische PowerShell Schnittstelle für den Zugriff auf relationale Datenbanken über ADO.NET darstellen soll.

Konfiguration

Für den Verbindungsaufbau und das anschließende Ausführen der SQL Queries werden zwei Informationen benötigt, die dem Cmdlet über Parameter mitgeteilt werden können. Dabei handelt es sich zum einen um den entsprechenden Datenprovider und zum anderen um die Verbindungsinformationen der Datenbank. Aktuell werden die folgenden standardmäßig in ADO.NET angebotenen Datenprovider aus dem System.Data Namespace unterstützt:

  • SqlClient

  • OleDb

  • Odbc

  • OracleClient

  • EntityClient

  • SqlCeClient

Die Verbindungsinformationen werden in Form eines typischen ConnectionStrings übergeben. Eine Referenz verschiedener ConnectionStrings für verschiedene Datenbanktechnologien kann unter [2] gefunden werden.

Queries

Die SQL Queries können dem Cmdlet über die Pipeline als Strings übergeben werden. Diese werden dann sequentiell verarbeitet. Ein String kann wiederum mehrere Queries enthalten, in dem diese durch ein Semikolon separiert angegeben werden. Handelt es sich um ein SELECT-Query, werden die angeforderten Daten aus der Datenbank in Form von PowerShell-Objekten zurückgegeben, was eine einfache Weiterverarbeitung ermöglicht. Alle anderen Anfragen werden ohne Rückgabewert ausgeführt. Mithilfe des -Verbose Parameters kann jedoch ausgegeben werden, wie viele Reihen von dem Query betroffen waren.

Beispiele

Im Folgenden wird beispielhaft eine lokale Microsoft Access 2013 Datenbank Database.accdb mit einer Tabelle Customers und den Spalten Id, FirstName, LastName und Age verwendet, die wie in Bild 1 zu sehen befüllt sind. Für den Zugriff auf diese ergibt sich der ConnectionString „Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb;“  und der Datenprovider OleDb.

Bild 1: Beispieldatenbank

Bild 1: Beispieldatenbank

Beispiel 1 – Einzelabfrage

Ausgabe aller Reihen und Spalten aus der Datenbank (Bild 2).

'SELECT * FROM Customers' | Invoke-SqlQuery -Provider OleDb -ConnectionString "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb;"
Bild 2: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 1

Bild 2: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 1

Beispiel 2 – Mehrfachabfrage

Einfügen einer neuen Reihe mit anschließender Ausgabe aller Reihen und Spalten der Datenbank (Bild 3).

"INSERT INTO Customers VALUES (3, 'Jeff', 'Doe', 16)", 'SELECT * FROM Customers ORDER BY ID' | Invoke-SqlQuery -Provider OleDb -ConnectionString "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb;" -Verbose
Bild 3: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 2

Bild 3: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 2

Beispiel 3 – Mehrfachabfrage (Alternative Syntax)

Einfügen einer neuen Reihe mit anschließender Ausgabe aller Reihen und Spalten der Datenbank (Bild 4).

Die Queries werden hier alternativ innerhalb eines Strings durch ein Semikolon separiert übergeben, statt des vollen Namens des Cmdlets wird der eingebaute Alias query benutzt und die übergebenen Parameter anhand ihrer Positionen ermittelt.

"INSERT INTO Customers VALUES (4, 'Jenny', 'Doe', 8); SELECT * FROM Customers ORDER BY ID" | query OleDb "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb;" -Verbose
Bild 4: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 3

Bild 4: Ausgabe der PowerShell nach Absetzen des Befehls aus Beispiel 3

Download

Das Modul mit dem enthaltenen Cmdlet wird zum Download in der PowerShell Gallery [1] bereitgestellt, kann aber auch unter folgenden Links entweder als .txt oder .psm1 Datei direkt heruntergeladen werden:

PowerADO.NET.txt
PowerADO.NET.psm1

[1] https://www.powershellgallery.com/packages/PowerADO.NET/1.0.0.0

[2] https://www.connectionstrings.com/

ESCde Windows Server TeamPowerShell, ADO.NET, SQL