SQL Server、Access DB（ファイル）問い合わせ（SELECT文） Powershell スクリプトモジュール（コマンドレット）

スポンサーリンク

---

問い合わせ（SELECT文）を実行し、 SQL Server、Access DB（ファイル）の情報を表示する Powershell スクリプトモジュール（コマンドレット）

OS は Windows 10 、Powershell のバージョンは 5.1.17763.503（Get-Host コマンドにて確認）

［スクリプト（psm1ファイル）］

<#
［How to use］
1.Create installation folder
    %userprofile%/Documents/WindowsPowerShell/Modules/mycmdlet/
2.Copy this script
    copy to %userprofile%/Documents/WindowsPowerShell/Modules/mycmdlet/mycmdlet.psm1
#>
# 以下2行、使用環境のDB接続文字列を設定
$defaultConnectionString='Data Source=XXXXXXXXXX'
$defaultProvider='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXXXXXXXXX'
function sql {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR SQL SERVER DATABASE
.DESCRIPTION
    Output : Format-Table Cmdlet
.EXAMPLE
    sql 'SELECT * FROM TABLE_NAME'
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query
    )
    Execute-DatabaseQuery -query $query -connectionString $defaultConnectionString
}

function sqlcsv {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR SQL SERVER DATABASE
.DESCRIPTION
    Output : Export-Csv Cmdlet
.EXAMPLE
    sqlcsv 'SELECT * FROM TABLE_NAME' csv_file_name.csv
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query,
        # CSV FILE NAME
        [string]$csv
    )
    Execute-DatabaseQuery -csv $csv -query $query -connectionString $defaultConnectionString
}

function sqllist {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR SQL SERVER DATABASE
.DESCRIPTION
    Output : Format-List Cmdlet
.EXAMPLE
    sqllist 'SELECT * FROM TABLE_NAME'
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query
    )
    Execute-DatabaseQuery -list -query $query -connectionString $defaultConnectionString
}

function ole {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR ACCESS DATABASE
.DESCRIPTION
    Output : Format-Table Cmdlet
.EXAMPLE
    ole 'SELECT * FROM TABLE_NAME'
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query
    )
    Execute-DatabaseQuery -query $query -connectionString $defaultProvider
}

function olecsv {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR ACCESS DATABASE
.DESCRIPTION
    Output : Export-Csv Cmdlet
.EXAMPLE
    olecsv 'SELECT * FROM TABLE_NAME' csv_file_name.csv
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query,
        # CSV FILE NAME
        [string]$csv
    )
    Execute-DatabaseQuery -csv $csv -query $query -connectionString $defaultProvider
}

function olelist {
<#
.SYNOPSIS
    SQL QUERY EXECUTER FOR ACCESS DATABASE
.DESCRIPTION
    Output : Format-List Cmdlet
.EXAMPLE
    olelist 'SELECT * FROM TABLE_NAME'
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query
    )
    Execute-DatabaseQuery -list -query $query -connectionString $defaultProvider
}

function Execute-DatabaseQuery {
<#
.SYNOPSIS
    SQL QUERY EXECUTER
.DESCRIPTION
    SQL QUERY EXECUTER for SqlClient database(SQL Server) or OleDb database(Access)
.EXAMPLE
    [SqlClient database（SQL Server）]
    Execute-DatabaseQuery -query 'SELECT * FROM TBL_NAME' -connectionString 'Data Source=XXXXXX'
.EXAMPLE
    [OleDb database（Access）]
    Execute-DatabaseQuery -query 'SELECT * FROM TBL_NAME' -OleDbMode -connectionString 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXXXXX'
.EXAMPLE
    [Output CSV File]
    Execute-DatabaseQuery -csv filename.csv -query 'SELECT * FROM TBL_NAME' -connectionString 'Data Source=XXXXXX'
.EXAMPLE
    [Output Format-List Cmdlet]
    Execute-DatabaseQuery -list -query 'SELECT * FROM TBL_NAME' -connectionString 'Data Source=XXXXXX'
.NOTES
    Provider : Provider=Microsoft.JET.OLEDB.4.0; / Provider=Microsoft.ACE.OLEDB.12.0;
#>
    [CmdletBinding()]
    param (
        # SQL QUERY STRINGS
        [string]$query,
        # CONNECTION STRINGS
        [string]$connectionString,
        # OLE DB MODE
        [switch]$OleDbMode,
        # CSV FILE NAME
        [string]$csv,
        # Output Format-List
        [switch]$list
    )
    Begin {
        if ($OleDbMode) {
            $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
        } else{
            $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
        }
        $connection.ConnectionString = $connectionString
        $command = $connection.CreateCommand()
        $command.CommandText = $query
    }
    Process {
        if ($OleDbMode) {
            $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
        } else {
            $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
        }
        $dataset = New-Object -TypeName System.Data.DataSet
        $adapter.Fill($dataset) >$null | Out-Null
        if($csv) {
            $dataset.Tables[0] | Export-Csv -Path $csv -Encoding Default -NoTypeInformation -Delimiter ','
        } else {
            if($list) {
                $dataset.Tables[0] | Format-List
            } else {
                # 項目数、データ長に因っては切り捨てあり
                $dataset.Tables[0] | Format-Table -AutoSize -Wrap
            }
        }
    }
    End {
        $connection.Close()
    }
}

・コマンドレットの名前（sql、sqlcsv、sqllist、ole、olecsv、olelist）は、個人的に使用する場合以外は、命名規則（動詞－名詞）に合わせる
・エラー処理、他など諸々省略している版

---

スポンサーリンク

タグ：スクリプトモジュール Select SQL コマンドレット Access DB SQL Server Windows 10 powershell cmdlet