0

在 PowerShell 可以引用 .NET 程式庫，因此就算想要用它連資料庫跑 SQL 指令，也不是什麼難事。建立 SqlConnection、SqlCommand，ExecuteReader() 讀結果，ExecuteNonQuery() 跑更新，三兩下就能搞定。

但在 .NET 習慣了 Dapper 的簡潔輕巧，建 SqlParameter 傳參數讓人煩躁，但實在也不想再寫「串參數組 SQL 字串」的低級程式。。 (擔心小朋友有樣學樣搞出 SQL Injection。參考：你的網站正在裸奔嗎?)

想在 PowerShell 引用 Dapper，載入 DLL 不難，但 Dapper 跟 LINQ 重度依賴擴充方法，PowerShell 要引用也是可以，但寫法複雜到想吐，失去簡潔易用的原意。參考：PowerShell 裡可以寫 .NET LINQ 嗎？

為此，我試著在 PowerShell 實現類似 Dapper 用物件屬性傳參數的簡潔寫法，甚至能支援 WHERE Id IN @ids 傳參數陣列，感覺是不是很酷。

query "SELECT * FROM PSTEST WHERE Id IN `@ids" ([PSCustomObject]@{ ids = (1, 3) })

另外，關於連線字串，每次輸入帳號密碼不方便，但我也無法接受明碼儲存。PowerShell SecureString 限定主機及使用者使用，加密內容外流也難以解密，若沒有更安全的自訂連線字串儲存機制，不失為保存連線字串的簡易選擇。參考：淺談 PowerShell 中的密碼字串加密處理

綜合以上，這篇將展示如何使用 SecureString 儲存 SQL 連線字串，以及在 PowerShell 用類似 Dapper 的寫法查詢及更新資料庫。

Encrypt-ConnString.ps1 是一支小程式，輸入 Data Source、Initial Catalog、User Id、Password (或指定 Integrated Security=SSPI) 後加密儲存成檔案供重複使用。連線字串使用 SecureString / DPAPI 加密，在同一台主機使用相同帳號登入才能解開，方便將帳號密碼儲存起來重複使用。

$dataSource = Read-Host "Data Source"
$initCatalog = Read-Host "Initial Catalog"
$sspi = (Read-Host "Use Integrated Security? (Y/N)") -ieq 'y'
function secStrToClearText($secString) {
    $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secString)
    return [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
}
if (!$sspi) {
    $userId = Read-Host "User Id"
    $passwd = secStrToClearText (Read-Host "Password" -AsSecureString)
}
$path = Read-Host "Please input the file name of encrypted connection string"
$cnStr = "data source=$dataSource;initial catalog=$initCatalog;"
if ($sspi) {
    $cnStr += "integrated security=sspi"
}
else {
    $cnStr += "user id=$userId;password=$passwd"
}
ConvertTo-SecureString -String $cnStr -AsPlainText -Force | ConvertFrom-SecureString | Out-File $path

在 PowerShell 仿效 Dapper 以匿名型別物件傳 SQL 參數沒想像來得困難，以 PSCustomObject 定義匿名型別，跑迴圈逐一將屬性轉成 SqlParameter。若資料值為陣列，遇到 WHERE Id IN @ids 則跑迴圈加入 @id1、@id2、@id3 等 SqlParameter，再將 @ids 換成 (@id1, @id2, @id3)。前面提到的參數會用 AddParameterWithValue() 讓 ADO.NET 自動對映參數資料型別，若有特殊需求，亦可自建 SqlParameter 傳入。

另外，函式也能共用外部傳入的 SqlConnection，並可啟用 Transaction 交易，支援 Rollback。多說無益，直接看程式吧!

