0 Überblick

1 DatenProvider
    Beispiel 1: Anzeige der installierten Datenprovider

 

2 ConnectionObject
2.1 OleDbConnection
       Beispiel 1a: Erstellen einer Verbindung zu einer Accessdatenbank mit OleDB
       Beispiel 1b: Erstellen einer Verbindung zu einer Exceldatei mit OleDb
       Beispiel 1c: Erstellen einer Verbindung zu einem Datenbankserver mit  SQL-Datenbank mit OleDb
2.2 SQLConnection
        Beispiel 1: Demo zum Öffnen und Schließen einer SqlConnection
2.3 ServerConnection
         Beispiel 1: Daten aus einer SqlDatenbank mit der ServerConnection-Klasse auslesen

 

3 Die Command-Klasse (Lesen und Schreiben von Daten) 
3.1 Einführungsbeispiele (MSAccess, Excel, SQLServer)
         Beispiel 1a: Auslesen von Daten aus einer Accessdatenbank (OleDbCommand)
         Beispiel 1b: Auslesen von Daten aus einer Exceltabelle (OleDbCommand)
         Beispiel 1c: Auslesen von Daten von einem SQLServerDatenbank
         Beispiel 2: Alternativen, um das Commandobjekt zu erzeugen
3.2 Eigenschaften und Methoden der Command-Klasse

 

4 DataAdapter - Klasse
      Beispiel 1a: Erzeugen eines AdapterObjects mit dem Konstruktor "OleDbAdapter()"
      Beispiel 1b: Erzeugen eines AdapterObjects mit dem Konstruktor "OleDbAdapter(OleDbCommand)"
      Beispiel 1c: Erzeugen eines Adapterobjects mit dem Konstruktor "OleDbAdapter(String, OleDbConnection)"
      Beispiel 1d: Erzeugen eines Adapterobjects mit dem Konstruktor "OleDbAdapter(String, String)"
4.1 Methoden der DataAdapter - Klasse
4.1.1 Fill-Methode
            Beispiel 1: Übertragen von Daten aus Access in ein DataSet oder in eine separate DataTable OleDbDataAdapter.Fill(DataSet)  / OleDbDataAdapter.Fill(DataTable)
            Beispiel 2: Übertragen von Daten aus Access in ein DataSet bestehend aus mehreren DataTables
            Beispiel 3: Übertragen von Daten aus Excel in ein DataSet OleDbDataAdapter.Fill(DataSet)  
4.1.2 Update-Methode
            Beispiel 1: Schreiben von Systemdaten in eine Accesstabelle "Update(DataTable)"
            Beispiel 2: Daten aus Excel in eine DataTable schreiben, verändern und exportieren


0 Überblick

In diesem Kapitel arbeite ich detailliert die notwendigen Schritte ab, um Daten aus einer Datenquelle (Excel/ Access/ SQLServer) bis zum Arbeitsspeicher (=DataSet/ Datatable) zu bringen. Das DataSet und die DataTable werden genauer im Kapitel Disconnected Classes behandelt.

 

1 Datenprovider

Die Provider werden meist vom Hersteller der Datenbank selbst entwickelt. Der Anwender muss den benötigten Provider auf sein System bringen und kann mit deren Anwendung in einer Connection (siehe Kapitel 1.2 ConnectionObject) die Verbindung zur Datenquelle herstellen. Der Provider (=Providerklasse) ist also für den Datenaustausch zwischen den Disconnect-Klassen (DataSet/ DataTable) und den gespeicherten Daten (SQLDatenbank, Excel,..) zuständig.

Windows bringt schon eine Reihe von Providern mit, die man, wie im nächsten Beispiel gezeigt, auflisten kann. Sollten diese nicht ausreichen, findet man im Web für viele andere Datenbanken (mySQL, DB und andere) die passenden Provider zum Nachladen. siehe mySql - ADO.Net

 

Beispiel 1: Anzeige der installierten Datenprovider

Die aktuell auf dem System installierten ADO.NET Datenprovider bekommt man aus der Klasse "DBProviderFactories"

MSDN: DbProviderFactories.GetFactoryClasses-Methode

Set-StrictMode -Version "2.0"
Clear-Host

