Run-MySQLQuery

I’ve found this great function written by Thomas Maurer on his blog. It’s really usefull to automate MySQL queries.

 

Function Run-MySQLQuery {
<#
.SYNOPSIS
run-MySQLQuery
.DESCRIPTION
By default, this script will:
- Will open a MySQL Connection
- Will Send a Command to a MySQL Server
- Will close the MySQL Connection
This function uses the MySQL .NET Connector or MySQL.Data.dll file
.PARAMETER ConnectionString
Adds the MySQL Connection String for the specific MySQL Server
.PARAMETER Query
The MySQL Query which should be send to the MySQL Server
.EXAMPLE
C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;Pwd=p@ssword;database=project;" -Query "SELECT * FROM firsttest"
Description
-----------
This command run the MySQL Query "SELECT * FROM firsttest"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword and selects the database project
.EXAMPLE
C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;Pwd=p@ssword;database=project;" -Query "UPDATE firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
Description
-----------
This command run the MySQL Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword
.EXAMPLE
C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;Pwd=p@ssword;" -Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
Description
-----------
This command run the MySQL Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword and selects the database project
#>
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$query,
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$connectionString
)
Begin {
Write-Verbose "Starting Begin Section"
}
Process {
Write-Verbose "Starting Process Section"
try {
# load MySQL driver and create connection
Write-Verbose "Create Database Connection"
# You could also could use a direct Link to the DLL File
# $mySQLDataDLL = "C:\scripts\mysql\MySQL.Data.dll"
# [void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $ConnectionString
Write-Verbose "Open Database Connection"
$connection.Open()

# Run MySQL Querys
Write-Verbose "Run MySQL Querys"
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connection)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($dataSet, "data")
$dataSet.Tables["data"] | Format-Table
}
catch {
Write-Host "Could not run MySQL Query" $Error[0]
}
Finally {
Write-Verbose "Close Connection"
$connection.Close()
}
}
End {
Write-Verbose "Starting End Section"
}
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top