PowerShell: Create and Delete Folders Based on SQL DataSet

Question

I'm trying to write a PowerShell script that will do the following:

1. Executes a stored procedure or run a t-SQL command that returns a list of values.
2. Compares the data from step 1 to an existing file directory structure.
3. Creates directories for values in step 1 that are not in the existing directory structure.
4. Deletes directories for values not in step 1 that are in the existing directory structure.

I built a similar process using SSIS already, but I would like to try to move this into a PowerShell script since I suspect it will perform substantially better than my current SSIS package. I eventually figured it out on my own and the code is listed below.

It would be great if someone posts a faster way to delete the missing folders without having to iterate through the data set for every folder, but I haven't been able to find a way. I tried -contains and Compare-Object, but I couldn't figure out the way to achieve my goal.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = ServerName; Database = DatabaseName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT DISTINCT folder = CONVERT(VARCHAR(MAX), id) FROM Some.Table (NOLOCK);"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

$RootDirectory = "\\Some\Root\Directory\Over\There\"

# Delete folders if they shouldn't exist.
$RootDirectoryChildItems = Get-ChildItem $RootDirectory
foreach ($i in $RootDirectoryChildItems)
{
    $Matched = 0
    $TargetDirectory = $RootDirectory + $i.Name

    $DataSetTableRows = $DataSet.Tables[0].Rows
    foreach ($Row in $DataSetTableRows)
    {
        if ($Row.folder -eq $i.Name)
        {
            $Matched = 1
        }
    }

    if ($Matched -eq 0)
    {
        Write-Output 'Deleted folder' $TargetDirectory; Remove-Item $TargetDirectory -Recurse | Out-Null
    }
}

# Create folders if they don't exist.
$DataSetTableRows = $DataSet.Tables[0].Rows
foreach ($Row in $DataSetTableRows)
{  
    $TargetDirectory = $RootDirectory + $Row.folder
    if (!(Test-Path $TargetDirectory))
    {
        Write-Output 'Created folder' $TargetDirectory '.'; New-Item $TargetDirectory -type directory | Out-Null
    }
}

Answer 1

I'd probably build a couple of collections based on the dir and stored procedure results and compare them with compare-object. The "SideIndicator" field on the results should tell you what you need to do.

Answer 2

I resolved this issue myself on Friday and added the answer to the question.