Hi,

sometimes it makes sence to write Data in a “real” Databasesystem instead of writing to a text- or xml file.

MySQL offers an native Windows .NET Connector to its Database. You can get it here.

Some examples:-)

First of all load the assembly

PS D:\> [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\MySql.Data\v4.0_....

 

Build the connection string for your Database

[string]$sMySQLUserName = 'dbusername'
[string]$sMySQLPW = 'DatabasePa$$word'
[string]$sMySQLDB = 'db_test'
[string]$sMySQLHost = 'localhost'
[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB

Open a Database connection

$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)
$Error.Clear()
try
{
    $oConnection.Open()
}
catch
{
    write-warning ("Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: "+$Error[0].ToString())
}

The first query 🙂

# Get an instance of all objects need for a SELECT query. The Command object
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
# DataAdapter Object
$oMYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
# And the DataSet Object 
$oMYSQLDataSet = New-Object System.Data.DataSet	
# Assign the established MySQL connection
$oMYSQLCommand.Connection=$oConnection
# Define a SELECT query
$oMYSQLCommand.CommandText='SELECT username,login,telephone from  `db_test`.`users`'
$oMYSQLDataAdapter.SelectCommand=$oMYSQLCommand
# Execute the query
$iNumberOfDataSets=$oMYSQLDataAdapter.Fill($oMYSQLDataSet, "data")

Processing the returned Dataset(in this example just one table is return)

foreach($oDataSet in $oMYSQLDataSet.tables[0])
{
     write-host "User:" $oDataSet.username "Login:" $oDataSet.login "Telephonenumber:" $oDataSet.telephone 
}

Insert some data

# I defined a PSCustom Object for a User
$oNewUser=New-Object psobject -Property @{"username"="NewUser";"login"="login126";"telephone"="+491234 56789"}
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES("'+$oNewUser.username+'","'+$oNewUser.login+'","'+$oNewUser.telephone+'")'
$iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()

The same INSERT with a prepare statement for inserting a bulk of user.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)'
$oMYSQLCommand.Prepare()
$oMySqlCommand.Parameters.AddWithValue("@username", "")
$oMySqlCommand.Parameters.AddWithValue("@login", "")
$oMySqlCommand.Parameters.AddWithValue("@telephone", "")

And insert a bunch of User(all from an array of my custom PSObject)

[PSObject[]]$aNewUsers=@(
(New-Object psobject -Property @{"username"="NewUser1";"login"="login126";"telephone"="+491234 56789"}),
(New-Object psobject -Property @{"username"="NewUser2";"login"="login127";"telephone"="+491234 98765"}),
(New-Object psobject -Property @{"username"="NewUser3";"login"="login128";"telephone"="+491234 03456"})
)
foreach($oUser in $aNewUsers)
{
    $oMySqlCommand.Parameters["@username"].Value=$oUser.username
    $oMySqlCommand.Parameters["@login"].Value=$oUser.login
    $oMySqlCommand.Parameters["@telephone"].Value=$oUser.telephone
    $iRowsInsert=$oMySqlCommand.ExecuteNonQuery()
}

And as an trancation. Only one open transaction by connection is allowed. When you call BeginTransaction() again before an already started transaction is “Commited” or “Rolledback” you got an error “Nested transactions are not supported.”
Note: By default autocommit is enabled in MySQL Server. Means that MySQL automatically commits an transaction after an INSERT or UPDATE statement. You can disable autocommit by setting autocommit = 0.

$oTransAction=$oConnection.BeginTransaction()
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.Transaction=$oMYSQLTransaction
$oMYSQLCommand.CommandText = "SET autocommit = 0";
$iRows=$oMYSQLCommand.executeNonQuery();
# Do some Inserts or updates here and commit your changes
try
{
	$oMySQLTransaction.Commit()
}
catch
{
	write-warning ("ERROR occured while commit")
	$oMySQLTransaction.Rollback()
}
finally
{
	$oMySqlCommand.Connection.Close()
}
# Start a new transaction....

The UPDATE of a table is straightforward to the previous examples.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
UPDATE
 `db_test`.`users` `TUsr` SET `telephone`="+49 9876 54321"
 where `TUsr`.`username`=`NewUser2` and `TUsr`.`login`=`login127`'
$iRowsAffected=$oMYSQLCommand.executeNonQuery();

If you do not want to query if a Dataset exist and want to INSERT on not exists or UPDATE on exists use the following statement. It assumed that the columns `username` and `login` are unique together and are therefore the key of the table so only the `telephone` field has to updated.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)
ON DUPLICATE KEY UPDATE 
`telephone`=VALUES(`telephone`)'
$oMYSQLCommand.Prepare()
$oMySqlCommand.Parameters.AddWithValue("@username", "NewUser2")
$oMySqlCommand.Parameters.AddWithValue("@login", "login127")
$oMySqlCommand.Parameters.AddWithValue("@telephone", "+39 1234 56676")
$iRowsAffected=$oMYSQLCommand.executeNonQuery();

Calling a stored procedure. We assume that there is a stored procedure defined in the database that accepts the following parameter

PROCEDURE `getTelephoneNumber`(
IN USERNAME VARCHAR(45),
IN LOGIN VARCHAR(45),
OUT TELEPHONENUMBER VARCHAR(45)
)

and call the storedprocedure

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText="getTelephoneNumber"
$oMYSQLCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$oMYSQLCommand.Parameters.AddWithValue("USERNAME", "NewUser2")
$oMYSQLCommand.Parameters["USERNAME"].Direction = [System.Data.ParameterDirection]::Input
$oMYSQLCommand.Parameters.AddWithValue("LOGIN", "login127")
$oMYSQLCommand.Parameters["LOGIN"].Direction = [System.Data.ParameterDirection]::Input
$oMYSQLCommand.Parameters.AddWithValue("TELEPHONENUMBER", [MySql.Data.MySqlClient.MySqlDbType].GetMember("String").GetRawConstantValue())
$oMYSQLCommand.Parameters["TELEPHONENUMBER"].Direction = [System.Data.ParameterDirection]::Output
$iReturn=$oMYSQLCommand.ExecuteNonQuery()
write-host ("Telephonenumber: "+$oMYSQLCommand.Parameters["TELEPHONENUMBER"].Value);

I have to use [MySql.Data.MySqlClient.MySqlDbType].GetMember("String").GetRawConstantValue() for the constant of an MySQL String.

[MySql.Data.MySqlClient.MySqlDbType]::String did not work due to an non complient definition of DateTime DateTime in [MySql.Data.MySqlClient.MySqlDbType] 🙁

Error: The field or property: “Datetime” for type: “MySql.Data.MySqlClient.MySqlDbType” differs only in letter casing from the field or property: “DateTime”. The type must be Common Language Specification (CLS) compliant.
However, you can also simply write 🙂

$oMYSQLCommand.Parameters.AddWithValue("TELEPHONENUMBER", 254)

to be continued……

Michael