param (
    [Parameter(Mandatory = $true)][string]$encConnStrFile
)
$ErrorActionPreference = 'STOP'
try {
    $secString = Get-Content $encConnStrFile | ConvertTo-SecureString
    $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secString)
    $cnStr = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
}
catch {
    Write-Host "Failed to read connection string - $($error[0].Exception.Message)"
    return
}
function createConn() {
    return New-Object System.Data.SqlClient.SqlConnection -ArgumentList $cnStr
}
function addParams($cmd, [PSCustomObject]$params) {
    if ($params -ne $null) {
        $params.PSObject.Properties.Name | ForEach-Object {
            $paramName = $_
            if (-not ($paramName -match '^[0-9A-Za-z]+$')) {
                throw "Invalid parameter name: $paramName"
            }
            $paramVal = $params.$paramName
            if ($paramVal -is [System.Data.SqlClient.SqlParameter]) {
                $cmd.Parameters.Add($paramVal) | Out-Null
            }
            elseif ($paramVal -is [array]) {
                # support "WHERE Col IN @array"
                $idx = 1
                $paramNameList = ''
                $paramVal | ForEach-Object {
                    $cmd.Parameters.AddWithValue('@' + $paramName + $idx, $_) | Out-Null
                    $paramNameList += '@' + $paramName + $idx + ','
                    $idx++                    
                }
                $cmd.CommandText = $cmd.CommandText.Replace('@' + $paramName, "($($paramNameList.TrimEnd(',')))")
            }
            else {
                $cmd.Parameters.AddWithValue('@' + $paramName, $paramVal) | Out-Null
            }
        }
    }
}
function execute([string]$cmdText, [PSCustomObject]$params, $extConn, $trans) {
    if ($null -eq $extConn) {
        $cn = createConn
        $cn.Open()
    }
    else {
        $cn = $extConn
    }
    $cmd = $cn.CreateCommand()
    if ($null -ne $trans) { $cmd.Transaction = $trans }
    $cmd.CommandText = $cmdText
    addParams $cmd $params
    $cmd.ExecuteNonQuery() | Out-Null
    if ($null -eq $extConn) { $cn.Dispose() }
}
function query([string]$cmdText, [PSCustomObject]$params, $extConn, $trans) {
    if ($null -eq $extConn) {
        $cn = createConn
        $cn.Open()
    }
    else {
        $cn = $extConn
    }
    $cmd = $cn.CreateCommand()
    if ($null -ne $trans) { $cmd.Transaction = $trans }
    $cmd.CommandText = $cmdText
    addParams $cmd $params
    $dr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.load($dr)
    $dr.Dispose()
    if ($null -eq $extConn) { $cn.Dispose() }
    return $dt
}

# 範例一 無參數SQL指令

execute @"
IF OBJECT_ID('PSTEST','U') IS NULL 
BEGIN
    CREATE TABLE PSTEST (ID INTEGER, NAME NVARCHAR(32)) 
END
ELSE 
BEGIN
    TRUNCATE TABLE PSTEST
END
"@
execute "INSERT INTO PSTEST VALUES (1,'Jeffrey')"
execute "INSERT INTO PSTEST VALUES (2,'darkthread')"
execute "INSERT INTO PSTEST VALUES (3,'Ironman')"

# 結果以 Format-Table 方式呈現
Write-Host "簡單查詢" -ForegroundColor Yellow
query "SELECT * FROM PSTEST" | Format-Table

# 類似 LINQ Where()/Select() 概念
Write-Host "結合 Where-Object / ForEach-Object" -ForegroundColor Yellow
query 'SELECT * FROM PSTEST' | Where-Object { $_.Id -le 2 } | 
    ForEach-Object { "* $($_.Name)" }

# 範例二 使用自訂資料物件傳參數

Write-Host "使用自訂物件傳參數" -ForegroundColor Yellow
$users = @( @{ id = 4; name = 'Clone-1' }, @{ id = 5; name = 'Clone-2'} )
$users | ForEach-Object { 
    execute 'INSERT INTO PSTEST VALUES (@id, @name)' ([PSCustomObject]$_) 
}
query "SELECT * FROM PSTEST WHERE Id = `@id OR Name LIKE `@name + '%'" `
    ([PSCustomObject]@{ id   = 1; name = 'Clone' }) | Format-Table

Write-Host "WHERE Id IN @ids 陣列參數" -ForegroundColor Yellow
query "SELECT * FROM PSTEST WHERE Id IN `@ids" ([PSCustomObject]@{ ids = (1, 3) }) | Format-Table

# 範例三 指定特殊參數型別

Write-Host "指定 SqlParameter SqlDbType" -ForegroundColor Yellow
$guidParam = New-Object System.Data.SqlClient.SqlParameter -ArgumentList "@guid",[System.Data.SqlDbType]::UniqueIdentifier
$guidParam.Value = New-Guid
$params = [PSCustomObject]@{
    guid = $guidParam
    str = 'Param Type Test'
}
query 'SELECT @str AS S, @guid as G' $params | Format-Table

# 範例四 啟動 Transaction

Write-Host "啟動 Transaction" -ForegroundColor Yellow
Write-Host "測試前筆數： $((query 'SELECT COUNT(1) AS C FROM PSTEST').C)"
$cn = createConn
$cn.Open()
$trans = $cn.BeginTransaction()
execute 'DELETE FROM PSTEST' -extConn $cn -trans $trans
$cnt = (query 'SELECT COUNT(1) AS C FROM PSTEST' -extConn $cn -trans $trans).C
Write-Host "交易過程筆數：$cnt"
$trans.Rollback()
$trans.Dispose()
$cn.Dispose()
Write-Host "Rollback 後筆數：$((query 'SELECT COUNT(1) AS C FROM PSTEST').C)"

測試成功!

Example of how to save conneciton securely and run SQL command in Dapper-style with PowerShell.


Comments

Be the first to post a comment

Post a comment