PowerShell Class to Generate SQL Insert statements

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
}

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

}