PowerShell で SQL Server にデータ登録のメモ

ブログからの転載

PowerShell を利用して SQL Server にデータを入れ入れしてたメモ

環境

• Windows 10 Pro
• PowerShell 5.1
• SQL Server 2017

事前準備

テーブルを作成する

-- DUMMY_TABLE が既にあったら DROP
IF object_id('DUMMY_TABLE') IS NOT NULL
DROP TABLE DUMMY_TABLE

-- ID 列は自動採番
CREATE TABLE DUMMY_TABLE(
    ID             INT   NOT NULL PRIMARY KEY IDENTITY,
    INT_DATA       INT,
    NVARCHAR_DATA  NVARCHAR(255),
    DATETIME_DATA  DATETIME,
    VARBINARY_DATA VARBINARY(max)
);
GO

INT 型、NVARCHAR 型、DATETIME 型、VARBINARY 型の Insert 例

# SQL Server への接続文字列
$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder

# データソース
$ConnectionString['Data Source']         = "ExampleServer\ExampleInstance"
# データベース名
$ConnectionString['Initial Catalog']     = "ExampleDB"
# ユーザー名
$ConnectionString['User ID']             = "ExampleUser"
# パスワード
$ConnectionString['Password']            = "ExamplePassword"
# Windows 認証(SqlClient のときは true)
$ConnectionString['Integrated Security'] = $true

# SQL Server へのコネクションオブジェクトを生成
$con = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

$sql = @"
INSERT INTO DUMMY_TABLE(
    INT_DATA,
    NVARCHAR_DATA,
    DATETIME_DATA,
    VARBINARY_DATA
) VALUES (
    @INT_DATA,
    @NVARCHAR_DATA,
    @DATETIME_DATA,
    @VARBINARY_DATA
);
"@

try {
    $con.Open()
    $transaction = $con.BeginTransaction()

    $cmd = $con.CreateCommand()
    $cmd.Connection = $con
    $cmd.Transaction = $transaction

    $cmd.CommandText = $sql

    # INT 型
    $sqlparam = New-Object Data.SqlClient.SqlParameter("@INT_DATA", [Data.SQLDBType]::INT, -1)
    $cmd.Parameters.Add($sqlparam).Value = 1

    # NVARCHAR 型
    $sqlparam = New-Object Data.SqlClient.SqlParameter("@NVARCHAR_DATA", [Data.SQLDBType]::NVARCHAR, -1)
    $cmd.Parameters.Add($sqlparam).Value = "日本語の入力"

    # DATETIME 型
    $sqlparam = New-Object Data.SqlClient.SqlParameter("@DATETIME_DATA", [Data.SQLDBType]::DATETIME, -1)
    $cmd.Parameters.Add($sqlparam).Value = [Datetime]("2018/06/02 00:00:00")

    # VARBINARY 型
    $sqlparam = New-Object Data.SqlClient.SqlParameter("@VARBINARY_DATA", [Data.SQLDBType]::VARBINARY, -1)
    $cmd.Parameters.Add($sqlparam).Value = [System.IO.File]::ReadAllBytes("C:\hoge.png")

    # パラメータークエリー
    $cmd.Prepare()

    # 実行
    [void]$cmd.ExecuteNonQuery()

    # コミット
    $transaction.Commit()

} catch {
    Write-Error $_.Exception.ToString()
} finally {
    $con.Close()
    $con.Dispose()
}

自動採番される ID を取得

パラメータークエリー前あたりを下記に書き換える

    # Insert 文の後に追記
    $sql += 'SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];';

    # パラメータークエリー
    $cmd.Prepare()

    # Insert 後に ID 取得
    $Id = $cmd.ExecuteScalar();

空文字や NULL のセット

    # 空文字をセット
    $cmd.Parameters.Add($sqlparam).Value ＝[string]::Empty

    # NULL をセット
    $cmd.Parameters.Add($sqlparam).Value = [System.DBNull]::Value

INT 型や DATETIME 型の最大値や最小値をセット

    # INT 型の最小値をセット
    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlInt16]::MinValue.Value

    # INT 型の最大値をセット
    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlInt16]::MaxValue.Value

    # DATETIME 型の最小値をセット
    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlDateTime]::MinValue.Value

    # DATETIME 型の最大値をセット
    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlDateTime]::MaxValue.Value

SqlParameter をもう少し直感的に使いたい

v5 以降ならクラスを使ったほうがよい

# クラスの定義
class SqlParamGenarator
{
    [System.Data.Common.DbParameter]
    Int([string]$Name) { return $this._Genarator($Name, 'Int') }

    [System.Data.Common.DbParameter]
    NVarchar([string]$Name) { return $this._Genarator($Name, 'NVarchar') }

    [System.Data.Common.DbParameter]
    DateTime([string]$Name) { return $this._Genarator($Name, 'DateTime') }

    [System.Data.Common.DbParameter]
    VarBinary([string]$Name) { return $this._Genarator($Name, 'VarBinary') }

    [System.Data.Common.DbParameter]
    _Genarator($Name, $Type)
    {
        return New-Object Data.SqlClient.SqlParameter("@$Name",  [Data.SQLDBType]::$Type, -1)
    }
}

    ## 使い方
    $GenSqlParam = [SqlParamGenarator]::new()
    # INT 型
    $cmd.Parameters.Add($GenSqlParam.Int('INT_DATA') ).Value = 2

    # NVARCHAR 型
    $cmd.Parameters.Add($GenSqlParam.NVarchar('NVARCHAR_DATA')).Value = "日本語の入力"

    # DATETIME 型
    $cmd.Parameters.Add($GenSqlParam.DateTime('DATETIME_DATA')).Value = "2018/06/03 00:00:00"

    # VARBINARY 型
    $cmd.Parameters.Add($GenSqlParam.VarBinary('VARBINARY_DATA')).Value = [System.IO.File]::ReadAllBytes("C:\hoge.png")

v4 以下なら、スクリプトブロックで頑張る

## 定義
$GenSqlParam = {
    $Genarator = @{}
    $Genarator.Set_Item('Int',      ({ param($Name)(New-Object Data.SqlClient.SqlParameter("@$Name",  [Data.SQLDBType]::Int, -1)) }))
    $Genarator.Set_Item('NVarchar', ({ param($Name)(New-Object Data.SqlClient.SqlParameter("@$Name",  [Data.SQLDBType]::NVarchar, -1)) }))
    $Genarator.Set_Item('DateTime', ({ param($Name)(New-Object Data.SqlClient.SqlParameter("@$Name",  [Data.SQLDBType]::DateTime, -1)) }))
    $Genarator.Set_Item('VarBinary',({ param($Name)(New-Object Data.SqlClient.SqlParameter("@$Name",  [Data.SQLDBType]::VarBinary, -1)) }))
    # 動的な型変換対策
    return ,$Genarator
}.Invoke()

    ## 使い方
    # INT 型
    $cmd.Parameters.Add( $GenSqlParam.Int.Invoke('INT_DATA')[0] ).Value = 2

    # NVARCHAR 型
    $cmd.Parameters.Add( $GenSqlParam.NVarchar.Invoke('NVARCHAR_DATA')[0] ).Value = "日本語の入力"

    # DATETIME 型
    $cmd.Parameters.Add( $GenSqlParam.DateTime.Invoke('DATETIME_DATA')[0] ).Value = "2018/06/03 00:00:00"

    # VARBINARY 型
    $cmd.Parameters.Add( $GenSqlParam.VarBinary.Invoke('VARBINARY_DATA')[0] ).Value = [System.IO.File]::ReadAllBytes("C:\hoge.png")