MySqlHelpers.ps1

using namespace System.Reflection
using namespace System.Collections.Generic
using namespace System.Data
using namespace MySql.Data.MySqlClient

if(-not [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")) {
    Write-Output "the MySql connector should be installed to be able to use the cmdlet"
}

if(-not [System.Reflection.Assembly]::LoadWithPartialName("System.Collections")){
    Write-Output "failed loading System.Collection assembly"
}

if(-not [System.Reflection.Assembly]::LoadWithPartialName("System.Linq")){
    Write-Output "failed loading System.Linq assembly"
}

if(-not [System.Reflection.Assembly]::LoadWithPartialName("System.Data")){
    Write-Output "failed loading System.Collection assembly"
}

function Get-MySqlRows{
    Param(
        [string]$ConnectionString,
        [string]$TableName,
        [string]$Where,
        [string[]]$Columns,
        [string]$IdColumn
    )
    $connection = [MySqlConnection]::new($ConnectionString)

    $selectCommand = $connection.CreateCommand()
    $selectCommand.CommandText = Get-SelectSql -TableName $TableName -Columns $Columns -IdColumn $IdColumn
    
    $parameter = [MySqlParameter]::new()
    $parameter.ParameterName = "@$IdColumn"
    $selectCommand.Parameters.Add($parameter)

    # $selectWhere = $selectCommand.CommandText
    # $selectAll = $selectCommand.CommandText.Replace("WHERE $IdColumn=@$IdColumn", "")
    # $selectAll
    # $selectCommand.CommandText = $selectAll
    
    $dataAdapter = Get-MySqlDataAdapter -SelectCommand $selectCommand
    # $dataAdapter.
    $commandBuilder = Get-MySqlCommandBuilder -DataAdapter $dataAdapter
    $dataSet = Get-DataSet -DataAdapter $dataAdapter -TableName $TableName

    $dataSet
}

function Get-SelectSql {
    Param(
        [Parameter()]
        [string]$TableName,
        [Parameter()]
        [string[]]$Columns,
        [Parameter()]
        [string]$IdColumn
    )

    $selectSql = "SELECT "
    $Columns | ForEach-Object -Process { $selectSql += "$_," } 
    $selectSql = $($selectSql.Remove($selectSql.LastIndexOf(',')))
    $selectSql +=  " FROM $TableName" # WHERE $IdColumn=@$IdColumn"
    return $selectSql  
}

function Get-MySqlDataAdapter {
    Param(
        [Parameter()]
        [IDbCommand]$SelectCommand,
        [Parameter()]
        [string]$ConnectionString,
        [Parameter()]
        [string]$SelectSql)
    if((-not [string]::IsNullOrEmpty($ConnectionString)) `
        -and (-not [string]::IsNullOrEmpty($SelectSql))) {
        return [MySqlDataAdapter]::new($SelectSql, $ConnectionString)
    } elseif ($null -ne $SelectCommand) {
        return [MySqlDataAdapter]::new($SelectCommand)        
    }
}

function Get-MySqlCommandBuilder {
    Param(
        [Parameter()]
        [MySqlDataAdapter]$DataAdapter)
    return [MySqlCommandBuilder]::new($DataAdapter)
}

function Get-DataTable {
    Param([string]$TableName)
    return [DataTable]::new($TableName)
}
function Get-DataSet {
    Param(
        [IDbDataAdapter] $DataAdapter,
        [string] $TableName,
        [string[]] $Columns,
        [string] $ConnectionString
    )

    if(-not([string]::IsNullOrEmpty($TableName)) `
        -and ($Columns.Count -gt 0) `
        -and -not ([string]::IsNullOrEmpty($ConnectionString))) {
        $connection = Get-Connection -ConnectionString $ConnectionString
        $selectCommand = Get-SelectCommand -TableName $TableName -Columns $Columns -Connection $connection
        $DataAdapter = Get-MySqlDataAdapter -SelectCommand $selectCommand
    }

    if($DataAdapter.SelectCommand.Connection.State -eq [ConnectionState]::Closed) {
        $DataAdapter.SelectCommand.Connection.Open()
    }
    
    $dataSet = [System.Data.Dataset]::new()
    $dataTable = [DataTable]::new($TableName)
    
    $dataSet.Tables.Add($dataTable)

    $DataAdapter.FillSchema($dataTable, [SchemaType]::Mapped)
    $DataAdapter.Fill($dataTable)
    
    $DataAdapter.SelectCommand.Connection.Close()

    return $dataSet
}

function Get-Connection {
    Param([string]$ConnectionString)
    return [MySqlConnection]::new($ConnectionString)
}

function Get-SelectCommand {
    Param(
        [string]$TableName,
        [string[]]$Columns,
        [IDbConnection]$Connection
    )
    $selectSql = Get-SelectSql -TableName $TableName -Columns $Columns
    $selectCommand = $Connection.CreateCommand()
    $selectCommand.CommandText = $selectSql  
    
    return $selectCommand
}

function Get-InsertCommand {
    Param(
        [string]$TableName,
        [string[]]$Columns,
        [string]$IdColumn,
        [IDbCommand]$SelectCommand
    )

    $dataAdapter = Get-MySqlDataAdapter -SelectCommand $SelectCommand
    $commandBuilder = Get-MySqlCommandBuilder -DataAdapter $dataAdapter

    return $commandBuilder.GetInsertCommand()
}

function Get-DeleteCommand {
    Param(
        [string]$TableName,
        [string[]]$Columns,
        [string]$IdColumn,
        [IDbCommand]$SelectCommand
    )

    $dataAdapter = Get-MySqlDataAdapter -SelectCommand $SelectCommand
    $commandBuilder = Get-MySqlCommandBuilder -DataAdapter $dataAdapter

    return $commandBuilder.GetDeleteCommand()
}

function Get-UpdateCommand {
    Param(
        [string]$TableName,
        [string[]]$Columns,
        [string]$IdColumn,
        [IDbCommand]$SelectCommand
    )

    $dataAdapter = Get-MySqlDataAdapter -SelectCommand $SelectCommand
    $commandBuilder = Get-MySqlCommandBuilder -DataAdapter $dataAdapter

    return $commandBuilder.GetUpdateCommand()
}

function Get-MySqlCommands {
    Param(
        [string]$TableName,
        [string[]]$Columns,
        [string]$ConnectionString
    )
    $connection = Get-Connection -ConnectionString $connectionString
    $selectCommand = Get-SelectCommand -TableName $TableName -Columns $columns -Connection $connection
    $insertCommand = Get-InsertCommand -SelectCommand $selectCommand
    $updateCommand = Get-UpdateCommand -SelectCommand $selectCommand
    $deleteCommand = Get-DeleteCommand -SelectCommand $selectCommand
    
    return @{Select=$selectCommand; Insert=$insertCommand; Update=$updateCommand; Delete=$deleteCommand}
}


$connectionString = "Server=localhost;Port=3306;Database=ceres;Uid=root;Pwd=mrullerp!0;"
$TableName = 'menus'

$columns = @('id_menu', 'name', 'active')
$values = @((114, "SPL1", 1),(115, "SPL2", 1),(116, "SPL3", 1))
$Rows = @(@{id_menu=121; name="CTRL1"; active=0}, @{id_menu=122; name="CTRL2"; active=0}, @{id_menu=123; name="CTRL3"; active=0})

$currentValues = Get-MySqlRows -TableName $TableName -Columns $columns -IdColumn 'id_menu' -ConnectionString $connectionString


$commands = Get-MySqlCommands -TableName $TableName -Columns $columns -ConnectionString $connectionString

$dataset = Get-DataSet -TableName $TableName -Columns $columns -ConnectionString $connectionString
$dataset.Tables[0].Rows.Add(17, 'BLED', 0)
$dataset.Tables[0].Rows.Add(18, 'BLED1', 0)
$dataAdapter.Update($dataset.Tables[0])
$dataAdapter.Fill($dataset.Tables[0])

MySQLInsert.ps1

using namespace System.Collections.Generic
using namespace System.Linq
using namespace System.Data
using namespace System.Reflection
using namespace System.Management.Automation
using namespace MySql.Data.MySqlClient

if(-not [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")) {
    Write-Output "the MySql connector should be installed to be able to use the cmdlet"
}

<#
 .Synopsis
  Make an insert in a MySql database table.

 .Description
  the cmdlet gives the possibility to insert values in the specified columns of a MySql Table.

 .Parameter ConnectionString
  The connection string used to access the database.

 .Parameter TableName
  The destination table.

 .Parameter Columns
  The columns array to insert values into.

 .Parameter Values
  The values array to insert respectively to columns.

 .Parameter Transaction
  The transaction to use in case of the insert operation is part of transaction.

 .Example
   # Insert using a connection string.
   MySqlInsert -ConnectionString $connectionString -Columns @('column1', 'column2', ...) -Values @('value1', 'value2', ...)

 .Example
   # Insert using transaction.
   $transaction = [MySql.Data.MySqlClient.Transaction]::new()
   MySqlInsert -Transaction $transaction -Columns @('column1', 'column2', ...) -Values @('value1', 'value2', ...)

#>

function MySQL-Insert{
    Param(
        [Parameter(Mandatory=$true)][string] $TableName,
        [Parameter(Mandatory=$true)][string[]] $Columns,
        [Parameter(Mandatory=$true)][object[][]] $Values,
        [string] $ConnectionString,
        [IDbTransaction] $Transaction,
        [Parameter(ValueFromPipeline = $true)][MySqlConnection] $connection
    )

    [IDbConnection] $connection;

    <###
    # handling the case of using transaction
    # if the transaction parameter isn't null 
    # we use the transaction connection instead
    ###>
    
    [IDbCommand] $insertCommand

    if($Transaction -ne $null) {
        $connection = $Transaction.Connection
        $insertCommand = $connection.CreateCommand();
        $insertCommand.Transaction = $Transaction
    }
    else {
        $connection = [MySql.Data.MySqlClient.MySqlConnection]::new($ConnectionString)        
        $insertCommand = $connection.CreateCommand();
    }    

    $insertCommand.CommandType = [CommandType]::Text
    $insertCommand.CommandText = Get-InsertSQL -TableName $TableName -Columns $Columns

    $parameters = Get-Parameters -Columns $Columns -Values $Values[0]
    Apply-Parameters -Command $insertCommand -Parameters $parameters
    
    $insertCommand.Parameters
    try{    
        OpenConnection -Connection $connection
        return InsertRows -Command $insertCommand -Values $Values
    } 
    catch [ConstraintException] {
        Write-Output $PSItem.Exception.Message
    }
    catch {
        $PSItem
        #Write-Host $_.ErrorDetails
        #Write-Host $_.ScriptStackTrace
        
        if($Transaction -ne $null)
        {
            $Transaction.Rollback()
        }
    }
    finally {
        $insertCommand.Dispose()

        if($Transaction -eq $null) { 
            $Connection.Close()
            $connection.Dispose() 
        }
    }   
}

<###
    # helpers
    #
    ###>

function Get-InsertSQL {
    Param(
        [string]$TableName,
        [string[]]$Columns
        )

        $columns_aggregator = [Func[string, string, string]] { 
                param($c1, $c2) 
                if([String]::IsNullOrEmpty($c1)) {
                    return "$($c2)"
                }
                [string]::Concat("$($c1), ", "$($c2)") 
        }
    
        $parameters_aggregator = [Func[string, string, string]] { 
            param($c1, $c2) 
                if([String]::IsNullOrEmpty($c1)) {
                    return "@$($c2)"
                }
                [string]::Concat("$($c1), ", "@$($c2)") 
        }

        $columnsList = [System.Collections.Generic.List[string]]::new()
        $columnsList.AddRange($Columns)

        $columnsAggergate = [Enumerable]::Aggregate($columnsList, "", $columns_aggregator)
        $parametersAggergate = [Enumerable]::Aggregate($columnsList, "", $parameters_aggregator)

        return "INSERT INTO $($TableName) ($($columnsAggergate)) Values ($($parametersAggergate))"    
    }

<###
    # 
    # 
    ###>

function OpenConnection
{ Param([IDbConnection]$Connection)
        try 
        {
            if($connection.State -ne [ConnectionState]::Open) 
            { 
                [void]$connection.Open() 
            }        
        }
        catch 
        {
            Write-Output "Database exception: $($PSItem.Exception.Message)"            
        }
    }

<###
    #
    #
    ###>
    
function InsertRows 
{
        Param(
            [IDbCommand] $Command,
            [object[][]] $Values
        )
        try 
        {
            $rows = 0

            if($Values.Count -gt 1) 
            {
                foreach($value in $Values)
                {
                    $i = 0;
                    foreach($parameter in $Command.Parameters)
                    {
                        $parameter.Value = $value[$i]
                        $i++
                    }
                    $rows += $Command.ExecuteNonQuery()
                }
            } else {
                $rows = $Command.ExecuteNonQuery()
            }        
            return $rows 
        } 
        catch [MySqlException]
        {
            Write-Output "Database insert Exception: $($PSItem.Exception.Message)"
        }
        catch 
        {
            Write-Output $PSItem.Exception.Message 
        }
    }
    
<###
    #
    #
    ###>

function Get-Parameters
{
        Param(
            [string[]]$Columns,
            [object[]]$Values
        )

        $parameters = [List[IDbDataParameter]]::new()
        $ix = 0
        foreach($current in $Values){
            $parameter = [MySql.Data.MySqlClient.MySqlParameter]::new($Columns[$ix], $current)
            $parameters.Add($parameter)
            $ix += 1
        }
        return $parameters;
    }

<###
    #
    #
    ###>

function Apply-Parameters 
{
    Param(
        [Parameter(ValueFromPipeline=$true)][MySqlParameter[]]$Parameters,
        [MySqlCommand]$Command
    )
    foreach($parameter in $Parameters) {       
        $Command.Parameters.AddRange($Parameters) 
    }
}
<###
    # end helpers
    #
    ###>

$connectionString = "Server=127.0.0.1;Port=3306;Database=human_resources;Uid=root;Pwd=P@ssw0rd"
$tableName = 'employee'
$columns = @('id', 'name', 'status')
$values = @((1, "John", 1),(2, "Peter", 1),(3, "Bart", 1))

#$connection = [MySql.Data.MySqlClient.MySqlConnection]::new($connectionString)

#$connection.Open()
#$transaction = $connection.BeginTransaction()

MySQLUpdate.ps1

using namespace System.Collections.Generic
using namespace System.Linq
using namespace System.Data
using namespace System.Reflection
using namespace System.Management.Automation
using namespace MySql.Data.MySqlClient

if(-not [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")) {
    Write-Output "the MySql connector should be installed to be able to use the cmdlet"
}

<#
 .Synopsis
  Make an update in a MySql database table.

 .Description
  the cmdlet gives the possibility to update values in the specified columns of a MySql Table.

 .Parameter ConnectionString
  The connection string used to access the database.

 .Parameter TableName
  The destination table.

 .Parameter Rows
  The rows to update.

 .Parameter IdColumn
  The id column name used in the update 'where' clause.

 .Parameter Transaction
  The transaction to use in case of the insert operation is part of transaction.

 .Example
   # update using a connection string.
    $connectionString = 'Server=.....;Database=.....;Uid=.....;Pwd=......'
   $tableName = 'menus'
   $idColumn = 'id'
   $rows = @(@{id=1; name='menu1'; active=0}; @{id=2; name='menu2'; active=1})
   MySqlUpdate -ConnectionString $connectionString -TableName $tableName -Rows $rows -IdColumn $idColumn

 .Example
   # update using transaction.
   $connectionString = 'Server=.....;Database=.....;Uid=.....;Pwd=......'
   $tableName = 'menus'
   $idColumn = 'id'
   $rows = @(@{id=1; name='menu1'; active=0}; @{id=2; name='menu2'; active=1})
   $connection = [MySql.Data.MySqlClient.MySqlConnection]::new($connectionString)
   $transaction = $transaction.BeginTransaction()
   MySqlUpdate -Transaction $transaction -TableName $tableName -Rows $rows -IdColumn $idColumn
#>

function MySQL-Update{
    Param(
        [Parameter(Mandatory=$true)][string] $TableName, 
        [Parameter(Mandatory=$true)][object[]] $Rows,
        [Parameter(Mandatory=$true)][string] $IdColumn,
        [string] $ConnectionString,
        [IDbTransaction] $Transaction,
        [Parameter(ValueFromPipeline=$true)][MySqlConnection] $connection
    )
    
    Add-Type -Path "C:\\Program Files (x86)\\MySQL\\Connector NET 8.0\\Assemblies\\v4.5.2\\MySql.Data.dll"

    [IDbConnection]$connection
    [IDbCommand] $updateCommand

    if($Transaction -ne $null) {
        $connection = $Transaction.Connection
        $updateCommand = $connection.CreateCommand()
        $updateCommand.Transaction = $Transaction
    }
    else {
        $connection = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnectionString)
        $updateCommand = $connection.CreateCommand()   
    }

    $updateCommand.CommandType = [System.Data.CommandType]::Text
    $updateCommand.CommandText = Get-UpdateSQL -TableName $TableName -Columns $Rows.Item(0).Keys -IdColumn $IdColumn
    
    $parameters = Get-Parameters -Columns $Rows.Item(0).Keys

    $updateCommand.Parameters.AddRange($parameters)


    try {
        OpenConnection -Connection $connection  
        return UpdateRows -Command $updateCommand -Rows $Rows -IdColumn $IdColumn
    }
    catch [ConstraintException] {
        Write-Host $PSItem.Exception.Message
    }
    catch {
        Write-Host $_.Message
        
        if($Transaction -ne $null)
        {
            $Transaction.Rollback()
        }
    }
    finally {
        $updateCommand.Dispose()

        if($Transaction -eq $null) { 
            $Connection.Close()
            $connection.Dispose() 
        }
    }
}
<###
    #
    #
    ###>
function Get-UpdateSQL 
{
    Param(
        [string] $TableName,
        [string[]] $Columns,
        [string] $IdColumn
    )
    
    $columnsList = [Enumerable]::ToList($Columns)
    [void]$columnsList.Remove($IdColumn)

    $values_aggregator = [Func[string, string, string]] { 
        param($str, $column) 
        if([string]::IsNullOrEmpty($str)) {
            return "$($column)=@$($column)"
        } 
        else 
        { 
            return "$($str), $($column)=@$($column)" 
        } 
    }

    $aggregated = [Enumerable]::Aggregate($columnsList, "", $values_aggregator)     
    
    return "UPDATE $($TableName) SET $($aggregated) WHERE $($IdColumn)=@$($IdColumn)"    
}
<###
    #
    #
    ###>

function Get-Parameters
{
        Param(
            [string[]]$Columns
        )

        $parameters = [List[IDbDataParameter]]::new()
        foreach($current in $Columns){
            $parameter = [MySql.Data.MySqlClient.MySqlParameter]::new()
            $parameter.ParameterName = $current
            $parameters.Add($parameter)
        }
        return $parameters;
}
<###
    # 
    # 
    ###>

function OpenConnection
{ Param([IDbConnection]$Connection)
        try 
        {
            if($connection.State -ne [ConnectionState]::Open) 
            { 
                $connection.Open() 
            }        
        }
        catch 
        {
            Write-Error -Exception $_.Exception -ErrorAction Stop
        }
    }

<###
    #
    #
    ###>    

function UpdateRows 
{
        Param(
            [IDbCommand] $Command,
            [object[]] $Rows,
            [string] $IdColumn
        )

        try 
        {
            $updatedRows = 0
            foreach($Row in $Rows)
            {
                $i = 0;
                foreach($parameter in $Command.Parameters)
                {
                    $parameter.Value = $Row[$parameter.ParameterName]
                    $i++
                }
                $updatedRows += $Command.ExecuteNonQuery()
            }
            return $Updatedrows 
        } 
        catch 
        {
            Write-Error -Exception $_.Exception -ErrorAction Continue
        }
    }
<###
# end helpers
#
###>

function itemFromArrayRow {
    Param($row, $columns)
    return @{id=$row[$columns[0]];name=$row[$columns[1]];state=$row[$columns[2]]}
}
$connectionString = "Server=127.0.0.1;Port=3306;Database=human_resources;Uid=root;Pwd=P@ssw0rd;"
#$connection = [MySql.Data.MySqlClient.MySqlConnection]::new($connectionString)

$tableName = 'Employees'
$Rows = @(@{id=111; name="John"; status=1}, @{id=112; name="Peter"; status=1}, @{id=113; name="Bart"; status=1})
$IdColumn = 'id'

#$connection.Open()
#$transaction = $connection.BeginTransaction()