Some Powershell functions for Preparing Mysql Statements and executing them.
Requires the MySQL .NET Connector
$Dbserv = "server"
$Dbname = "db"
$Dbuser = "user"
$Dbpass = "pass"
#MySQL Database connection
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") | Out-Null
$SQLConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$SQLConnection.ConnectionString = "server=$Dbserv;port=3306;uid=$Dbuser;pwd=$Dbpass;database=$Dbname;Integrated Security=False"
function MySQLprepare {
[OutputType([MySql.Data.MySqlClient.MySqlCommand])]
param (
# Query
[Parameter(
Mandatory = $true
)]
[String]
$query,
# Connection
[Parameter(
Mandatory = $true,
ValueFromPipeline = $true
)]
[MySql.Data.MySqlClient.MySqlConnection]
$connection
)
$command = New-Object MySql.Data.MySqlClient.MySqlCommand
$command.Connection = $connection
$command.CommandText = $query
(Select-String "@\w+" -input $query -AllMatches).Matches.Value | ForEach-Object {
if ($_) { $command.Parameters.AddWithValue($_, "") }
} | Out-Null
$command.Prepare()
return , $command
}
function MySQLexec {
[OutputType([Int])]
param (
# QueryParams
[Parameter(
Mandatory = $false
)]
[hashtable]
$queryParams = @{},
# Command
[Parameter(
Mandatory = $true,
ValueFromPipeline = $true
)]
[MySql.Data.MySqlClient.MySqlCommand]
$command
)
(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
} | Out-Null
$affectedRows = $command.ExecuteNonQuery()
return $affectedRows
}
function MySQLquery {
[OutputType([System.Data.DataTable])]
param (
# QueryParams
[Parameter(
Mandatory = $false
)]
[hashtable]
$queryParams = @{},
# Command
[Parameter(
Mandatory = $true,
ValueFromPipeline = $true
)]
[MySql.Data.MySqlClient.MySqlCommand]
$command
)
(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
} | Out-Null
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet, "data") | Out-Null
return , $dataSet.Tables["data"]
}
Examples
Preparing some statements
$insertVM = $SQLConnection | MySQLprepare -query "INSERT INTO virtual_machines ( id, name, description, status ) VALUES ( @id, @name, @description, 'new' );" $selectVMs = $SQLConnection | MySQLprepare -query "SELECT * FROM virtual_machines WHERE status LIKE @status;" $updateVM = $SQLConnection | MySQLprepare -query "UPDATE virtual_machines SET status = @status WHERE id = @id;"
Executing them
$insertVM | MySQLexec -queryParams @{"@id" = 1; "@name" = 'test'; "@description" = 'example vm'} | Out-Null
$dtVMs = $selectVMs | MySQLquery -queryParams @{"@status" = 'new'}
foreach ($vm in $dtVMs.Rows) {
Write-Host "Working on VM" $vm["id"] -ForegroundColor Cyan
$updateVM | MySQLexec -queryParams @{"@id" = $vm["id"]; "@status" = 'ready'} | Out-Null
}