MySQL Powershell Module

This is a simple MySQL Powershell module.  For more complex needs, please look at SQLPSX at CodePlex with also has support for MySQL.

NOTE: When copying this, the wiki is automatically sticking a space in the middle of the upper comment block marker. So you’ll need to replace all instances of “< #" with "<#". [powershell] [void][system.reflection.Assembly]::LoadFrom("$pwd\MySQL.Data.dll") ### Requires MySQL ADO.Net Connector dlls in $PWD ### Download from: http://dev.mysql.com/downloads/connector/net/ <# ### Example use of Module: Import-Module $pwd\MySQL.psm1 ### Example INSERT with ON DUPLICATE KEY UPDATE $insert = Prepare-MySQL -server 'server' -user 'user' -password 'password' -database 'database' $insert.CommandText = "INSERT INTO database.table (`Name`, `Id`, `Parent`, `ParentId`) VALUES ('" + $Name + "', '" + $Id + "', '" + $Parent + "', '" + $ParentId + "') ON DUPLICATE KEY UPDATE Name = VALUES(Name), Id = VALUES(Id), Parent = VALUES(Parent), ParentId = VALUES(ParentId), LastUpdate = NOW()"; $success = $insert.ExecuteNonQuery(); Close-MySQL -Query $insert ### Example SELECT $query = Prepare-MySQL -server 'server' -user 'user' -password 'password' -database 'database' $query.CommandText = "SELECT * FROM database.table WHERE column LIKE 'filter%'"; $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($query) $dataSet = New-Object System.Data.DataSet $recordCount = $dataAdapter.Fill($dataSet, "data") $dataSet.Tables["data"] | out-gridview Close-MySQL -Query $query #>

function Connect-MySQL {
< # .Synopsis Creates a MySQL connection. .Description Creates a MySQL connection. .Parameter Server MySQL Server you wish to connect to. .Parameter User MySQL user that can login the the MySQL server. .Parameter password Associated password for the MySQL user. .Parameter database MySQL database on the MySQL server. .Example PS> Connect-MySQL -database $database -server $server -user $user -password $password;
Creates a MySQL connection to Server $server and database $database using username $user and password $password.

.Inputs
None. Connect-MySQL does not support pipelining.
.Outputs
MySql.Data.MySqlClient.MySqlConnection object

.Link
Prepare-MySQL
Close-MySQL

.Notes
NAME: Connect-MySQL
AUTHOR: Andrew Stein/Billy Beaudoin
LASTEDIT: 08/01/2011
#>
[CmdletBinding()] PARAM (
[Parameter(Mandatory=$True, HelpMessage=”Server”)] [String] $server,
[Parameter(Mandatory=$True, HelpMessage=”User”)] [String] $user,
[Parameter(Mandatory=$True, HelpMessage=”Password”)] [String] $password,
[Parameter(Mandatory=$True, HelpMessage=”Database”)] [String] $database
)

PROCESS {
$mysqlInfo = “server=$server;database=$database;uid=$user;pwd=$password;”; #Write-Host $mysqlInfo
$db = New-Object MySql.Data.MySqlClient.MySqlConnection;
$db.ConnectionString = $mysqlInfo;
$db.Open();
return $db;
}
}

function Prepare-MySQL {
< # .Synopsis Creates a MySQL connection. .Description Creates a MySQL connection. .Parameter Connection Previous MySQL connection you wish to use. .Parameter Server MySQL Server you wish to connect to. .Parameter User MySQL user that can login the the MySQL server. .Parameter password Associated password for the MySQL user. .Parameter database MySQL database on the MySQL server. .Example PS> Prepare-MySQL -database $database -server $server -user $user -password $password;
Creates a MySQL connection to Server $server and database $database using username $user and password $password.

.Inputs
None. Prepare-MySQL does not support pipelining.
.Outputs
MySql.Data.MySqlClient.MySqlCommand object

.Link
Connect-MySQL
Close-MySQL

.Notes
NAME: Connect-MySQL
AUTHOR: Andrew Stein/Billy Beaudoin
LASTEDIT: 08/01/2011
#>
[CmdletBinding()] PARAM (
[Parameter(Mandatory=$false, HelpMessage=”Connection”)] [MySql.Data.MySqlClient.MySqlConnection] $connection = ”,
[Parameter(Mandatory=$True, HelpMessage=”Server”)] [String] $server,
[Parameter(Mandatory=$True, HelpMessage=”User”)] [String] $user,
[Parameter(Mandatory=$True, HelpMessage=”Password”)] [String] $password,
[Parameter(Mandatory=$True, HelpMessage=”Database”)] [String] $database
)

PROCESS {
$connection = Connect-MySQL -database $database -server $server -user $user -password $password;
$query = New-Object Mysql.data.mysqlclient.mysqlcommand;
$query.Connection = $connection;
return $query;
}
}

Function Close-MySQL {
< # .Synopsis Closes MySQL connection. .Description Closes MySQL connection. .Parameter Query Query created using the Prepare-MySQL function. .Parameter Connection Connection created using Connect-MySQL or Prepare-MySQL functions. .Example PS> Close-MySQL -Query $query
Closes MySQL connection associated with query $query.
.Example
PS> Close-MySQL -Connection $connection
Closes MySQL connection $connection.

.Inputs
None. Close-MySQL does not support pipelining.
.Outputs
None

.Link
Connect-MySQL
Prepare-MySQL

.Notes
NAME: Close-MySQL
AUTHOR: Andrew Stein/Billy Beaudoin
LASTEDIT: 08/01/2011
#>
[CmdletBinding()] PARAM (
[Parameter(Mandatory=$false, HelpMessage=”Query”)][MySql.Data.MySqlClient.MySqlCommand] $query = ”,
[Parameter(Mandatory=$false, HelpMessage=”Connection”)] [MySql.Data.MySqlClient.MySqlConnection] $connection = ”
)

PROCESS {
if ($connection -ne ”){
$connection.Close();
}

if ($query -ne ”){
$query.Connection.Close();
}
}
}

export-modulemember Connect-MySQL
export-modulemember Prepare-MySQL
export-modulemember Close-MySQL
[/powershell]