$Providers = [System.Data.Common.DBProviderFactories]::GetFactoryClasses()
$Providers | Foreach {
 "Name: {0}`nInvariantName: {1}`nDescription: {2}" -f `
     $_.Name,$_.InvariantName,$_.Description
 "{0}" -f "-"*40 #Trennlinien
}

#$Providers | Select *

#getestet mit Win7/ Powershell V3.0 / SQLServer 2008R2

Name: Odbc Data Provider
InvariantName: System.Data.Odbc
Description: .Net Framework Data Provider for Odbc
----------------------------------------
Name: OleDb Data Provider
InvariantName: System.Data.OleDb
Description: .Net Framework Data Provider for OleDb
----------------------------------------
Name: OracleClient Data Provider
InvariantName: System.Data.OracleClient
Description: .Net Framework Data Provider for Oracle
----------------------------------------
Name: SqlClient Data Provider
InvariantName: System.Data.SqlClient
Description: .Net Framework Data Provider for SqlServer
----------------------------------------
Name: Microsoft SQL Server Compact Data Provider
InvariantName: System.Data.SqlServerCe.3.5
Description: .NET Framework Data Provider for Microsoft SQL Server Compact
----------------------------------------

Unter dem "System.Data-Namespace" (MSDN: System.Data-Namespaces) befinden sich Klassen mit verschiedenen Anbietern (=Providern). Zum Beispiel sind Anbieter für SQL Server, Oracle, ODBC und OleDB verfügbar. Diese Provider ermöglichen den gekapselten Zugriff und die Verwaltung von Daten aus verschiedenen Quellen und natürlich können weitere Provider von Herstellern anderer Datenbanken eingebunden werden

Hier nocheinmal ein Vergleich der beiden wichtigsten Provider (OleDb / SqlClient) mit ihren Klassen, die ich im Folgenden auch behandeln werde.

 

2 ConnectionObject

Mit Hilfe eines geeigneten Providers und einer Connectionklasse kann man sich auf alle möglichen Datenquellen verbinden. Die wichtigste Eigenschaft dieser Klasse ist der "ConnectionString". In ihm werden der verwendete Provider, Sicherheitsparameter und weitere Parameter definiert. In den folgenden drei Unterkapiteln zeige ich Beispiele für den Verbindungsaufbau über 

  • OleDb-Klassen (siehe Kapitel 1.2.1)
  • die Klasse SqlConnection (siehe Kapitel 1.2.2)
  • die Klasse Serverconnection (siehe Kapitel 1.2.3)

Wie der Name "SqlConnection" schon andeutet, ist diese Klasse besonders auf SqlServer optimiert und ist daher so etwas wie die Standardconnection.

 

2.1 OleDbConnection

MSDN: OleDbConnection-Klasse

Mittels OleDB kann man verschiedenste Datenquellen (Excel, Access, SQLServer, etc.) wie eine Datenbank behandeln. Für echte Datenbanken wie einen SQL-Server existieren aber meist performantere Methoden. 

 

Beispiel 1a: Erstellen einer Verbindung zu einer Accessdatenbank mit OleDB

Hinweise für das Erstellen der hier verwendeten Nordwinddatenbank findet ihr hier: Access -> 1.1 Beispieldaten

Set-StrictMode -Version "2.0"
Clear-Host

$DataBaseName = "c:\temp\Nordwind.mdb"
$Provider="Provider=Microsoft.ACE.OLEDB.12.0"

#Zusammenbau des ConnectionStrings
$DataSource = "Data Source = $DataBaseName"
$ConnectionString = "$Provider;$DataSource"

#Connection Object erstellen und öffnen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#Eigenschaften des ConnectionObjects
"Status: $($Connection.State)"
$Connection.GetType() | Format-List Name,Basetype
"Provider: $($Connection.Provider)"
"DataSource: $($Connection.DataSource)"

#Schließen der Verbindung
$Connection.Close()

#getestet mit Office2010 und Win7 64bit/64bit
#mögliche Ausgabe

Status: Open

Name     : OleDbConnection
BaseType : System.Data.Common.DbConnection

Provider: Microsoft.ACE.OLEDB.12.0
DataSource: c:\temp\Nordwind.mdb

 
Beispiel 1b: Erstellen einer Verbindung zu einer Exceldatei mit OleDb

Set-StrictMode -Version "2.0"
Clear-Host

$FileName="c:\temp\excel\NordwindTables.xlsx"
$Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx)
#$Provider = "Provider=Microsoft.Jet.OLEDB.4.0" # nur für 32-Bit OfficeVersionen

#Zusammenbau des ConnectionStrings
$DataSource = "Data Source = $FileName"
$Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'"
#$Extend = "Extended Properties=Excel 8.0"  #Excel 97-2003 Workbook (.xls)
$ConnectionString = "$Provider;$DataSource;$Extend"

#Connection Object erstellen
und öffnen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#Ausgabe
"State: $($Connection.State)"
$Connection.gettype() | Format-List name,basetype
"Provider: $($Connection.Provider)"
"DataSource: $($Connection.DataSource)"

#Schließen der Verbindung
$Connection.Close()

""
"Status: $($Connection.State)"


#getestet mit Office2010 und Win7 64bit/64bit

#mögliche Ausgabe

State: Open

Name     : OleDbConnection
BaseType : System.Data.Common.DbConnection

Provider: Microsoft.ACE.OLEDB.12.0
DataSource: c:\temp\excelNordwindTables.xlsx

 

Status: Closed

Die Exceldatei "NordwindTables.xlsx" findet ihr zum Download am Ende dieser Seite

 
Beispiel 1c: Erstellen einer Verbindung zu einem Datenbankserver mit  einer SQL-Datenbank mit OleDb

Informationen, wie ihr euch eine Testumgebung mit SQL-Server und einer Beispieldatenbank erstellen könnt, findet ihr hier:

SQLServer -> Beispieldaten

Set-StrictMode -Version "2.0"
Clear-Host
 
$DatabaseServer = "Srv01"
$Database = "AdventureWorks2008R2"
 
$Provider = "Provider=SQLOLEDB"
$InitialCatalog = "Initial Catalog=$DataBase"
$Security="integrated security=SSPI"
 
#Zusammenbau des ConnectionStrings
$DataSource = "Data Source = $DatabaseServer"
$ConnectionString = "$Provider;$DataSource;$InitialCatalog;$Security"
 
#Connection Object erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()
 
#Ausgabe
"Status: $($Connection.State)"
 
$Connection.GetType() | Format-List Name,BaseType
 
"Provider: $($Connection.Provider)"
"DataSource: $($Connection.DataSource)"
 
#Schließen der Verbindung
$Connection.Close()
""
"Status: $($Connection.State)"
#mögliche Ausgabe

Status: Open
 
Name     : OleDbConnection
BaseType : System.Data.Common.DbConnection
 
Provider: SQLOLEDB
DataSource: Srv01
 
Status: Closed

Anmerkung 1:

Bei Excel (siehe Beispiel 1b)  ist es notwendig, zusätzlich die sogenannten "Extended Properties" im Connectionstring zu definieren.

Die richtigen Versionsnummern für die Extended Properities sind abhängig von der verwendeten Officeversion

File Type (extension)                                             Extended Properties
 ----------------------------------------------------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls)                    "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx)                 "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm)   "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb)  "Excel 12.0"

Quelle: http://www.microsoft.com/en-us/download/details.aspx?id=13255 -> Install Instructions

 

Weitere Möglichkeiten und Beispiele für einen Connectionstring findet ihr beispielsweise unter

Seht euch wirklich mal auf dem zweiten Link ein bischen um. Dann bekommt ihr ein Gefühl, wieviele weitere Stellschrauben im Connectionstring noch enthalten sind.

Anmerkung 2:

Laut mehreren Artikeln soll eine OleDB-Verbindung auf einen SQLServer etwas langsamer sein soll, als eine SqlConnection. die ich im nächsten Kapitel beschreibe.

 

2.2 SqlConnection

MSDN: SqlConnection-Klasse

 

Beispiel 1: Demo zum Öffnen und Schließen einer SqlConnection 

Set-StrictMode -Version "2.0"
Clear-Host

$SQLServerName = "Dom1Srv01"
$DataBaseName = "AdventureWorks"

#Zusammenbau des ConnectionStrings
$Server = "Server=$SqlServerName" #
$Server = "Server=."
$DataBase = "Database=$DataBaseName"
$Security = "Integrated Security=True"

#Erstellen der SqlConnection 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "$Server;$DataBase;$Security"
 

#Status der Connection verändern und ausgeben 
$SqlConnection.Open()
"Status: {0}" -f $SqlConnection.State

$SqlConnection.Close()
"Status: {0}" -f $SqlConnection.State
#Ausgabe

Status: Open
Status: Closed

Die OleDb-/ SqlConnectionklassen aus diesem und dem letzten Kapitel sind sich relativ ähnlich. Laut MSDN ist die SqlConnection dennoch vorzuziehen, da diese für Zugriffe auf Sql-Server optimiert ist. Wenn ihr komplette Beispiele mit Datenausgabe sucht, schaut mal unter: Datenzugriffe über ADO.Net - Microsoft SQL-Server -> 3 praktische Beispiele

 

Auch für den SqlConnectionString gibt es zahlreiche Beispiele unter: https://www.connectionstrings.com/sql-server-2008/ . Wem diese Beispiele auch noch nicht ausreichen, kann sich die möglichen Keywörter eines ConnectionStrings unter

ansehen

 

2.3 ServerConnection

Abschließend noch der Verbindungsaufbau über die Klasse ServerConnection. Hier geht der Verbindungserstellung wohl am einfachsten, da hier kein extra Connectionstring erforderlich ist und für die Übergabe des SqlStatements kein Command-Objekt erforderlich ist Das Statement wird direkt der ExecuteReader-Klasse als Parameter übergeben.

 

Beispiel 1: Daten aus einer SqlDatenbank mit der ServerConnection-Klasse auslesen

Set-StrictMode -Version "2.0"
Clear-Host

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$DatabaseName="AdventureWorks"
$SqlServerName = "Dom1Srv01"  #$SqlServerName = "Dom1Srv01"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName)
$ServerConnection.DatabaseName = $DatabaseName

$QueryString = "SELECT Name, CostRate FROM Production.Location"
$Reader = $ServerConnection.ExecuteReader($QueryString)
while ($Reader.Read())
{
   $Name = $Reader.GetValue(0);
   $cRate = $Reader.GetValue(1);
   Write-Host $Name,"(",$cRate,")"
}
$Reader.Close()
#Ausgabe

Tool Crib ( 0,0000 )
Sheet Metal Racks ( 0,0000 )
Paint Shop ( 0,0000 )

Um auf die Klassen aus dem Namespace "Microsoft.SqlServer.ConnectionInfo" zugreifen zu können, muss die entsprechende Assembly zuerst geladen werden. Im VisualStudio entspricht dies dem Hinzufügen eines Verweises

Da ich für die Serverconnection-Klasse keine weiteren Beispiele ausführen werde, habe ich hier den rötlich geschriebenen Teil im Skript hinzugefügt. Dadurch habt ihr hier ein komplettes Beispiel.

 

Eine Beschreibung zu Assemblies, wie diese angezeigt und gegebenfalls nachgeladen werden können, findet ihr unter Grundlagen -> Automation Interfaces -> .Net -> 4. Assemblies

 

Zusammenfassung: In den letzten drei Unterkapiteln 1.2.1 bis 1.2.3 wurden mit Hilfe verschiedener Provider Connectionobjekte auf eine Datenbank erstellt. Auf diese Connectionobjekte kann nun die passende Command-Klasse angewendet werden, wie im nächsten Kapitel gezeigt wird. 

 

3 Die Command-Klasse (Lesen und Schreiben von Daten)

Je nach Art der Datenquelle benötigt man nun die passende Command-Klasse

Wenn die Verbindung zur Datenquelle, wie im letzten Kapitel 1.2ff gezeigt, aufgebaut ist, benützt man das Command-Object um mit einem SQLStatement bestimmte Daten einer Quelle auszulesen. Das es aber auch anders geht, zeigte das letzte Beispiel in Kapitel 1.2.3 Serverconnection.

 

3.1 Einführungsbeispiele (MSAccess, Excel, SQLServer)

Ich zeige hier anhand der nächsten drei Beispiele, wie ähnlich der Zugriff auf eine Accessdatenbank, eine Exceltabelle und eine SQLDatenbank mit der jeweils passenden Connection- und der CommandKlasse abläuft.

Die einfache Datenausgabe mit dem DataReader am Ende der Skripte dient nur der Demonstration. In den Beispielen geht es in erster Linie um das Commandobjekt, das bei Zugriff auf Officeanwendungen aus der Klasse "System.Data.OleDb.OleDbCommand", bei Zugriff auf eine MSSQLDatenbank aus der Klasse "System.Data.SqlClient.SqlCommand" erstellt wird.

 

Beispiel 1a: Auslesen von Daten aus einer Accessdatenbank (OleDbCommand)

Hinweise für das Erstellen der hier verwendeten Nordwinddatenbank findet ihr hier: Access -> 1.1 Beispieldaten

Set-StrictMode -Version "2.0"
Clear-Host

#Variablendefinition
$DatabaseName = "c:\temp\Nordwind.mdb"
$Provider="Provider=Microsoft.ACE.OLEDB.12.0"
$TableName = "Lieferanten"
$Query = "Select Top 3 * From $TableName"

#Zusammenbau des ConnectionStrings
$DataSource = "Data Source = $DataBaseName"
$ConnectionString = "$Provider;$DataSource"

#Connection Object erstellen und öffnen
$AccConnection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$AccConnection.Open()

#OleDbCommand-Object erstellen
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
$OleDbCommand.CommandText = $Query
$OleDbCommand.Connection = $AccConnection


#Daten einlesen
[System.Data.Common.DbDataReader]$DbDataReader = $OleDbCommand.ExecuteReader()

#Beispielausgabe
While($DbDataReader.Read())
{
  $LieferantenNr = $DbDataReader.GetValue(0);
  $Firma = $DbDataReader.GetValue(1);
  $Kontaktperson = $DbDataReader.GetValue(2);
  Write-Host "$LieferantenNr;$Firma;$Kontaktperson"
 }  
 $DbDataReader.Close()

 
#Schließen der Verbindung
$AccConnection.Close()


#getestet mit Office2010 und Win7 64bit/64bit
#Ausgabe

1;Exotic Liquids;Charlotte Cooper
2;New Orleans Cajun Delights;Shelley Burke
3;Grandma Kelly's Homestead;Regina Murphy

 

Beispiel 1b: Auslesen von Daten aus einer Exceltabelle ( OleDbCommand )

Die Exceldatei "NordwindTables.xlsx" findet ihr zum Download am Ende dieser Seite

Set-StrictMode -Version "2.0"
Clear-Host

#Variablendefinition 

$FileName = "c:\temp\excel\NordwindTables.xlsx"
$TableSheetName = "Lieferanten"
$Query = "Select Top 3 * From [$TableSheetName$]"

#Zusammenbau des ConnectionStrings
$Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx)
$DataSource = "Data Source = $FileName"
$Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'"
$ConnectionString = "$Provider;$DataSource;$Extend"

#Aufbau der Connection
$XLConnection = New-Object System.Data.OleDb.OleDbConnection
$XLConnection.ConnectionString = $ConnectionString
$XLConnection.Open()

#OleDbCommand-Object erstellen
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
$OleDbCommand.CommandText = $Query
$OleDbCommand.Connection = $XLConnection


#Daten einlesen
[System.Data.Common.DbDataReader]$DbDataReader = $OleDbCommand.ExecuteReader()


#Beispiel für eine Ausgabe
While($DbDataReader.Read())
 {
  $LieferantenNr = $DbDataReader.GetValue(0);
  $Firma = $DbDataReader.GetValue(1);
  $Kontaktperson = $DbDataReader.GetValue(2);
  Write-Host "$LieferantenNr;$Firma;$Kontaktperson"
 }  
$DbDataReader.Close()

$XLConnection.Close()

#getestet mit Office2010 und Win7 64bit/64bit
#Beispiel 1b: schlankerer Skriptrahmen
#Ausgabe

1;Exotic Liquids;Charlotte Cooper
2;New Orleans Cajun Delights;Shelley Burke
3;Grandma Kelly's Homestead;Regina Murphy

 

Beispiel 1c: Auslesen von Daten von einem SQLServerDatenbank (SqlCommand)

Set-StrictMode -Version "2.0"
Clear-Host

#Nachladen der Assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

#Variablendefinition
$Query = "SELECT Top 3 * FROM Production.Location"
$SQLServerName = "Dom1srv01"
$DataBaseName = "AdventureWorks"

#Zusammenbau des ConnectionStrings
$Server="Server=$SqlServerName"
$DataBase="Database=$DataBaseName"
$Security="Integrated Security=True"
$ConnectionString ="$Server;$DataBase;$Security"

#Aufbau der Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
 
#SqlCommand-Object erstellen
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = $Query
$SqlCommand.Connection = $SqlConnection


#Daten einlesen
[System.Data.SqlClient.SqlDataReader]$SqlDataReader = $SqlCommand.ExecuteReader()

#Beispiel für eine Ausgabe
While ($SqlDataReader.Read())
{
   $Name = $SqlDataReader.GetValue(0);
   $cRate = $SqlDataReader.GetValue(1);
   Write-Host $Name,"(",$cRate,")"
}
$SqlDataReader.Close()


#getestet mit Win7/ Powershell V2.0 / SQLServer 2008R2

#Ausgabe
 
1 ( Tool Crib )
2 ( Sheet Metal Racks )
3 ( Paint Shop )

 
Beispiel 2: Alternativen, um das Commandobjekt zu erzeugen

Beim Erzeugen des CommandObjects (= dunkelrot markierter Code in den obigen Skripten) kann der SqlQuery-String und das ConnectionObject in den Konstruktor der CommandKlasse gepackt werden. Ebenso kann das Commandobject auch über die CreateCommand-Methode der Connection-Klasse erzeugt werden.

#Alternativen zur Erzeugung des Commandobjects in Beispiel 1a (Access)

#Variante 1
#$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
#$OleDbCommand.CommandText = $Query
#$OleDbCommand.Connection = $AccConnection

#Variante 2
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand($Query,$AccConnection)

#Variante 3
#$OleDbCommand = $AccConnection.CreateCommand()
#$OleDbCommand.CommandText = $Query

#Alternativen zur Erzeugung des Commandobjects in Beispiel 1b (Excel)

#Variante 1
#$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
#$OleDbCommand.CommandText = $Query
#$OleDbCommand.Connection = $XLConnection

#Variante 2
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand($Query,$XLConnection)

#Variante 3
#$OleDbCommand = $XLConnection.CreateCommand()
#$OleDbCommand.CommandText = $Query

#Alternativen zur Erzeugung des Commandobjects in Beispiel 1c (SqlDatenbank)

#Variante 1
#$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
#$SqlCommand.CommandText = $Query
#$SqlCommand.Connection = $SqlConnection

#Variante 2
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query,$SqlConnection)

#Variante 3
#$SqlCommand = $SqlConnection.CreateCommand()
#$SqlCommand.CommandText = $Query

Den kompaktesten Code bietet Variante 2, die den SqlString und das ConnectionObject gleich im Konstruktor an die Klasse übergibt. Aber auch die beiden anderen Varianten 1 und 3 mit getrennter Zuweisung sind möglich.

Hier noch die zugehörigen MSDN-Seiten zu den Varianten

 

3.2 Eigenschaften und Methoden der Command-Klasse

Die Command-Klasse bietet eine Reihe von Eigenschaften und Methoden an.

Im letzten Kapitel haben wir gesehen, dass das Verbinden auf eine Datenquelle eine konsistente Angelegenheit ist, egal ob sich bei der Datenquelle um eine SqlDB, Excel oder eine MSAccessDB handelt. Der Einfachheit halber behandele ich in diesem Kapitel nur Beispiele aus der Accessbeispieldatenbank Nordwind.mdb, die jeder zur Verfügung haben dürfte. Anhand der Beispiele in Kapitel 1.3.1 ist es ja nicht weiter schwer, die Beispiele auf SQLServer oder Excel umzuschreiben.

 

4 DataAdapter - Klasse

Die Adapterklassen bilden die Verbindung zwischen der  Datenquelle (=Exceltabelle, Access, SqlServer) und dem in Kapitel 2 näher behandelten Dataset.

wie schon in den vorausgegangenen Kapiteln, verwendet man die OleDb-Klasse bei Officeanwendungen, die auf den SqlServer die entsprechend optimierte Sql-Serverklasse.

Im unten gezeigten Screenshot aus der MSDN sieht man, dass die Klasse mit vier Konstruktoren überladen ist. Je nach verwendeten Konstruktor wird der Powershellcode mal etwas kompakter, mal vielleicht etwas verständlicher.

die folgenden Beispiele zeigen die vier Konstruktoren jeweils in einem Powershellcode. Das Ergebnis ist aber überall identisch

 

Beispiel 1a: Erzeugen eines AdapterObjects mit dem Konstruktor "OleDbAdapter()"

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT * FROM Kunden"
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=$DataBaseName;"

#ConnectionObject/ OleDbCommandObject erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OleDbCommand  = New-Object System.Data.OleDb.OleDbCommand($Query,$Connection)
$Connection.Open()

#Daten in den Adapter laden
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
$DataAdapter.SelectCommand = $OleDbCommand

#Dataset erstellen, Daten mit der Fill-Methode übertragen
$Dataset = New-Object System.Data.DataSet
[void]$DataAdapter.Fill($DataSet)
$Connection.Close()

#Daten ausgeben
$DataSet.Tables.Item(0) |  ft Firma,Position -auto

 
Beispiel 1b: Erzeugen eines AdapterObjects mit dem Konstruktor "OleDbAdapter(OleDbCommand)"

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT * FROM Kunden"
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=$DataBaseName;"

#ConnectionObject/ OleDbCommandObject erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OleDbCommand  = New-Object System.Data.OleDb.OleDbCommand($Query,$Connection)
$Connection.Open()

#Daten in den Adapter laden
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($OleDbCommand)

#Dataset erstellen, Daten mit der Fill-Methode übertragen
$Dataset = New-Object System.Data.DataSet
[void]$DataAdapter.Fill($DataSet)
$Connection.Close()

#Daten ausgeben
$DataSet.Tables.Item(0) |  ft Firma,Position -auto

 
Beispiel 1c: Erzeugen eines Adapterobjects mit dem Konstruktor "OleDbAdapter(String, OleDbConnection)"

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT * FROM Kunden"
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=$DataBaseName;"

#ConnectionObject erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#Daten zuerst in den Adapter laden
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($Query,$Connection)

#Dataset erstellen, Daten mit der Fill-Methode übertragen
$Dataset = New-Object System.Data.DataSet
[void]$DataAdapter.Fill($DataSet)
$Connection.Close()

#Daten schreiben
$DataSet.Tables.Item(0) |  ft Firma,Position -auto

 
Beispiel 1d: Erzeugen eines Adapterobjects mit dem Konstruktor "OleDbAdapter(String, String)"

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT * FROM Kunden"
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$DataBaseName;"

#ConnectionObject erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#Daten zuerst in den Adapter laden
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($Query,$Connectionstring)

#Dataset erstellen, Daten mit der Fill-Methode übertragen
$Dataset = New-Object System.Data.DataSet
[void]$DataAdapter.Fill($DataSet)
$Connection.Close()

#Daten schreiben
$DataSet.Tables.Item(0) |  ft Firma,Position -auto

#Ausgabe gekürzt (für alle Beispiele gleich)

 

Firma                                Position                  
-----                                --------                  
Alfreds Futterkiste                  Vertriebsmitarbeiterin    
Ana Trujillo Emparedados y helados   Inhaberin                 
Antonio Moreno Taquería              Inhaber     

  • Welche der 4 Konstruktoren man zum Erstellen des Adapterobjects benutzt, ist reine Geschmackssache. 
  • Der Einsatz der SqlDataAdapter-Klasse zum Zugriff auf eine SqlDatenbank ist identisch zur OleDbData-Klasse
  • Die DataSet-Klasse benutze ich hier vorab nur zur Demo. Genauer wird die Klasse im Kapitel 2 Disconnected Classes (DataSet /DataTable) behandelt.

 

4.1 Methoden der DataAdapter - Klasse

 

4.1.1 Fill-Methode

Mit der Fill-Methode werden Datensätze (=Zeilen) aus der Datenquelle in ein DataSet beziehungsweise in ein DataTable angehängt oder aktualisiert. Die Fill-Methode ist überladen, kann also mit verschiedenen Parameterlisten aufgerufen werden (siehe: MSDN-Link der FillMethode)

Anbei ein paar Beispiele, wie die FillMethode angewendet werden kann. Auf die verwendeten Klassen "DataSet" und "DataTable" gehe ich unter Disconnected Classes näher ein.

 

Beispiel 1: Übertragen von Daten aus Access in ein DataSet oder in eine separate DataTable OleDbDataAdapter.Fill(DataSet)  / OleDbDataAdapter.Fill(DataTable)

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query = "SELECT Top 5 *  FROM Artikel "
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$DataBaseName;"

#Connection Object erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#DataAdapter erstellen
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($Query,$Connectionstring)

#Dataset und DataTable erstellen
$Dataset = New-Object System.Data.DataSet
$DataTable = New-Object System.Data.DataTable("KopieVonKunden")

#Daten mit der Fill-Methode übertragen
[void]$DataAdapter.Fill($DataSet)
[void]$DataAdapter.Fill($DataTable)

#Verbindung schließen
$Connection.Close()

#Daten der Datatable ausgeben
#"TableName: {0}" -f $DataTable.TableName
#$DataTable.Rows  | Format-Table Artikelname,Einzelpreis -AutoSize

#Daten des DataSets ausgeben
"Tablename: {0} " -f $DataSet.Tables.Tablename
$DataSet.Tables.Item(0) | Format-Table Artikelname,Einzelpreis -AutoSize

#Ausgabe

Tablename: Table

Artikelname                  Einzelpreis
-----------                  -----------
Chai                                   9
Chang                                9,5
Aniseed Syrup                          5
Chef Anton's Cajun Seasoning          11
Chef Anton's Gumbo Mix            10,675

In diesem Beispiel habe ich Daten aus der Nordwinddatenbank in einen DataAdapter geladen. Erklärungen zu den Schritten findet ihr in den vorangegangen Kapiteln. Interessant für dieses und die nächsten Beispiele ist die Übertragung der Daten aus dem DataAdapterObject in eine DataTable und in ein DataSet.

 
Beispiel 2: Übertragen von Daten aus Access in ein DataSet bestehend aus mehreren DataTables 

Konstruktor: OleDbDataAdapter(DataSet, String)

  • DataSet: DataSetobject
  • String: Tabellenname, in den die Daten geschrieben werden. Wenn die Tabelle noch nicht existiert, wird diese erstellt.

Set-StrictMode -Version "2.0"
Clear-Host

$DatabaseName = "c:\temp\Nordwind.mdb"
$Query_0 = "SELECT * FROM Artikel"
$Query_1 = "SELECT * FROM Bestelldetails"

#$Query_1 = "SELECT * FROM [Order Details]" #[]-falls Leerzeichen im Tablename
$Query_2 = "SELECT * FROM Bestellungen"

$Query_3 = "SELECT Top 5 * FROM Kunden"        

#Connection Object erstellen
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$DataBaseName;"
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.Open()

#DataAdapter erstellen
$DataAdapter_0 = New-Object System.Data.OleDb.OleDbDataAdapter($Query_0,$Connectionstring)
$DataAdapter_1 = New-Object System.Data.OleDb.OleDbDataAdapter($Query_1,$Connectionstring)
$DataAdapter_2 = New-Object System.Data.OleDb.OleDbDataAdapter($Query_2,$Connectionstring)
$DataAdapter_3 = New-Object System.Data.OleDb.OleDbDataAdapter($Query_3,$Connectionstring)

#Dataset erstellen
$NordwindDataset = New-Object System.Data.DataSet

#Daten mit der Fill-Methode übertragen
[void]$DataAdapter_0.Fill($NordwindDataset,"Artikel") #Namen sind aber beliebig
[void]$DataAdapter_1.Fill($NordwindDataset,"Bestelldetails")

#[void]$DataAdapter_1.Fill($NordwindDataset,"OrderDetails")  #keine Leerzeichen erlaubt
[void]$DataAdapter_2.Fill($NordwindDataset,"Bestellungen")
[void]$DataAdapter_3.Fill($NordwindDataset,"Kunden")


"TabellenNamen:"
$NordwindDataSet.Tables.Tablename
""
"Anzahl der Tabellen im Dataset: {0}" -f $NordwindDataSet.Tables.Count
$NordwindDataSet.Tables.Item("Kunden") | Format-Table Firma,Kontaktperson -auto

$Connection.Close()

#Ausgabe

TabellenNamen:
Table
Bestelldetails
Bestellungen
Kunden

Anzahl der Tabellen im Dataset: 4

Firma                              Kontaktperson     
-----                              -------------     
Alfreds Futterkiste                Maria Anders      
Ana Trujillo Emparedados y helados Ana Trujillo      
Antonio Moreno Taquería            Antonio Moreno    
Around the Horn                    Thomas Hardy      
Berglunds snabbköp                 Christina Berglund

 

Beispiel 3: Übertragen von Daten aus Excel in ein DataSet OleDbDataAdapter.Fill(DataSet) 

Set-StrictMode -Version "2.0"
Clear-Host

#Variablendefinition
$FileName = "c:\temp\excel\NordwindTables.xlsx"
$TableSheetName = "Lieferanten"
$Query = "Select Top 3 * From [$TableSheetName$]"

#Zusammenbau des ConnectionStrings
$Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx)
$DataSource = "Data Source = $FileName"
$Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'"
$ConnectionString = "$Provider;$DataSource;$Extend"

#Aufbau der Connection
$XLConnection = New-Object System.Data.OleDb.OleDbConnection
$XLConnection.ConnectionString = $ConnectionString
$XLConnection.Open()

#OleDbCommand-Object erstellen
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
$OleDbCommand.CommandText = $Query
$OleDbCommand.Connection = $XLConnection

$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
$DataAdapter.SelectCommand = $OleDbCommand

#Dataset erstellen, Daten mit der Fill-Methode übertragen
$Dataset = New-Object System.Data.DataSet
[void]$DataAdapter.Fill($DataSet)
$XLConnection.Close()

#Daten ausgeben
$DataSet.Tables.Item(0) |  ft Firma,Position -auto
#Ausgabe

Firma                      Position                     
-----                      --------                     
Exotic Liquids             Einkaufsmanager              
New Orleans Cajun Delights Bestellungen-Sachbearbeiterin
Grandma Kelly's Homestead  Vertriebsmitarbeiterin      

Das Beispiel 3 ist praktisch identisch zu Beispiel 1, nur dass als Datenquelle eben eine Exceldatei (Download am Ende des Kapitels) verwendet wird

 

4.1.2 Update-Methode

MSDN: DbDataAdapter.Update-Methode

Die Daten haben wir in den Beispielen des letzten Kapitels mit der Fill-Methode in ein DataSet oder eine DataTable geschrieben. Objekte dieser beiden Klassen sind nicht mehr mit der Datenquelle verbunden (=Disconnected Classes, siehe Kapitel 2), daher werden Änderungen im DataSet nicht automatisch zurück in die Datenquelle geschrieben. Die DbDataAdapter-Klasse stellt für diese Aufgabe die Update-Methode bereit, die in den nächsten Beispielen angewendet wird. Auch von der Update-Methode gibt es, wie bei der Fill-Methode, verschiedene Versionen (siehe MSDN-Link), die alle zumindest vom OleDb- und dem SqlDbDatenProvider unterstützt werden .


Beispiel 1: Schreiben von Systemdaten in eine Accesstabelle "Update(DataTable)"

Damit der Update funktioniert, benötigt das Skript zusätzlich ein OleDbCommandbuilder-Objekt

Set-StrictMode -Version "2.0"
Clear-Host
          
$DatabaseName = "c:\temp\servers.accdb" 
$Query = "SELECT * FROM tbl_DriveC"
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Databasename;"
 
#Connection Object erstellen
$Connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OleDbCommand  = New-Object System.Data.OleDb.OleDbCommand($Query,$Connection)
$Connection.Open()
 
#Daten in den Adapter laden - Konstruktor: OleDbAdapter(OleDbCommand)
$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($OleDbCommand)
$Null=New-Object System.Data.OleDb.OleDbCommandBuilder($DataAdapter)
 
#DataTable erstellen, Daten mit der Fill-Methode übertragen
$DataTable = New-Object System.Data.DataTable 
[void]$DataAdapter.Fill($DataTable) 
 
#Nacheinander jede DataRow behandeln
$DataTable | ForEach { 
  #Wmi-Abfrage als Datenlieferant
  $Query="Select * FROM Win32_LogicalDisk WHERE DeviceID = 'C:'"
  $LogicalDisks=@()
  $LogicalDisks=Get-WmiObject -query $Query -computername $($_.Servername)
  #Datensätze mit Daten befüllen
  $_.myDate  = [DateTime]::Now
  $_.SizeC_in_Gb = $($LogicalDisks.Size)/1GB
  $_.SizeC_in_Gb = [math]::Round($_.SizeC_in_Gb,2)
  $_.FreeSpaceC_in_Gb=$($LogicalDisks.FreeSpace)/1GB
  $_.FreeSpaceC_in_Gb=[math]::Round($_.FreeSPaceC_in_Gb,2)
  $_.FreeRatio_in_Percent = 100*$($_.FreeSpaceC_in_Gb)/$($_.SizeC_in_Gb)
  $_.FreeRatio_in_Percent = [math]::Round($_.FreeRatio_in_Percent,2)
}#Foreach    
  
#Update der Datenquelle       
[void]$DataAdapter.Update($DataTable)
   
#Schließen der Verbindung    
$Connection.Close()

#getestet mit Access2010 und Win7 64bit/64bit

Damit ihr das Beispiel leicht nachvollziehen könnt, habe ich euch die Access-Datenbank unten als Anhang angehängt. Entfernt das -txt-Suffix und entpackt die Datanbank.

Wenn ihr das Beispiel anpassen wollt, gebt auf ein paar Dinge acht.

  • Aufgrund der fehlenden Fehlerbehandlung muss bei jeder ID auch ein Servername stehen. Ein leeres Feld würde einen Fehler verursachen
  • Bei den Feldnamen (SizeC, FreespaceC_in_Gb, ...) sollten keine Leerzeichen vorkommen. Manchmal funktioniert das Skript auch mit Leerzeichen, aber oft kommt es zu Fehlern. Ein Muster konnte ich nicht erkennen

 

Beispiel 2: Daten aus Excel in eine DataTable schreiben, verändern und exportieren

Meine Versuche, dasselbe Prinzip wie in Beispiel 1 (Daten aus einer Datenquelle wie Access über einen DataAdapter in eine DataTable zu schreiben, dann zu verändern und anschließend mit der Update-Methode die Daten in Access zu aktualisieren) schlägt mit einem Excelarbeitsblatt leider fehl. Zumindest habe ich es nicht geschafft und auch keine Beispiele dafür gefunden.

Alternativ kann man eine DataTable aber sehr leicht in eine Csv-Datei exportieren, um diese dann in Excel zu laden.

Set-StrictMode -Version "2.0"
Clear-Host

#Variablendefinition
$FileName = "c:\temp\excel\Test1.xlsx"
$TableSheetName = "myTabelle1"
$Query = "Select Top 3 * From [$TableSheetName$]"

#Zusammenbau des ConnectionStrings
$Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx)
$DataSource = "Data Source = $FileName"
$Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'"
$ConnectionString = "$Provider;$DataSource;$Extend"

#Aufbau der Connection
$XLConnection = New-Object System.Data.OleDb.OleDbConnection
$XLConnection.ConnectionString = $ConnectionString
$XLConnection.Open()

#OleDbCommand-Object erstellen
$OleDbCommand = New-Object System.Data.OleDb.OleDbCommand
$OleDbCommand.CommandText = $Query
$OleDbCommand.Connection = $XLConnection

$DataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
$DataAdapter.SelectCommand = $OleDbCommand
$Null=New-Object System.Data.OleDb.OleDbCommandBuilder($DataAdapter)

#DataTable erstellen, Daten mit der Fill-Methode übertragen
$DataTable = New-Object System.Data.DataTable("TestTable")
[void]$DataAdapter.Fill($DataTable)

#Verändern der Daten im DataTable
#Zeile0 (=Zeile1 in Excel)  verändern
$DataTable.Rows[0].Spalte1 = 25
$DataTable.AcceptChanges()

#neue Zeile anhängen
$NewRow = $DataTable.NewRow()  #TypeName DataRow
$NewRow["Spalte1"] = 100
$NewRow["Spalte2"] = 200
$DataTable.Rows.Add($NewRow)

<#
$DataTable | foreach{
  "$($_.Spalte1) $($_.Spalte2)"  
  }
#>

$DataTable | Ft Spalte1,Spalte2 -auto

#Export der geänderten DataTable
#$DataAdapter.Update($DataTable) #funktioiert leider nicht

#Workaround
$DataTable | Export-Csv c:\Test.csv -notype -force

$XLConnection.Close()

#Ausgabe

Spalte1 Spalte2
------- -------
     25       1
      4       3
    100     200

Die unheimlich komplexe Excelmappe Test1.xlsx findet ihr unten zum Download: "Kap_1.4.1.2-Beispiel2_Test1.xlsx.txt"

Eine weitere Möglichkeit, Daten direkt in ein ExcelBlatt zu schreiben, findet ihr im NachbarKapitel Datenzugriffe über ADO.Net - Excel -> 1.2.5 SELECT...INTO Statement