Podstawy PowerShell SQL

Niniejszy post dedykuję znajomemu. Mianowicie, zostałem poproszony o pomoc w kilku projektach, w ramach których dostałem zasadnicze zapytanie: Czy i w jaki sposób można dostać się z poziomu skryptów PowerShella do danych zamieszczonych w bazie pracującej na instancji MS SQL Server. Oczywiście samo rozwiązanie nie zawierało tylko i wyłącznie odczytu danych, ale również i modyfikację jak i zapis nowych informacji do danych.
Moja odpowiedź brzmi: Oczywiście, że tak, a jak tego dokonać przybliżę w niniejszym poście.

Najprostszym rozwiązaniem jest wykorzystanie modułu SQLPS, gdzie cała procedura pracy na bazie danych polega na wykorzystaniu odpowiedniego cmdletu jakim jest Invoke-Sqlcmd.

Import-Module SQLPS
Invoke-Sqlcmd -Query "SELECT GETDATE()"

lub w przypadku bardziej złożonych zapytań zapisanych w pliku:

Invoke-Sqlcmd -InputFile "C:\SQL-Script.sql"

Ale gdzie tutaj całą zabawa…

Co zrobić gdy nie posiadamy nowej wersji serwera SQL, która to zawiera w/w moduł? W takiej sytuacji pozostaje do wykorzystania .NET a konkretnie klasa System.Data.SqlClient.SqlConnection.

Aby zaprezentować tą funkcjonalność przygotujmy testowe środowisko. Posiadamy maszynę z lokalną instancją MS SQL Server 2005 Express. W ramach niej pracuje testowa baza danych z nie skomplikowaną tabelą o niniejszej testowej strukturze:

ID Int, PK
Attribute1 nchar(10)
Attribute2 nchar(10)
Attribute3 nchar(10)

Rozpocznijmy, więc zabawę. Podstawową operacją jaką musimy wykonać, by móc zarządzać danymi w bazie to nawiązanie połączenia z serwerem MS SQL. Do tego celu potrzebujemy zdefiniować kilka zmiennych. Mianowicie, najważniejsze z nich to adres serwera oraz ewentualna baza danych do której chcemy się podłączyć.

$dataSource = "LocalHost"
$database = "TESTOWA"

Bazując na powyższych zmiennych możemy przystąpić do zbudowania ConnectionString’a:

$connectionString = "Data Source=$dataSource; " +
"Initial Catalog=$database; " +
"Integrated Security=SSPI;"

Oczywiście parametrów połączenia możemy być znacznie więcej, a zależy to tylko od naszych potrzeb.

Sednem całej operacji jest utworzenie obiektu klienta SQL bazującego na powyższych parametrach połączenia.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

Gdy posiadamy poprawnie utworzony obiekt klienta, wykorzystujemy metodę Open() w celu nawiązania połączenia do bazy danych.

$SqlConnection.Open()

Kolejnym etapem w naszej PowerShellowej podróży po SQLu jest utworzenie obiektu zapytania SQL. Do tego celu wykorzystując obiekt klienta wraz z metodą CreateCommand().

$SqlCommand = $SqlConnection.CreateCommand()

To właśnie w tym obiekcie dla atrybutu CommandText definiujemy podstawowe zapytanie SQL.

$SqlCommand.CommandText = "INSERT INTO TestTable (ID,Attribute1,Attribute2,Attribute3) VALUES (@ID,@Attribute1,@Attribute2,@Attribute3)"

Powyższe zapytanie będzie pozwalać na dodanie jednego wiersza z danymi do naszej testowej tabeli. Jak możemy również zaobserwować, aby nasze zapytanie było bardziej profesjonalne wykorzystywane są parametry. Dlatego by zdefiniować ich wartości wykorzystujemy atrybut Parameters, który tak naprawdę definiuje listę atrybutów wraz z ich poszczególnymi wartościami.

$SqlCommand.Parameters.AddWithValue("@ID", $Row.ID)
$SqlCommand.Parameters.AddWithValue("@Attribute1", $Row.Attribute1)
$SqlCommand.Parameters.AddWithValue("@Attribute2", $Row.Attribute2)
$SqlCommand.Parameters.AddWithValue("@Attribute3", $Row.Attribute3)

