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