データベースのキーからデータを取得するCmdlet作ったぞ！


- 商品の価格をExcelに出力
- 得意先の一覧をExcelに出力

とかよく言われます。

この価格表一覧のとか、僕の得意先だけとか言われるので、
キーで指定して取得してこなければなりません。

それにExcel持ってない場合もある。

キーの一覧とSQLを指定したらDBのデータを取得してくれるPowerShellのCmdlet(Function)が欲しいな…。

という理由で作ってみました。

## Get-OdbcDataFromPKs

必要となる変数

- SQL文 (パラメーター化クエリー)
- キーリスト
- OdbcのConnectionString

それらの変数を食べてプライマリキーリストから一つずつTableに格納。
結果をTableを出力。

CSVに吐くような作業はExport-Csvがやるので実装しない。

基本設計を元に色々思案していくと下記のようなCmdletが出来上がった。


```ps1:Get-OdbcDataFromPKs.ps1

function Get-OdbcDataFromPKs{

<#
.Synopsis
    パラメーター化クエリーと主キーリストから該当データを取得
    Get the Data form Parameternized Query and KeyList
.DESCRIPTION
    パラメーター化クエリーと主キーリストから該当データを取得する。
    ODBCのDSN名やConnectionStringを指定して他のDatabaseでも使えます。
    ODBC専用
.EXAMPLE
    Get-OdbcDataFromPKs -q "SELECT id,name,price FROM items WHERE id=?" -IDs 0,1,2 | Format-Table
    id name   price
    -- ----   -----
    0  apple   100
    1  orange   80
    2  grape   120

    # Get Data form IDs. IDsから主キーで取得
.EXAMPLE
    Get-OdbcDataFromPKs -q "SELECT * FROM items WHERE id=?" -IDs 0,1,2 | Export-Csv -Path items.csv -Encoding UTF8 -NoTypeInformation
    # 取得したデータをCsvファイルに出力

    PS1 > Get-Content .\item.csv
    "id","name","price"
    "0","apple","100"
    "1","orange","80"
    "2","grape","120"

    # check exported csv file. 出力したCsvファイルの確認
.EXAMPLE
    Get-OdbcDataFromPKs -connectionString "DSN=stationer" -q "SELECT * FROM items WHERE id=?" -IDs 0,1,2
    id name   price
    -- ----   -----
    0  pencil  100
    1  eraser   80
    2  pencase 120

    # 既定以外のデータベースでSELECT文を実行

.NOTES
    System.Data.Odbc      ODBC
    System.Data.OleDb     OleDb
    System.Data.SqlClient SQL Server
    使う.Netのオブジェクトによって、
    OleDb用やSqlClient用のCmdletも作れます。

    Author: Yusuke Arakawa
    Created: 2016/8/23
#>
    [CmdletBinding()]
    param(
        # ConnectionStrings 接続文字列
        # 既存DBのConnectionString
        [string]$connectionString="DSN=MyDB", 
        # Parameterized Queries　パラメータ化クエリ
        [string]$q,
        # KeyList 読込んだキー一覧
        [string[]]$IDs
    )
    Begin{
        $Con = New-Object System.Data.Odbc.OdbcConnection($connectionString)

        # コマンドオブジェクト作成
        $Cmd=New-Object System.Data.Odbc.OdbcCommand
        $Cmd.Connection=$Con
        $Cmd.CommandText=$q
    }
    Process{    
        # DataAdapter
        $da=New-Object System.Data.Odbc.OdbcDataAdapter
        $da.SelectCommand=$Cmd

        $Cmd.Connection=$Con.Open()

        # table初期化
        $table=$NULL

        ForEach($id in $IDs)
        {
            $param=New-Object System.Data.Odbc.OdbcParameter("@id",[System.Data.SqlDbType]::Int);
            $param.Value=[Int]$id
            # OdbcParameterは戻り値を返すが不要なのでnullに封じ込める
            $null=$Cmd.Parameters.Add($param)
        
            $Cmd.Connection=$Con
            # DataAdapter
            $da=New-Object System.Data.Odbc.OdbcDataAdapter
            $da.SelectCommand=$Cmd
        
            # DataSetに格納
            $DataSet=New-Object System.Data.DataSet
            $nRecs=$da.Fill($DataSet)
            $nRecs | Out-Null
            # tableに1行ずつ格納
            if($DataSet.Tables[0].Rows.Count -eq 0){
            #何もしない
            }else{
            $table += $DataSet.Tables[0]
            }
            $Cmd.Parameters.Clear()
        }
        
        # Data出力
        $table
    }
    End{
        # Connection Close
        $Con.Close()

    }
}

```


###使い方

Exampleに書かれているのは本当に簡単な使い方なんだけど、
ちょっと応用編を書いてみようと思う。

営業から「Excelで打ってきた商品cd一覧から商品名と価格を付けて欲しい。」
なんて言われたとしよう。

まずは、Excelをcsvにして保存する。
Get-Content(cat)で見てみよう。

```ps1:商品cd一覧
PS > cat ItemcdList.csv
"id"
"0"
"1"
"2"

```

商品cd一覧をIDsで読込めるようなリストに変換する。

```ps1:List化
PS > $itemids=Import-Csv -Path ItemcdList.csv
PS > $IDs=ForEach($id in $itemids){$id.id}
PS > cat $IDs
0
1
2
```

作成したキーリストとパラメーター化クエリーをGet-OdbcDataFromPKsに読ませる。

```ps1:Get-OdbcDataFromPKsを実行
# Get-OdbcDataFromPKs.ps1を読み込んでCmdletを実行できるようにする
PS > . .\Get-OdbcDataFormPKs.ps1
# Get-OdbcDataFromPKsを実行
PS > $table = Get-OdbcDataFromPKs -IDs $IDs -q "SELECT id,name,price FROM items WHERE id=?"
PS > $table
id name   price
-- ----   ------
0  apple  100
1  orange  80
2  grape  100

```

後はCSVに出力

```ps1:csv出力
PS > $table | Export-Csv -Path ItemList.csv -NoTypeInformation

# 中身を確認
PS > cat ItemList.csv
"id","name","price"
"0","apple","100"
"1","oragne","80"
"2","grape","100"

```

このItemList.csvを渡してあげればいい。
Excelのデータで欲しいのであればLibreOffice Calcで開いてxlsx形式等の好きな形式に保存することをお勧めする。
Excelで開かないのは悩ましい問題があってね。
[Csvを開くならExcelではなくCalcで](http://qiita.com/arachan@github/items/1f302618f0b426f4a60a)