Gdzie zmienna $Row zawiera wszystkie niezbędne wartości jakie chcemy zamieścić w bazie danych.

Oczywiście nic nie stoi na przeszkodzie, aby zdefiniować wszystko w formie gotowego zapytania, np…

"INSERT INTO TestTable (ID,Attribute1,Attribute2,Attribute3) VALUES ($($Row.ID), $($Row.Attribute1), $($Row.Attribute2), $($Row.Attribute3))"

…jednakże nie jest już ono takie przejrzyste jak z wykorzystaniem parametrów.

Tak zdefiniowany obiekt zapytania wystarczy tylko zatwierdzić metodą ExecuteNonQuery(), aby umieścić dane w bazie.

$SqlCommand.ExecuteNonQuery()

Ostatecznie pozostaje tylko zakończyć połączenie do bazy danych.

$SqlConnection.Close()

Oczywiście jest to tylko najprostszy z możliwych przykładów zapisu do bazy danych. W przypadku gdy będziemy zamierzali w hurtowy sposób dokonywać importu czy modyfikacji danych wystarczy, iż obiekt $SqlCommand będzie wykorzystany w ramach pętli.

Na przykład, importujemy partię danych z pliku csv:

$TestData = Import-Csv C:\TestData.txt

Ustanawiamy połączenie i rozpoczynamy zapętlone zapytanie zapisu danych.

$dataSource = "LocalHost"
$database = "TESTOWA"

$connectionString = "Data Source=$dataSource; " +
"Initial Catalog=$database; " +
"Integrated Security=SSPI;"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$SqlConnection.Open()
$SqlCommand = $SqlConnection.CreateCommand()

foreach($Row in $TestData)
{
  $SqlCommand.CommandText = "INSERT INTO TestTable (ID,Attribute1,Attribute2,Attribute3) VALUES (@ID,@Attribute1,@Attribute2,@Attribute3)"

  $SqlCommand.Parameters.AddWithValue("@ID", $Row.ID) | Out-Null
  $SqlCommand.Parameters.AddWithValue("@Attribute1", $Row.Attribute1) | Out-Null
  $SqlCommand.Parameters.AddWithValue("@Attribute2", $Row.Attribute2) | Out-Null
  $SqlCommand.Parameters.AddWithValue("@Attribute3", $Row.Attribute3) | Out-Null

  $SqlCommand.ExecuteNonQuery()
  $SqlCommand.Parameters.Clear()
}

$SqlConnection.Close()

Analogicznie postępujemy w przypadku odczytu danych. Jednakże w tym przypadku dla ułatwienia prezentacji danych musimy posłużyć się dwoma dodatkowymi obiektami: SqlDataAdapter oraz DataSet. Mianowicie, łączymy się do bazy danych:

$dataSource = "LocalHost"
$database = "TESTOWA"

$connectionString = "Data Source=$dataSource; " +
"Initial Catalog=$database; " +
"Integrated Security=SSPI;"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$SqlConnection.Open()

Tworzymy obiekt z zapytaniem SQL:

$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = "SELECT * FROM TestTable"

Kolejnym krokiem jest utworzenie obiektu SqlDataAdapter. Dla niego przypisujemy nasz obiekt z zapytaniem SQL.

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

Następnie tworzymy obiekt DataSet, do którego trafią nasze dane.

$DataSet = New-Object System.Data.DataSet

Ostatecznie wykorzystujemy metodę Fill obiektu SqlAdapter w celu wykonania zapytania SQL i załadowania danych do obiektu DataSet.

$SqlAdapter.Fill($DataSet)

Teraz możemy skopiować dane z atrybutu Tables obiektu $Dataset do dowolnej zmiennej lub po prostu wyświetlić na ekranie.

$Table = $Dataset.Tables[0]

Na zakończenie oczywiście zamykamy połaczenie do bazy danych.

$SqlConnection.Close()

Źródła:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx
http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

Reklamy

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s