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
}