Access adatbázisok eléréséhez Rachard Siddawaytől olvashatunk sokat ( http://msmvps.com/blogs/richardsiddaway/ ). Az ő ötleteit fejlesztettem tovább és így egy olyan függvénykönyvtárat készítettem, amelyben az Access alapvető adatbázis-kezelési helyzeteire vannak függvények:
function New-AccessDatabase {
param (
[string] $path,
[switch] $close,
[switch] $passthru
)
if (!(Test-Path (split-path $path))){Throw "Invaild Folder"}
if (Test-Path $path){Throw "File Already Exists"}
$cat = New-Object -ComObject 'ADOX.Catalog'
[Void] $cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=$path")
$cat.ActiveConnection.Close()
if(!$close){
$connection = New-Object
System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$path")
$connection.Open()
if($passthru){$connection}
else{$global:AccessConnection = $connection}
}
}
Az első függvény az adatbázisok létrehozását végzi. Egy paramétert kell neki kötelezően megadni, ez pedig az adatbázisfájl elérési útja. A függvény elején van egy kis hibakezelés, ami a nem létező szülőkönyvtárat és a már létező fájlt szűri ki.
Ha használjuk a –Close kapcsolót a függvény hívásakor, akkor a létrehozott adatbázist be is zárja, ha nem, akkor egy élő kapcsolati objektumot ad vissza a függvény, méghozzá –Passthru kapcsoló nélkül a globális $AccessConnection változóba, egyébként meg csak „sima” visszatérési értékként.
Az én tapasztalatom az, hogy könnyű elfelejtkezni ennek a kapcsolati objektumnak a változóba történő mentéséről, így ez a globális változó a legkényelmesebb módja a kezelésének. Persze, ha párhuzamosan több adatbázis-kapcsolatot akarunk kezelni, akkor külön változókban érdemes ezeket tárolni.
A második függvény az adatbázis megnyitása:
function Open-AccessDatabase {
param (
[string]$path,
[switch] $passthru
)
if (!(Test-Path $path)){Throw "File Does Not Exist"}
$connection = New-Object System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$path")
$connection.Open()
if($passthru){$connection }
else {$global:AccessConnection = $connection}
}
Ez nem igényel túl sok magyarázatot, itt is a –Passthru kapcsoló segítségével tudjunk visszatérési értéket adni, egyébként a $AccessConnection változóba tölti a kapcsolati objektumot.
Ha szükséges, akkor az adatbázist be is zárhatjuk, azaz a kapcsolatot megszűntetjük:
function Close-AccessDatabase {
param (
[System.Data.OleDb.OleDbConnection]$connection
)
if($connection){$connection.Close()}
elseif($global:AccessConnection){$global:AccessConnection.Close()}
else{throw "Nothing
to close"}
}
Ha adunk neki át kapcsolati paramétert, akkor azt zárja be, egyébként próbálja a globális $AccessConnection tartalma alapján bezárni a kapcsolatot, különben hibát jelez.
Új adatbázisba érdemes táblát is létrehozni:
function New-AccessTable {
param (
[string] $name,
[hashtable] $columns,
[System.Data.OleDb.OleDbConnection] $connection = $global:AccessConnection
)
$ofs=","
$col = [string] ($columns.Keys | %{"$_ $($columns.$_)"})
$sql = "CREATE
TABLE $name ($col)"
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
$cmd.ExecuteNonQuery()
}
Ennek a függvénynek meg kell adni az új tábla nevét ($name) és az oszlopokat hashtábla formátumban és opcionálisan a kapcsolati objektumot. Ha ilyet nem adok, akkor a globális $AccessConnection változó tartalma alapján csatlakozik az adatbázishoz. A függvény törzsében felépítem a hashtábla tartalma alapján azt az SQL kifejezést, amely a táblát hozza létre.
Nézzünk ennek futtatására egy példát:
PS C:\> New-AccessTable -name AlapAdatok -columns @{
>> ID = "COUNTER CONSTRAINT PrimaryKey PRIMARY KEY";
>> Név = "Text (250)";
>> Ár = "CURRENCY";
>> Kell = "YESNO";
>> Bevitel = "DATE DEFAULT Now()";
>> Jegyzet = "MEMO"}
>>
0
Megjegyzés
Vigyázzunk, az általam létrehozott egyszerű függvényekkel nem lehet olyan oszlopokat és táblákat létrehozni, amelyek neve egy SQL-ben használatos lefoglalt kulcsszó. Így például „Memo” nevű oszlop létrehozásakor hibát kapunk.
Ez a kifejezés létrehoz egy táblát AlapAdatok névvel, lesz egy ID oszlop, ami az Access AutoNumber típusát veszi fel, és egyedi azonosítóként szolgál, lesz egy Név oszlop, ami szöveges és max. 250 karakter hosszúságú adatot fogad, lesz egy Ár oszlop, amely pénznem adatokat fogad, lesz egy bool típusú Kell oszlop, egy Bevitel oszlop, ami dátum típusú és alapértékként az aktuális dátumot veszi fel értékként és végül egy Jegyzet oszlop, ami hosszú szövegeket fogad.
Érdekes lehet annak kinyerése, hogy egy adatbázisban milyen táblák vannak:
function Get-AccessTable
{
param (
[string] $Filter = "*",
[System.Data.OleDb.OleDbConnection] $connection = $global:AccessConnection
)
$Connection.GetSchema("Tables") |
Where-Object {$_.table_type
-eq "table" -and
$_.table_name -like $filter}
|
Select-Object @{n="Name"; e={$_.table_name}}
}
A táblákat a kapcsolati objektum GetSchema metódusával tudjuk kinyerni. Ez nem csak a „felhasználói” táblákat listázza ki, hanem az adatbázis belső, rendszerleíró tábláit is, így ezeket kiszűröm a table_type tulajdonság vizsgálatával. A Get-AccessTable függvénynek meg lehet adni még egy további szűrőt, mellyel a táblanévre lehet szűrni, és megadható itt is, ha nem az alaphelyzet szerinti kapcsolathoz szeretnénk csatlakozni.
Töltsük fel a táblát adatokkal! Ehhez az Add-AccessTableRecord függvény ad segítséget:
function Add-AccessTableRecord {
param (
[string]$table,
[hashtable] $record,
[System.Data.OleDb.OleDbConnection]$connection = $global:AccessConnection
)
$ofs=",
"
$cols = "$($record.keys)"
$ofs = """,
"""
$vals = """"+[string] ($record.Keys |
foreach-object {$record.$_}) + """"
$sql = "INSERT
INTO $table ($cols) VALUES ($vals)"
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
$cmd.ExecuteNonQuery()
}
A filozófia hasonló az előző függvényéhez, azaz meg kell adni a tábla nevét és hashtábla formátumban a beillesztendő adatsort oszlopnév = érték módon. Nézzünk erre is példát:
PS C:\> Add-AccessTableRecord -table AlapAdatok -record @{név = "Bizgentyű"; Ár
= 1526; Kell = -1; Jegyzet = "Ez az első teszt adat"}
1
PS C:\> Add-AccessTableRecord -table AlapAdatok -record @{név = "Herkentyű"; Ár
= 9876; Kell = 0; Jegyzet = "Ez a második adat"; Bevitel = "2010.01.02"}
1
Természetesen érdemes az adatokat visszaolvasni tudni, ehhez a következő függvényt készítettem:
function Get-AccessTableRecord {
param (
[string[]] $columns ="*",
[string] $table,
[string] $where,
[System.Data.OleDb.OleDbConnection] $connection = $global:AccessConnection
)
$ofs = ",
"
$cols = [string] $columns
$wh = if($where){"
WHERE $where"} else {""}
$sql = "SELECT
$cols FROM $table" + $wh
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
$reader = $cmd.ExecuteReader()
$dt = New-Object System.Data.DataTable
$dt.Load($reader)
$dt
}
Ez nem egy általános lekérdező függvény, ez kifejezetten egy tábla adatsorait tudja lekérdezni. Az első paramétere a –Columns, ami egy sztringtömböt vár. Ez adja meg, hogy a tábla mely oszlopait szeretném látni, ha nem adok meg semmit, akkor mindegyiket. Paraméter még, hogy melyik tábla sorait kérdezzük le, és meg lehet még adni egy szűrő feltételt is az SQL WHERE klauzulájának megfelelően.
Nézzük ennek is a kimenetét, az első esetben oszlopok megadásával:
PS C:\> Get-AccessTableRecord -columns név, jegyzet -table AlapAdatok
név jegyzet
--- -------
Bizgentyű Ez az első teszt adat
Herkentyű Ez a második adat
A második esetben oszlopok nélkül. Itt látható, hogy a PowerShell 5 tulajdonság felett alaphelyzetben a listanézetet preferálja:
PS C:\> Get-AccessTableRecord -table AlapAdatok
ID : 2
Bevitel : 2010.02.22. 15:43:42
Kell : True
Jegyzet : Ez az első teszt adat
Név : Bizgentyű
Ár : 1526
ID : 3
Bevitel : 2010.01.02. 0:00:00
Kell : False
Jegyzet : Ez a második adat
Név : Herkentyű
Ár : 9876
Végül egy példa a szűrésre:
PS C:\> Get-AccessTableRecord -table AlapAdatok -where "név = 'Bizgentyű'"
ID : 2
Bevitel : 2010.02.22. 15:43:42
Kell : True
Jegyzet : Ez az első teszt adat
Név : Bizgentyű
Ár : 1526
Utolsóként nézzünk egy adatmódosító függvényt! Itt már a fejlett függvények lehetőségeit is kihasználtam, hogy lehessen –WhatIf kapcsolóval „óvatosan” is futtatni:
function Set-AccessTableData {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
[string]$table,
[string]$filter,
[hashtable] $value,
[System.Data.OleDb.OleDbConnection]$connection = $global:AccessConnection
)
$ofs = ",
"
$set = [string] ($value.keys | ForEach-Object {"$_ = '$($value.$_)'"})
$sql = "UPDATE
$table SET $set WHERE $filter"
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql"))
{$cmd.ExecuteNonQuery()}
}
A működés logikája a korábbiakhoz hasonló, a –Table paraméternek kell megadni, hogy melyik tábla adatát szeretnénk módosítani, a –Filter paraméterhez egy SQL szintaxisú „WHERE” klauzulát kell megadni. A –Value paraméterhez megint csak egy hashtábla formátumban kell megadni az oszlopnév – új érték párokat a következő példa alapján:
PS C:\> Set-AccessTableData -table AlapAdatok -filter 'név = "Bizgentyű"' –valu
e @{
>> név = "Kütyübigyó";
>> Jegyzet = "Módosítva";
>> Bevitel = "2010.02.01"
>> }
>>
1
Visszaolvasva az adatokat már az újakat látjuk:
140 . ábra A PowerShellel létrehozott Access tábla adatokkal
Vagy ugyanez a függvényeim segítségével:
PS C:\> Get-AccessTableRecord -table AlapAdatok | Format-Table -AutoSize
ID Bevitel Kell Jegyzet Név Ár
-- ------- ---- ------- --- --
2 2010.02.01. 0:00:00 True Módosítva Kütyübigyó 1526
3 2010.01.02. 0:00:00 False Ez a második adat Herkentyű 9876
Végül nézzük az adatrekordok eltávolítását! Szintén óvatos végrehajtással és egy adott táblára:
function Remove-AccessTableRecord {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
[string] $table,
[string] $filter,
[System.Data.OleDb.OleDbConnection] $connection = $global:AccessConnection
)
$sql = "DELETE
*
FROM $table
WHERE $filter"
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql"))
{$cmd.ExecuteNonQuery()}
}
Ennek használata:
PS C:\> Remove-AccessTableRecord -table alapadatok -filter "Id=2"
1
A kimenet azt adja meg, hogy hány rekordot sikerült törölnie a táblából.
Természetesen ezt még tovább lehet fejleszteni, de itt csak annyi volt a célom, hogy olyan adatkezelési igényekre adjak PowerShelles választ, amelyekhez nem kell mély SQL tudás.
Megjegyzés
A 64-bites Windows XP-n a fenti SQL-es PowerShell kifejezések hibát adnak a „normál” PowerShell ablakban futtatva. Ennek oka az, hogy a szükséges ODBC driver 64-bites változata nincsen benne az operációs rendszerben, csak a 32-bites változat, amit csak 32 bites alkalmazásból szólíthatunk meg. Ha ilyen hibát tapasztalunk, akkor futtassuk a fenti kódot 32-bites PowerShell ablakban, ami szintén elérhető a 64-bites gépen is, az már működni fog minden baj nélkül.