A InfoWiki wikiből

A lap 2008. december 15., 13:13-kori változata

SQL-adatbázisok kezelése Powershellben

Nagyobb feladatok során szükség lehet arra, hogy bizonyos adatokat SQL-adatbázisból olvassuk ki, vagy adatbázisba írjunk. Habár a Powershell beépített eszközökkel nem támogatja az SQL-kiszolgálók kezelését, a .Net-keretrendszer lehetőséget ad erre. Az adattábláknak olyan adatbázis-kezelő rendszerben kell lennie, amelynek van ODBC csatolója. Ilyen lehet például az MS Access. Kiszolgálónak pedig a Microsoft SQL Server a legjobb választás.

SQL adattábla adatainak beolvasása egy változóba

Hozzunk létre egy adatbázist és annak egy adattábláját, amit a Powershellben használhatunk:

A következő sorokkal egy SQL kifejezéssel adatokat tudunk átemelni egy Powershell változóba: $connectionString = "Provider=microsoft.jet.oledb.4.0; " + "Data Source=C:\pelda\emberek.mdb;" $sqlCommand = "select * from emberek" $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString $command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand ,$connection $connection.Open() $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command $dataset = New-Object System.Data.DataSet [void] $adapter.Fill($dataSet) $connection.Close() $records = $dataSet.Tables | Select-Object -Expand Rows [11] $records

Az első parancsban összeállítunk egy ún. connection string-et, ami most éppen Access adatbázishoz való csatlakozást rejt, de kis módosítással bármilyen ODBC adatforráshoz testre szabható. A kapcsolatsztringek használatára a www.connectionstrings.com oldalon számos példát találunk. A másodikban összerakjuk az SQL kifejezést, amelynek eredményét szeretném PowerShellből kezelni. A következő négyben felépítjük a kapcsolatot az adatforráshoz, és végrehajtjuk az SQL kifejezést, majd betöltjük a $dataset változóba az adatforrásból kapott adatokat. Ezután már nyugodttan bonthatjuk a kapcsolatot az adatbázissal. A $dataset még elég összetett típus, közvetlen adatkinyerésre nehézkesen használható, ezért a 10. parancs sorban sorokra bontja ezt az objektumot a Select-Object cmdlet segítségével. Látható, hogy a $records már táblázathoz nagyon hasonló formátumot ad vissza. Természetesen SQL adatokat módosítani is lehet, ehhez a fenti példát úgy kell megváltoztatni, hogy az SQL utasításban valamilyen adatmanipulációs parancsot kell kiadni. Ilyen SQL parancs lehet az INSERT, UPDATE, stb. Ezt az $sqlCommand nevű változóba rakjuk bele.

Ezek után hogyan tudjuk ezeket az adatokat kezelni? A $records tömb egyes elemei maguk az adatrekordok. Például a második sorra így lehet hivatkozni:

$records[1]

Ez nagyon hasonlít a .NET beli tömbhivatkozásra:) Az adatrekordok mezőjire már pedig a szokásos tulajdonság-hivatkozással lehet hivatkozni:

$records[2].id

SQL kiszolgálótól adat fogadása, példa

Íme egy példa, ami egy tábla tartalmát olvassa ki, SQL-kiszolgálón keresztül:

$conn = New-Object "System.Data.SqlClient.SqlConnection" "Server=DC;
Database=master; Integrated Security=True"
$conn.Open()
$command = New-Object "System.Data.SqlClient.SqlCommand" "SELECT * FROM [spt- 
monitor]2, conn
$reader = $command.ExecuteReader()
while ($reader.Read()) { $reader[0].ToString()+":"+$reader[1].ToString() }
$reader.Close()
$conn.Close()

a fenti példa bemutatása: SqlConnection típusú objektumot hozunk létre, melynek paramétereként megadjuk a kapcsolat létrehozásához szükséges kapcsolatsztringet (connection string). Az integrated Security=True paraméterrel azt adjuk meg, hogy az SQL-kiszolgálóhoz a jelenlegi felhasználó nevében szeretnénk csatlakozni, az integrált Windows-hitelesítés révén. Az Open() metódussal nyitjuk meg a kapcsolatot. Ezután hozzuk létre a SqlCommand típusú objektumot két paraméterrel: SQL-lekérdezéssel, és a kapcsolatot, amelyre szeretnénk a lekérdezést lefuttatni. Ezután attól függően, hogy lekérdezés milyen értékkel tér vissza hívjuk meg az SqlCommand objektum különböző metődusait (lásd. következő fejezet). A munka befejeztével mindig le kell zárni a kapcsolatot: $conn.Close()

SQL lekérdezés

Az alábbiakban a $command változó alatt az előző példa $command változóját értjük. Az SQL lekérdezés (pl. a SELECT) három értékkel térhet vissza:

Táblával: ez van az előző példában is. Ilyenkor az egyes mezőkre az $reader[0], $reader[1]... kifejezéssel tudunk hivatkozni. Ha ismerjük a mezők nevét (pl. mezo1, mezo2) akkor a $reader["mezo1"] kifejezés is a kívánt eredménnyel tér vissza. $command.ExecuteReader() metódus ekkor használható.

Egyetlen értékkel: ilyenkor használjuk a $command.ExecuteScalar() metódust. Ha a lekérdezés eredménye nem egy érték, hanem egy egész tábla, akkor ez a metódus csak az első rekord első mezőjével tér vissza.

Érték nélkül: ha az SQL-lekérdezés nem tér vissza értékkel (ilyen az INSERT, az UPDATE vagy a DELETE) a példa $command változójának ExecuteNonQuerry() metódusát futtatjuk. Ha értékkel tér vissza a lekérdezés, ez a metódus nem létezik, meg sem tudjuk hívni.