Read the sql files by Exclude the list of files from sql table and Execute all the files in a single Transaction using PowerShell

I have not an Expert in power shell. I Need a script/Approch which handles the below requirement.

I have list of files in a folder and the file Names like below.

001_File.sql
002_File.sql
003_File.sql
004_File.sql

Also, I have a table in sql server which holds the file Name Information.

TableName: Executedfile with a column FileName.

002_File.sql
004_File.sql

My requirement is to read the files which is available in the folder but not in the table.

I have to the Read files only:

001_File.sql
003_File.sql

Now, I Need to Execute these two file in the sequential order under the same Transaction on SQL Server. As I Need to rollback all the transaction if any Error occurred.

As of now I wrote a power shell something below.

$QueryResult = Invoke-Sqlcmd -ServerInstance 'MyServer' -Database 'MyDb' -Query "SELECT DISTINCT FNames from TableName"

Get-ChildItem "E:Testing" -Filter *.sql | Sort-Object $_.Name|
Foreach-Object {
    $FileFullpath= $_.FullName

     Write-Host $FileFullpath

     $FileName = $_.Name

     Write-Host $FileName

     if(!$QueryResult.FName.Contains($FileName))
     {
        invoke-sqlcmd -inputfile $FileFullpath -serverinstance "servernameserverinstance" -database "mydatabase"
     }

}

Please suggest me some script.

Challenges:

1. How to read the files in Sequential order as it has leading Zeros. Does the Above `Sort-Object $_.Name ‘ will Sort ?
2. How to Execute all the list of files under one transaction.

Thanks

Answer

Finally I wrote the Script using SMO Objects to handle the GO Statement and Transactions.

$SqlFilePath = "D:RoshanTestingSQL"

$serverName = "MyServer"
$databaseName = "MyDB"

$QueryResult = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "SELECT DISTINCT FName from dbo.TableName" -AS DataRows

$connection = new-object system.data.SqlClient.SQLConnection("Data Source=$serverName;Integrated Security=SSPI;Initial Catalog=$databaseName;Connection Timeout=600;Max Pool Size=10"); 
$Server = new-Object Microsoft.SqlServer.Management.Smo.Server(New-Object Microsoft.SqlServer.Management.Common.ServerConnection($connection))

$script_contents ="SET XACT_ABORT ON
                GO
                BEGIN TRANSACTION 
                GO"


Get-ChildItem $SqlFilePath -Filter *.sql| Sort-Object $_.Name|

ForEach-Object {

if(!$QueryResult.FName.Contains($_.Name))
{
    Write-Host $_.Name -ForegroundColor Magenta

    #[string]$script_contents = Get-Content $_.FullName
    $script_contents += [IO.File]::ReadAllText($_.FullName)

   #Write-Host $script_contents
   #$Server.ConnectionContext.ExecuteNonQuery($script_contents)
} 

} 

$script_contents+= " COMMIT TRANSACTION;"

$Server.ConnectionContext.ExecuteNonQuery($script_contents)