Powershell 查詢SQL數據庫資料

豆子對SQL Server自帶的sqlps模塊并不熟悉。昨天發現Don Jones提供的一個SQL模塊，理論上支持任何兼容ODBC Driver的數據庫（MySQL, MSSQL, Oracle 等等），原理就是調用.Net框架的一些底層函數。使用起來很方便順手，也不需要學習新的技能。

https://technet.microsoft.com/zh-cn/magazine/hh855069.aspx

下載以后，發現這個模塊其實就只包括了兩個函數，一個用來查詢，另外一個用來修改刪除。兩個函數都只有3個參數，分別是connectionString, 需要執行的SQL語句，以及是否為SQLServer。 第一個的格式根據數據庫有所不同，可以去http://www.connectionstrings.com/ 看對應的例子。 第二個就是真正執行的SQL語句，最后一個其實是個布爾值，如果輸入了這個參數就是真，沒輸入就是假。

function Get-DatabaseData {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName `
            System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName `
            System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($isSQLServer) {
        $adapter = New-Object -TypeName `
        System.Data.SqlClient.SqlDataAdapter $command
    } else {
        $adapter = New-Object -TypeName `
        System.Data.OleDb.OleDbDataAdapter $command
    }
    $dataset = New-Object -TypeName System.Data.DataSet
    $adapter.Fill($dataset)
    $dataset.Tables[0]
    $connection.close()
}


function Invoke-DatabaseQuery {
    [CmdletBinding(SupportsShouldProcess=$True,
                   ConfirmImpact='Low')]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName `
            System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName `
            System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($pscmdlet.shouldprocess($query)) {
        $connection.Open()
        $command.ExecuteNonQuery()
        $connection.close()
    }
}

下載腳本之后，重命名然后拷貝到$env:psmodulepath對應的路徑上就可以使用了

現在看看怎么使用。

例如：豆子想查詢一下某個數據庫的備份記錄。首先登陸MSSQL2012 Express的管理界面，利用T-SQL語句查詢看看應該的效果。

把T-SQL的語句拷貝下來，作為傳入的字符串參數，如下所示，調用第一個查詢的函數

Import-Module db
$ConnectionString = "server=sydav01\Sophos;database=sophos521;trusted_connection=True;"
$query="
SELECT TOP 100
s.database_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
where s.database_name='SOPHOS521'
"
Get-DatabaseData  -connectionString $ConnectionString -isSQLServer -query $query | ft

結果如下所示

成功獲取，可以看見和在圖形界面下看見的結果是一樣的。