PowershellでMySQLに接続する

2013年11月28日

#[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="test"
$user="admin"
$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 'Array方法でアクセス：’
#Array方法で列名を取得
$ds.Tables[0].columns[0].columnname
#Array方法でデータを索引する
$ds.Tables[0].rows[0][1]