Mysql DB Access Examples

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$dbconn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection

$dbconn.ConnectionString = "SERVER=01SQLPR001;DATABASE=Inventory;UID=INVUSER;PWD=YOURPASS;Allow Zero Datetime=True;"

$mycmd = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand

$mycmd.Connection = $dbconn

$mycmd.CommandText = "SELECT * FROM Hardware LIMIT 50"

$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($mycmd)

$dataSet = New-Object System.Data.DataSet

$recordCount = $dataAdapter.Fill($dataSet, "sample_data")

$dataSet.Tables["sample_data"] #dump it to output

$dbconn.Close

#Returned data access is handled as you would guess

$dataSet.Tables["sample_data"] | foreach { $mystr = $mystr + ";" + $_.Vendor + "," + $_.Model + "," + $_.Cpuinfo }

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$dbconn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection

$dbconn.ConnectionString = "SERVER=01SQLPR001;DATABASE=Inventory;UID=INVUSER;PWD=YOURPASS;Allow Zero Datetime=True;"

$mycmd = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand

$mycmd.Connection = $dbconn

$mycmd.CommandText = "INSERT INTO Hardware (vendor,model,cpuinfo) VALUES ('"+$vendor+"','"+$model+"','"+$cpuinfo+"')"