connect to mysql using reusable functions

Hi,

I have a script built this way:

function runquery {

param1

connect to mysql

run a query with param1

disconnect

}

Main script:

foreach blabla{

$something = runquery $blabla1

...tasks...

}

I use the runquery function in a foreach and work with the retrieved values (main script).

The problem is that doing this I keep connecting and disconneting to the DB.

I'd like to change this behavior to:

function connecttomysql{}

function runquery{}

function disconnectfrommysql{}

Main script

connecttomysql

foreach blabla{

$something = runquery $blabla1

...task...

}

disconnectfrommysql

This way looks nicier to me (your thoughts?). Though I can't make it work so far.

I must specify that I DO want to run "myquery $pcname" for each pcname.

I don't want to do something like loading the complete "PC table" at once in memory and then work with it.

Said an other way, I don't want to replace: 

"SELECT * FROM wakeonlan WHERE hostname = '" + $pc_name + "';"

with

"SELECT * FROM wakeonlan"';"

Thank you.

Here is the code.

$scriptfolder = $PSScriptRoot

function ConnectToMySql{
  # http://woshub.com/run-mysql-queries-from-powershell/#:~:text=Run%20MySQL%20Queries%20from%20PowerShell%20One%20of%20the,tables%20to%20access%20data%20directly%20from%20PowerShell%20console.

    $mysql =   "myserver"
    $DB  = "mydb"
    $user ="myuser"
    $pass ="mypass"

    # Load MySQL .NET Connector Objects
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
    # Open Connection
    $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString="server=$MySQL;uid=$user;pwd=$pass;database=$DB"}
    $Connection.Open()
}

 function RunQuery{
 param(
 [Parameter (Mandatory = $true)] [string] $pc_name
 )


    $MYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
    $MYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
    $MYSQLDataSet = New-Object System.Data.DataSet
    $MYSQLCommand.Connection=$Connection
    $MYSQLCommand.CommandText="SELECT * FROM wakeonlan WHERE hostname = '" + $pc_name + "';"
    $MYSQLDataAdapter.SelectCommand=$MYSQLCommand
    $NumberOfDataSets=$MYSQLDataAdapter.Fill($MYSQLDataSet, "data")

    #$MYSQLDataSet.tables[0].id
    $pcinf = [PSCustomObject] @{
    hostname = $MYSQLDataSet.tables[0].hostname
    macadd   = $MYSQLDataSet.tables[0].macadd
    dbrcast  = $MYSQLDataSet.tables[0].dbrcast
    mask     = $MYSQLDataSet.tables[0].mask
    }
    return $pcinf
}

function DisconnectMysql{
    $Connection.Close()
}

#loading computers
$RemoteOfficeComputers = import-csv -Path "$scriptfolder\teletravail-vdi_prod.csv" -Delimiter ";" -Header "Domain", "Computer", "User"
#loading existing IP route 
$IPRouteInformation    = import-csv -Path "$scriptfolder\VCS_WOL_IPRoute2.ini" -Delimiter ";"



#move INI file values from array to hash
$NewIPRouteInformation=$null
$NewIPRouteInformation=@{}
foreach($record in $IPRouteInformation)
{
    $NewIPRouteInformation[$record.hostname]=[ordered]@{
        TimeID = $record.TimeID
        Hostname = $record.hostname
        macadd = $record.macadd
        dbrcast = $record.dbrcast
        mask = $record.mask }
}

#connecting to the DB
ConnectToMySql

#Checking each PC from CSV file
foreach($PC in $RemoteOfficeComputers.computer){
    $TimeID = date -Format yyyyMMddhhmmssfff
    #Getting PC's information from RT's DB
    #Running the query many times with the needed parameter
    $pcinfo = runquery -pc_name $PC
    write-host "Working on pcinfo.hostname = $($pcinfo.hostname)"

    #Verify if we find the PC in the ini file
    if ($pcinfo.hostname -eq $NewIPRouteInformation[$pcinfo.hostname].hostname){
        write-host "Both hostnames match $($pcinfo.hostname) = $($NewIPRouteInformation[$pcinfo.hostname].hostname)"
        
        #If the PC name is found, check if the Domain Broadcast has changed, if yes update and change TimeStamp
        if($pcinfo.dbrcast -ne $NewIPRouteInformation[$pcinfo.hostname].dbrcast) {
                $NewIPRouteInformation[$pcinfo.hostname].dbrcast = $pcinfo.dbrcast
                $NewIPRouteInformation[$pcinfo.hostname].TimeID = $TimeID  
        }
        write-host "Done for $($pcinfo.hostname)"
    }else{ #If hostname was not found, create the record in ini File
        write-host "NO hostnames match $($pcinfo.hostname) = $($NewIPRouteInformation[$pcinfo.hostname].hostname)"
            
        $NewIPRouteInformation[$pcinfo.hostname]=[ordered]@{
                TimeID = $TimeID
                Hostname = $pcinfo.hostname
                macadd = $pcinfo.macadd
                dbrcast = $pcinfo.dbrcast
                mask = $pcinfo.mask }
         write-host "Done for $($pcinfo.hostname)"
         #write-host "showing hash value :"$NewIPRouteInformation[$pcinfo.hostname]

    }
 } #end foreach PC

#use variable before ? Test execution time
#Removing records older than 365 days
$NewIPRouteInformation.GetEnumerator() | ? { $_.Value.TimeID -lt (date (get-date).AddDays(-365) -Format yyyyMMddhhmmssfff) } | %{$NewIPRouteInformation.Remove($_)}
#sort by timeID and then export to CSV
$NewIPRouteInformation.GetEnumerator() | Sort-Object $_.keys | ForEach-Object{[PSCustomObject]$_.value } | Sort-Object TimeID -Descending | Export-Csv "$scriptfolder\VCS_WOL_IPRoute2.ini" -NoTypeInformation -delimiter ";"

DisconnectMysql