PowerShell Class to Generate SQL Insert statements

There are better ways to do inserts but sometimes you just want to generate SQL. Maybe? Either way, it shows the use of classes in PowerShell.

class BuildSQLArray {

    <#
    .SYNOPSIS
        Class to build SQL 'insert' value rows easily.

    .METHODS
        AddRow()
        GetRows()
        GenSQL()

    .PARAMETER
        AddRow([System.Collections.ArrayList]$Row)
        GenSQL([string]$Table, [System.Collections.ArrayList]$Columns)

    .EXAMPLE
        $SQLBuilder = New-Object BuildSQLArray

        [System.Collections.ArrayList]$ColValues = @("'a'","'b'","'c'","'d'")
        $SQLBuilder.AddRow($ColValues)

        [System.Collections.ArrayList]$ColValues = @("'e'","'f'","'g'","'h'")
        $SQLBuilder.AddRow($ColValues)

        [System.Collections.ArrayList]$ColValues = @("'i'","'j'","'k'","'l'")
        $SQLBuilder.AddRow($ColValues)

        [System.Collections.ArrayList]$ColValues = @("'m'","'n'","'o'","'p'")
        $SQLBuilder.AddRow($ColValues)

        ForEach ($List in $SQLBuilder.GetRows()) {
            Write-Host $List
        }

        $InsertSQL = $SQLBuilder.GenSQL("DBO.TheTable", @("1"; "2"; "3"; "4"))
        Write-Host $InsertSQL

    .RETURN
        [System.Collections.ArrayList]$Rows = GetRows()
        [String]$Sql = GenSQL()
    #>

    [System.Collections.ArrayList]$ValueList = @()
    [System.Collections.ArrayList]$ValueLists = @()
    [int]$LimitRows = 800
    [int]$RowCount = 0

    [void]AddRow([System.Collections.ArrayList]$Row) {

        $this.RowCount += 1

        if ($this.RowCount -le $this.LimitRows) {
            $StrRow = $Row -Join ","
            $StrRow = "({0})" -f $StrRow
            $this.ValueList.Add($StrRow)
        } else {
            $this.UpdateRows()
            $StrRow = $Row -Join ","
            $StrRow = "({0})" -f $StrRow
            $this.ValueList.Add($StrRow)
        }
    }

    [void]UpdateRows() {

        if ($this.ValueList) {
            $StrRows = $this.ValueList -join ","
            $StrRows = "{0}" -f $StrRows
            $this.ValueLists += $StrRows
            [System.Collections.ArrayList]$this.ValueList = @()
            $this.RowCount = 0
        }
    }

    [array]GenSQL([String]$Table, [Array]$Columns) {
        $SQL = @()
        $Vals = $this.GetRows()
        $Columns = $Columns -Join ','

        if ($Vals) {
            ForEach ($Grp in $Vals) {
                $SQL += "INSERT INTO $Table ($Columns) VALUES $Grp;"
            }
        }
        return $SQL #-Join "`n"
    }

    [System.Collections.ArrayList]GetRows() {
        $this.UpdateRows()
        return $this.ValueLists
    }
}

<# MAIN: Test #>
cls

# Instantiate the class.
$SQLBuilder = New-Object BuildSQLArray

# Limit values per insert command. Best to keep this even, and I do not check for odd.
$SQLBuilder.RowCount = 2

# Add some row values. 
[System.Collections.ArrayList]$ColValues = @("'a'","'b'","'c'","'d'")
$SQLBuilder.AddRow($ColValues)

[System.Collections.ArrayList]$ColValues = @("'e'","'f'","'g'","'h'")
$SQLBuilder.AddRow($ColValues)

[System.Collections.ArrayList]$ColValues = @("'i'","'j'","'k'","'l'")
$SQLBuilder.AddRow($ColValues)

[System.Collections.ArrayList]$ColValues = @("'m'","'n'","'o'","'p'")
$SQLBuilder.AddRow($ColValues)

# Add the table and columns.
$InsertSQL = $SQLBuilder.GenSQL("DBO.TheTable", @("1"; "2"; "3"; "4"))

# Check the result.
ForEach ($SQL in $InsertSQL) {
    Write-Host $SQL
}