How to write calculated values to SQL INSERT/UPDATE in PowerShell

PowerShell Help
1

In my below PowerShell I am getting all first level folders in a UNC path, and make a calculation of each of these folders count of files, folders and size in MB, which I need written into a SQL Server table, but this gives me some headaches on writing the values to the SQL string.

I have 3 values that I need to write to the SQL which are:

  • $TotalFileCount
  • $TotalDirectoryCount
  • $TotalFolderSizeInMB’

I have tried to write them out like:

  • $TotalFileCount
  • $($TotalFileCount)
  • $($TotalFileCount.tostring()
  • $($TotalFileCount.value.tostring())

But none of them returns any data when I make a Write-Output , so I expect that it is because $TotalFileCount is a calculation, but any effort to find a solution to write this out have been effortless, so I need some help to find a solution.

My PS script:

$SQLServer = "SQL_SERVER"
 $SQLDatabase = "DATA_BASE"
    
    #SQL function
    function Invoke-SQL {
        param(
            [string] $sqlCommand = $(throw "Please specify a query.")
          )
    
        $connectionString = "Data Source=$SQLServer; " +
                "Integrated Security=SSPI; " +
                "Initial Catalog=$SQLDatabase"
    
        $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
        $connection.Open()
        
        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        
        $connection.Close()
        $dataSet.Tables
    }
    
    Function Get-DirectoryTreeSize {
    
     
    [CmdletBinding(DefaultParameterSetName="Default")]
     
    param(
        [Parameter(
            Position = 0,
            Mandatory = $true
        )]
        [string]  $Path,
     
     
     
        [Parameter(
            Mandatory = $false,
            ParameterSetName = "ShowRecursive"
        )]
        [switch]  $Recurse,
     
     
     
        [Parameter(
            Mandatory = $false,
            ParameterSetName = "ShowTopFolderAllItemsAndAllFolders"
        )]
        [switch]  $AllItemsAndAllFolders
    )
     
        BEGIN {
            #Adding a trailing slash at the end of $path to make it consistent.
            if (-not $Path.EndsWith('\')) {
                $Path = "$Path\"
            }
        }
     
        PROCESS {
            try {
                if (-not $PSBoundParameters.ContainsKey("AllItemsAndAllFolders") -and -not $PSBoundParameters.ContainsKey("Recurse")) {
                    $FileStats = Get-ChildItem -Path $Path -File -ErrorAction Stop | Measure-Object -Property Length -Sum
                    $FileCount = $FileStats.Count
                    $DirectoryCount = Get-ChildItem -Path $Path -Directory | Measure-Object | select -ExpandProperty Count
                    $SizeMB =  "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
     
                    [PSCustomObject]@{
                        Path                 = $Path
                        FileCount            = $FileCount
                        DirectoryCount       = $DirectoryCount
                        FolderSizeInMB       = $SizeMB
                    }
                }
     
                if  ($PSBoundParameters.ContainsKey("AllItemsAndAllFolders")) {
                    $FileStats = Get-ChildItem -Path $Path -File -Recurse -ErrorAction Stop | Measure-Object -Property Length -Sum
                    $FileCount = $FileStats.Count
                    $DirectoryCount = Get-ChildItem -Path $Path -Directory -Recurse | Measure-Object | select -ExpandProperty Count
                    $SizeMB =  "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
     
                    [PSCustomObject]@{
                        Path                 = $Path
                        TotalFileCount       = $FileCount
                        TotalDirectoryCount  = $DirectoryCount
                        TotalFolderSizeInMB  = $SizeMB
                    }
                }
     
                if ($PSBoundParameters.ContainsKey("Recurse")) {
                    Get-DirectoryTreeSize -Path $Path
                    $FolderList = Get-ChildItem -Path $Path -Directory -Recurse | select -ExpandProperty FullName
     
                    if ($FolderList) {
                        foreach ($Folder in $FolderList) {
                            $FileStats = Get-ChildItem -Path $Folder -File | Measure-Object -Property Length -Sum
                            $FileCount = $FileStats.Count
                            $DirectoryCount = Get-ChildItem -Path $Folder -Directory | Measure-Object | select -ExpandProperty Count
                            $SizeMB =  "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
     
                            [PSCustomObject]@{
                                Path                 = $Folder
                                FileCount            = $FileCount
                                DirectoryCount       = $DirectoryCount
                                FolderSizeInMB       = $SizeMB
                            }
                            #clearing variables
                            $null = $FileStats
                            $null = $FileCount
                            $null = $DirectoryCount
                            $null = $SizeMB
                        }
                    }
                }
            } catch {
                Write-Error $_.Exception.Message
            }
     
        }
     
        END {}
     
    }
    
    
    foreach ($name in Get-ChildItem "\\FILSRV01\home dir$"| Where { $_.PSIsContainer } | Where { $_.PSIsContainer } | Select-Object name)
    {
        $TopPath = "\\FILSRV01\home dir$\"
    
        $TempPath = Join-Path $TopPath $name."name"
        
        Get-DirectoryTreeSize $TempPath -AllItemsAndAllFolders
    
   
        $sqlSelectCommand = "SELECT * from EFP_HomeFolder_Stats where UserInitials = '$($name."name")'"
        $sqlInsertCommand = "INSERT INTO EFP_HomeFolder_Stats (Path,UserInitials,TotalFileCount,TotalDirectoryCount,TotalFolderSizeInMB,LastUpdated) VALUES('$TempPath','$($name."name")','$($TotalFileCount)','$TotalDirectoryCount','$TotalFolderSizeInMB',GETDATE())"
        $sqlUpdateCommand = "UPDATE EFP_HomeFolder_Stats SET Path = '$TempPath', UserInitials = '$($name."name")', TotalFileCount = '$TotalFileCount', TotalDirectoryCount = '$TotalDirectoryCount', TotalFolderSizeInMB = '$TotalFolderSizeInMB', LastUpdated = GETDATE()"
    
        Invoke-SQL -sqlCommand $sqlSelectCommand
    
        Write-host $name.ExistCoun
    
    
        Invoke-SQL -sqlCommand "
    
            if exists ($sqlSelectCommand)            
            BEGIN            
                $sqlUpdateCommand
            End                    
            else            
            BEGIN
                $sqlInsertCommand
            END 
            "
    }