Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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])
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()
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment