PowershellでMySQLにアクセス

2013年12月16日

psmysql.sh
#[void][System.Reflection.Assembly]::LoadWithPartialName(“MySql.Data")
[void][system.Reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\MySQL Connector Net 6.5.4\Assemblies\v4.0\MySql.Data.dll")
$Server="127.0.0.1″
$Database="testps"
$user="powershell"
$Password="XXX
$charset="utf8″
$connectionString = “server=$Server;uid=$user;pwd=$Password;database=$Database;charset=$charset"
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
$sql= “SELECT * FROM product_details"
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$ds = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($ds)
Write-Host '総計：’ $recordCount '個レコード’

#収集データの表示
Write-Host '$ds.Tables’
$ds.Tables

#カラム名を検索
Write-Host 'カラム名下記：’
foreach($table in $ds.Tables)
{
foreach($column in $table.Columns)
{
$column.ColumnName
}
}

#すべてのデータをポーリング
Write-Host 'ポーリングデータ：’
foreach($table in $ds.Tables)
{
foreach($row in $table.Rows)
{
$curRow = "
foreach ($column in $table.Columns)
{
$curRow += [string]$row[$column] + ' '
}
$curRow
}
}

Write-Host '配列アクセス：’
#配列方法でカラム名を取得
$ds.Tables[0].columns[0].columnname
#配列を介してデータをインデックス
$ds.Tables[0].rows[0][1]