Option: Get File Directory with Child Aggregations w/o Get-ChildItem


Get-ChildItem performs actions on a file directory, and it is one of the first PowerShell cmdlets that I learned; I am not advocating for its replacement. Yet, I have wondered if there were suitable options to modeling and storing data from directories and files—especially for very large or complex directory trees. I created a function that searches directories and returns aggregations from their child items. Below is the demonstration code from that function.


I chose four parent directories that have certain characteristics in access and write ages, file size, and the number of child items. These paths are stored in a variable in the first and longest of three logical (and unlabled) code sections. The reminder of this first section focuses on data modeling.

The second block of code is quite short—essentially everything happens inside foreach($i in $DirectoryPath) and involves the getting the directory and files data, and transforming those data inside of class instances.

The third code section involves the sending and storing of the [DataTable] rowset in a SQL database. More information about this can be found here.

Special Notes

  • HashID is used only for SQL boolean evaluations and is not meant to be an encryption key.
  • GetFileSystemInfos("*","AllDirectories") may create infinite loops on linked directories (see more).
  • Hidden items are included by default.
    • –from what I discovered and unlike gci (where -Force must be specified).

Demo Code

This code is intended for demostration only. The author does not consider it stable or suitable for production.

using namespace System.Collections;
using namespace System.Data;
using namespace System.Data.SqlClient;
using namespace System.IO;
using namespace Microsoft.SqlServer.Management.Smo;


[string[]]$DirectoryPath = "C:\SW-Kotor","A:\xampp","C:\Program Files\GIMP 2","A:\Privateer 2 - The Darkening";

Class FileShare
    [datetime] $CreationTimeUtc;
    [string] $FullName;
    [nullable[datetime]] $LastAccessTimeUtc_All;
    [nullable[datetime]] $LastWriteTimeUtc_All;
    [string] $Name;
    [int] $NbrFiles;
    [int] $NbrFolders;
    [string] $Parent;
    [string] $Root;
    [decimal] $TotalFileSize;
    [string] $HashID;

        $counts = $this.GetOnHandCount($fname);

        $this.CreationTimeUtc = $fname.CreationTimeUtc;
        $this.FullName = $fname.FullName;
        $this.LastAccessTimeUtc_All = if([string]::IsNullOrEmpty($counts[0])){"1900-01-01"}else{$counts[0]}
        $this.LastWriteTimeUtc_All  = if([string]::IsNullOrEmpty($counts[1])){"1900-01-01"}else{$counts[1]}
        $this.Name = $fname.Name;
        $this.NbrFiles = $counts[2];
        $this.NbrFolders = $counts[3];
        $this.Parent = $fname.Parent;
        $this.Root = $fname.Root;
        $this.TotalFileSize = $counts[4];

        $getFSI = $path.GetFileSystemInfos("*","AllDirectories");
        $queue = [Queue]::new();
        $queue.Enqueue(($getFSI.LastAccessTimeUtc | Sort-Object | Select-Object -Last 1));
        $queue.Enqueue(($getFSI.LastWriteTimeUtc | Sort-Object | Select-Object -Last 1));
        $queue.Enqueue(($getFSI.Where({$_.Attributes -notmatch "Directory"}).Count));
        $queue.Enqueue(($getFSI.Where({$_.Attributes -match "Directory"}).Count));

        [float]$totalFileSizeBytes = 0;

        foreach($i in $getFSI.Where({$_.Attributes -notmatch "Directory"}).Length)
            $totalFileSizeBytes += $i;

        $q1 = Switch($totalFileSizeBytes)
            {$_ -gt 0}{[decimal]::Round($_/1MB,2);break}

        $results = [ArrayList]::new();
        return $results;

        $paramStream = [MemoryStream]::new()
        $streamWriter = [StreamWriter]::new($paramStream);
        $paramStream.Position = 0;

        return Get-FileHash -InputStream $paramStream -Algorithm MD5 | Select-Object -ExpandProperty Hash;


$FileShareCollection = [Datatable]::new('FileShares','Inventory')

    $FileShareCollection.PrimaryKey = $FileShareCollection.Columns["FullName"];

    function Add-FileShare([FileShare]$item)
        $row = $FileShareCollection.NewRow();

        foreach($i in $item)
            $row.CreationTimeUtc = $i.CreationTimeUtc;
            $row.FullName = $i.FullName;
            $row.LastAccessTimeUtc_All = $i.LastAccessTimeUtc_All;
            $row.LastWriteTimeUtc_All = $i.LastWriteTimeUtc_All;
            $row.Name = $i.Name;
            $row.NbrFiles = $i.NbrFiles;
            $row.NbrFolders = $i.NbrFolders;
            $row.Parent = $i.Parent;
            $row.Root = $i.Root;
            $row.TotalFileSize = $i.TotalFileSize;
            $row.HashID = $i.HashID;

    # catch exception class goes here;
    # catch exception collection class goes here;

    foreach($i in $DirectoryPath)
        [DirectoryInfo]$ancestorPath = $i;

        foreach($j in $ancestorPath.GetDirectories("*","Top"))
            $Error.Clear(); # for logging IOException
            # try/catch [IOException] goes here;
            $parentPath = [DirectoryInfo]::new("$($j.FullName)");
            $directoryInfo = [FileShare]::new($parentPath);
            $directoryInfo.HashID = $directoryInfo.GenerateHash(($directoryInfo.LastAccessTimeUtc_All,$directoryInfo.LastWriteTimeUtc_All,$directoryInfo.NbrFiles,$directoryInfo.NbrFolders,$directoryInfo.TotalFileSize-join''));

[Server]$targetServer = "Bibopbibeep";
[Database]$targetDatabase = $targetServer.Databases.Item("NotAdventureWorks");

$targetConnection = [SQLConnection]::new("Server=$($targetServer.Name); Database=$($targetDatabase.Name); Integrated Security=true");

    $manageTargetTable = [SQLCommand]::new("Inventory.ManageFileShare",$targetConnection);
    $manageTargetTable.CommandType = [CommandType]::StoredProcedure;
    $manageTargetTable.Parameters.Add([SqlParameter]::new("@dt",[SQLDbType].Structured)).Value = $FileShareCollection;


See Also

Is there a question in this post? The question I have is why store all this in SQL, what purpose does it server. Thanks.

1 Like

Hello, @tonyd

Thanks for replying. The originating questions were mine when I looked for alternatives to gci. Many of those questions stemmed from inventory-related business requirements from different teams (total file size(s) over time, folders usage (static and dynamic), &c.). These collected data—along with other resource inventory—are stored in a database because access and querying are fundamentals for the teams and for leadership #ROI. And, as dashboards are making the rounds again as folks have their hands on a new Bright-n-Shiny, the database provides a solid backend. The SQL table in the demonstration and in the enterprise are temporal to help develop trend analyses.

It’s my hope that this post and those demonstrating innovative solutions help answer questions yet to be asked.