[PowerShell] 資料庫存取

在PowerShell 中, 可以透過叫用 .net framework library, 執行指令. 這裡利用 OLD DB 去對Orcale 進行存取如下:

# DB related settings.
$dbProvider="MSDAORA.1"
$dbHostName="ORACLE.DB"
$dbUserName="TEST"
$dbPassword="P@ssw0rd"
$dbConnectionString="Provider=$dbProvider;Data Source=$dbHostName;User ID=$dbUserName;Password=$dbPassword"

$dbConn = New-Object System.Data.OleDb.OleDbConnection($dbConnectionString)            
try
{
    $dbConn.Open()
    $searchSql="select * from staff"
    $searchCmd=$dbConn.CreateCommand()
    $searchCmd.CommandText=$searchSql
    $searchDataAdapter=New-Object system.Data.OleDb.OleDbDataAdapter($searchCmd)
    $ds=New-Object system.Data.DataSet  
    [void]$searchDataAdapter.Fill($ds)

    $firstName=$ds.Tables[0].Rows[0]['FirstName']
}
catch
{
    throw [Exception] "Execute error during SQL process."
}
finally
{
    if($dbConn -ne $null)
    {
        $dbConn.Close()
        $dbConn.Dispose()
    }
    if($searchCmd -ne $null) { $searchCmd.Dispose() }
}

因為工作需要, 故用到較舊的MS DAO, 但它不能在64bit平台中執行, 現在通常都會用Oracle OLAD 進行. 但這個不是重點. 透過.net library, 建立connection object, 之後建立command, 填入SQL statement 後執行. 完事後須要dispose以釋出系統資源.