Dark Artistry

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
}