kenger.SQLData.psm1

function GetColumnCreate($column, $types) {
    $type = $types | where { $_.Keys -eq $column } | % { $_.Values }
    
    if ( $type -eq $null ) {
        return "[$column] NVARCHAR(255) NULL"
    }

    switch ( $type )
    {  
        "System.DateTime" {"[$column] [datetime] NULL"}
        "datetime" {"[$column] [datetime] NULL"}

        "System.Int32" {"[$column] [int] NULL"}
        "int" {"[$column] [int] NULL"}

        "System.Int64" {"[$column] [bigint] NULL"}
        "long" {"[$column] [bigint] NULL"}

        "System.Boolean" {"[$column] [bit] NULL"}
        "bool" {"[$column] [bit] NULL"}

        default { "[$column] NVARCHAR(255) NULL" }
    }
}


 Function Convert-ByteArrayToHex {
    [cmdletbinding()]
    param(
        [parameter(Mandatory=$true)]
        [Byte[]]
        $Bytes
    )
    $HexString = [System.Text.StringBuilder]::new($Bytes.Length * 2)
    ForEach($byte in $Bytes){
        $HexString.AppendFormat("{0:x2}", $byte) | Out-Null
    }
    $HexString.ToString()
}


function Force-ToDB( $data, $table, $conn, [switch]$droptable, [switch]$removeUnusedColumns, [string]$primarykeyname = "id" ) {

    $CreateTableSQL = @"
    IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table'))
    BEGIN
        SET ANSI_NULLS ON
        SET QUOTED_IDENTIFIER ON
     
        CREATE TABLE [dbo].[$table](
            [$primarykeyname] [int] IDENTITY(1,1) NOT NULL
         CONSTRAINT [PK_$table] PRIMARY KEY CLUSTERED
        (
            [$primarykeyname] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]
    END
"@


   $DropTableSQL = @"
    IF ( EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table'))
    BEGIN
        DROP TABLE [$table]
    END
"@


    $data | select -First 1 | % {
        $obj = $_

        if ( $droptable.IsPresent ) {
            Write-SQLData -query $DropTableSQL -conn $conn | Out-Null
        }
        
        Write-SQLData -query $CreateTableSQL -conn $conn | Out-Null

        $schema_columns = Read-SQLData -conn $conn -query "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$table' AND NOT COLUMN_NAME = '$primarykeyname'" | Select -ExpandProperty COLUMN_NAME
        $columns = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name);
        $values  = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name | % { "'$($obj.$_ -Replace "'","''")'" });

        $columnsAdd = $columns | where { $schema_columns -NotContains $_ }  
        $columnsDel = $schema_columns | where { $columns -NotContains $_ }

        if ( $columnsDel -ne $null -and $removeUnusedColumns ) {
            $columnsDelSQL = ($columnsDel | % { "[$_]" } ) -join ","
            $columnsDelSQL = "ALTER TABLE $table DROP Column $columnsDelSQL"
            Write-SQLData -query $columnsDelSQL -conn $conn | Out-Null
        }

        if ( $columnsAdd -ne $null ) {
            #$columnAddTypes = $obj | get-member -MemberType NoteProperty,Properties,Property | select -exp Name | where { $obj.$_ -ne $null } | % { @{$_ = $obj.$_.GetType().ToString() } }
            $columnAddTypes = $obj | get-member -MemberType NoteProperty,Properties,Property | % { @{$_.Name = ($_.Definition -split " ")[0] } }
            $columnsAddSQL = ( $columnsAdd | % { ( GetColumnCreate -column $_ -types $columnAddTypes ) } ) -join ","
            $columnsAddSQL = "ALTER TABLE $table ADD $columnsAddSQL"
            Write-SQLData -query $columnsAddSQL -conn $conn | Out-Null
        }

        #Ensure string's not getting over 255, if so, alter the column to a nvarchar(MAX) instead of nvarchar(255).
        $supportedNonStringTypes = @("System.DateTime", "datetime", "System.Int32", "int", "System.Int64", "long", "System.Boolean", "bool");
        #$cols = $obj | get-member -MemberType NoteProperty,Properties,Property | where { ( ($_.Definition -split " ")[0] -eq "string" ) -or ( ($_.Definition -split " ")[0] -eq "System.String" ) } | select -ExpandProperty Name
        $cols = $obj | get-member -MemberType NoteProperty,Properties,Property | where { ( ($_.Definition -split " ")[0] -notin $supportedNonStringTypes ) } | select -ExpandProperty Name
        $cols | % { 
            $col = $_;
            #$length = ($data | select -ExpandProperty $col | % { $_.ToString() } | Measure-Object -Maximum -Property Length).Maximum
            #$length = ( ($data | select -ExpandProperty $col | % { $_.ToString() } ) -join "`r`n" | Measure-Object -Maximum -Property Length).Maximum
            #$length = ( ($data | select -ExpandProperty $col | % { $_ } | Out-String ) -join "`r`n" | Measure-Object -Maximum -Property Length).Maximum
            $length = $data | select $col | % { ($_.$col -join "`r`n" ).length } | Sort-Object -Descending | select -first 1
            if ( $length -gt 255 ) {
                $alterColumnSQL = "ALTER TABLE $table ALTER COLUMN [$col] nvarchar(MAX) NULL;"
                Write-SQLData -query $alterColumnSQL -conn $conn | Out-Null
            }
        }
    }
}





function New-SQLConn {
    <#
    .SYNOPSIS
    Create a new SQL Connection object
    .DESCRIPTION
    Returns a new SQL Connection object, than can hold the connection details of a SQL connection,
    that may be used several times in the same script
   
    .PARAMETER Server
    IP or hostname of the SQL database server you want to connect to.
    .PARAMETER Database
    Database name of the database you want to access.
    .PARAMETER sqlCredentials
    Username and password for the sql server
   
    .OUTPUTS ConnectionObject
    Object that contains connection the information given,
    so that they can be reused
    #>

    [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
    param
    (
        [Parameter(Mandatory=$True,
        HelpMessage='What IP or hostname do you want to connect to?')]
        [string]$server,

        [Parameter(Mandatory=$true,
        HelpMessage='What database do you want to access?')]
        [string]$database,

        [Parameter(
            Mandatory=$false,
            ParameterSetName='Trusted')]
        [switch]$trustedconnection,

        [Parameter(
            Mandatory=$false,
            HelpMessage='Windows credentials for impersonating',
            ParameterSetName='Trusted')]
        [PSCredential]$credentials,

        [Parameter(
            Mandatory=$false,
            HelpMessage='SQL Username and Password',
            ParameterSetName='NonTrusted')]
        [PSCredential]$sqlCredentials
    )

    begin {
        if ( $trustedconnection -eq $false -and $sqlCredentials.UserName -eq '' ) {
            throw "Missing some credentials parameter, sqlCredentials or use -TrustedConnection switch"
        }

        if ( $credentials.UserName -ne '') { # if credentials is present, we assum that they will use trusted connection
            $trustedconnection = [switch]::Present;
        }
    }

    process {
        $info = @{};
        $info.Server = $server;
        $info.Database = $database;
        $info.sqlCredentials = $sqlCredentials;
        $info.TrustedConnection = $trustedconnection;
        $info.Credentials = $credentials;
        
        if ( $trustedconnection ) {
            $info.ConnectionString = "server='$($info.Server)';database='$($info.Database)';trusted_connection=true;"
        }else{
            $info.ConnectionString = "server='$($info.Server)';database='$($info.Database)';User Id='$($info.sqlCredentials.Username)';Password='$($info.sqlCredentials.GetNetworkCredential().Password)'"
        }

        $conn = New-Object -TypeName PSObject -Prop $info;
        return $conn;
    }
}


function Read-SQLData {
    <#
    .SYNOPSIS
    Read data from SQL database
    .DESCRIPTION
    Returns a datatable that holds the selected data
   
    .EXAMPLE
    Read-SQLData -query "SELECT * FROM table" -Conn $conn
 
    .PARAMETER Query
    SQL query
    .PARAMETER Conn
    Connection Object
    #>

    [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
    param
    (
        [Parameter(Mandatory=$true, HelpMessage='SQL query?')]
        [string]$query,

        [Parameter(
            Mandatory=$true, 
            HelpMessage='Connection object (New-SQLConn)')]
        [PSObject]$conn
    )

    begin {
        
    }

    Process {
        Try {
            $code = {
                $conn = $args[1]
                $Connection = New-Object System.Data.SQLClient.SQLConnection
                $Connection.ConnectionString = $conn.ConnectionString
                $Connection.Open()
                $Command = New-Object System.Data.SQLClient.SQLCommand
                $Command.Connection = $Connection

                $Command.CommandText = $args[2];
                
                $result = $Command.ExecuteReader()
    
                $table = new-object "System.Data.DataTable"
                $table.Load($result)

                $Connection.Close()

                $table;
            }

            Write-Verbose "Query: $query";            
            Write-Verbose "ConnectionString: $($conn.ConnectionString)";
            if ( $conn.Credentials -ne $null ) {
                Write-Verbose "Impersonating $($conn.Credentials.UserName)"
                $job = Start-Job  -Credential $conn.Credentials -ArgumentList( ([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code
            } else {
                $job = Start-Job -ArgumentList(([environment]::CurrentDirectory=(Resolve-Path .\).Path),  $conn, $query) -ScriptBlock $code
            }
            $result = $job | Wait-Job | Receive-Job | select * -ExcludeProperty RunspaceId
            $job | Remove-Job
            Write-Verbose "$($result.Rows.Count) records returned";
            $result;
        }
        Catch {
            Write-Host -BackgroundColor Red "Error: $($_.Exception)"
            Break
        }
    }

    End {
        If ($?) {
            #Write-Host 'Completed Successfully.'
            #Write-Host ' '
        }
    }
}


function Write-SQLData {
    <#
    .SYNOPSIS
    Run SQL query on a MS SQL database
    .DESCRIPTION
    Save or update data to a database, or run a SQL command. This command return the number of rows affected, if $false is returned, something when wrong, try -verbose
   
    .EXAMPLE
    Write-SQLData -query "INSERT * FROM table () VALUES ('aaa','bbb')" -Conn $conn
 
    .PARAMETER Query
    SQL query
    .PARAMETER Conn
    Connection Object
    #>

    [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
    param
    (
        [Parameter(Mandatory=$true, HelpMessage='SQL query?')]
        [string[]]$query,

        [Parameter(Mandatory=$true, HelpMessage='Connection object')]
        [PSObject]$conn
)

    begin {
        
    }

    process {
        try {
            $code = {
                $conn = $args[1]
                $Connection = New-Object System.Data.SQLClient.SQLConnection
                $Connection.ConnectionString = $conn.ConnectionString
                $Connection.Open()
                $Command = New-Object System.Data.SQLClient.SQLCommand
                $Command.Connection = $Connection

                $Command.CommandText = $args[2];
                
                $result = $Command.ExecuteNonQuery()

                $Connection.Close()

                $result
            }


            Write-Verbose "ConnectionString: $($conn.ConnectionString)";
            Write-Verbose "Query: $query";
            
            if ( $WhatIfPreference ) {
                Write-Host "Whatif, this query would have been executed: $query" -ForegroundColor Yellow;
            }

            $rowCount = 0;
            if ($WhatIfPreference -eq $false) {
                if ( $conn.Credentials -ne $null ) {
                    Write-Verbose "Impersonating $($conn.Credentials.UserName)"
                    $job = Start-Job  -Credential $conn.Credentials -ArgumentList( ([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code
                } else {
                    $job = Start-Job -ArgumentList(([environment]::CurrentDirectory=(Resolve-Path .\).Path),  $conn, $query) -ScriptBlock $code
                }
                $rowCount = $job | Wait-Job | Receive-Job -Keep
                #$job | Remove-Job
            }

            Write-Verbose "Rows Updated: $rowCount"
        } catch {
            Write-Host -BackgroundColor Red "Error: $($_.Exception)"
            return $false;
        }
    }

    end {
        If ($?) {
            #return $rowCount;
            #Write-Host 'Completed Successfully.'
            #Write-Host ' '
        }
    }

}


function Write-SQLObject {
    <#
    .SYNOPSIS
    Write / Update data to/in a SQL database
    .DESCRIPTION
    Save or update data a table, by providing a object with properties matching to the table columns, this command gives no SQL data as result, only succeded or not
   
    .EXAMPLE
    Write-SQLData -object $obj -Conn $conn
    .EXAMPLE
    Write-SQLData $obj -Conn $conn
    .EXAMPLE
    Write-SQLData -object $obj -Conn $conn -force
 
    .PARAMETER Object
    Data Object (PSObject)
    .PARAMETER table
    Name of the data table to use
    .PARAMETER mode
    Default mode is insert, change if you need to update or delete data.
     
    Insert mode is used for inserting a new row into a table.
 
    Update mode is used for updating an existing row, remember to provide -objectkeys to tell what row that should be updated,
    ex. (-updatekeys ID) will tell that the ID columns is unique.
 
    Delete mode, is used to delete data from a table. The -objectkeys is also required to tell what row that should be deleted.
 
    InsertOrUpdate mode is a dynamic way to wrap the Insert and Update statetent in a TSQL IF statement,
    so it will check if the -updatekeys is found, and update the row, or if not found, insert as a new row.
 
    .PARAMETER objectKeys
    Used if you are using Update or InsertOrUpdate modes, lets the module know what rows you want to update (like a where clause)
    .PARAMETER Conn
    Connection Object
    .PARAMETER UpdateWithEmptyData
    Tells the module that you want to insert or update empty data such as '' into the db, ex. in case of empty properties on your data object.
    .PARAMETER IgnoreColumns
    List of columns to ignore and leave out from insert or updates, ex. auto incremental primary keys -IgnoreColumns id
    .PARAMETER force
    Force creation or altering of existing table with columns corresponding to the propeties of the object passed to the function.
    Supported colum types nvarchar, DateTime, Int32, Int64 and Boolean.
    .PARAMETER forceDropTable
    Need the -force, will force a drop table before recreating a new table.
    .PARAMETER forceRemoveUnusedColumns
    Need the -force, will alter an existing table and remove the columns not contained in the object passed to the method.
    .PARAMETER forcePrimaryKeyName
    Need the -force, should be used if primary key should not be called "id" as default, also needed to be specified when using -forceRemoveUnusedColumns
    if primary key is not called "id"
    #>

    [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
    param
    (
        [Parameter(Mandatory=$true, HelpMessage='Data Object?', ValueFromPipeline=$True, Position=1)]
        [PSObject]$object,

        [Parameter(Mandatory=$true, HelpMessage='Table name?')]
        [string]$table,

        [Parameter(Mandatory=$false)]
        [ValidateSet('Insert', 'Update', 'Delete', 'InsertOrUpdate')]
        [string]$mode = 'Insert',

        [Parameter(Mandatory=$false)]
        [string[]]$objectKeys = $null,

        [Parameter(Mandatory=$true)]
        [PSObject]$conn,

        [Parameter(Mandatory=$false)]
        [switch]$UpdateWithEmptyData = $false,

        [Parameter(Mandatory=$false)]
        [string]$IgnoreColumns = '',


        [Parameter( Mandatory=$false,
            ParameterSetName='Force')]
        [switch]$force,

        [Parameter( Mandatory=$false,
            ParameterSetName='Force')]
        [switch]$forceDropTable,

        [Parameter( Mandatory=$false,
            ParameterSetName='Force')]
        [switch]$forceRemoveUnusedColumns,

        [Parameter( Mandatory=$false,
            ParameterSetName='Force')]
        [string]$forcePrimaryKeyName = "id"


    )

    begin {
        
        Write-Verbose "Mode: $mode";
        if ( $mode -ne 'Insert' -and $objectKeys -eq $null) {
            throw "You need to define -objectKeys when using Update or InsertOrUpdate modes"
        }

        if ( $force.IsPresent ) {
            Write-Verbose "-Force is specified, forcing the table and column creation / altering"
            Force-ToDB -data $object -table $table -conn $conn -droptable:$forceDropTable -removeUnusedColumns:$forceRemoveUnusedColumns -primarykeyname $forcePrimaryKeyName
        }

        $rowCount = 0;
    }

    process {
        try {
            $queries = @();

            $object | % {
                $obj = $_

                #[System.Collections.Generic.List[System.Object]]($columns)
                $columns = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name);
                $values  = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name | % { 
                    $objData = $obj.$_;
                    if ( $objData -eq $null ) {
                        'null';
                    }else{
                        switch ($objData.GetType().Name) {
                            "Byte[]" {
                                "0x$(Convert-ByteArrayToHex $objData)";
                            }
                            "Int32" {
                                [int]"$([int]$objData)";
                            }
                            default {
                                "'$($objData -Replace "'","''")'";
                            }
                        }
                    }
                    
                });
            
                # we need the original key/value sets because the columns and values are filtered, emptydata and ignored columns are removed..
                # we still need to use these ignored keys in where clause for updating, ex. by id column... we could create then as a PSObject instead with a ignored flag? (TODO)
                $whereColumns = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name);
                $whereValues  = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name | % { "'$($obj.$_ -Replace "'","''")'" });
            
                # handle the UpdateWithEmptyData switch, if not set, dont insert or update with empty data '', remove the column and values from the lists.
                for( $i=0; $i -lt $columns.ToArray().count; $i++) {
                    if ( ( $UpdateWithEmptyData -ne $true -and $values[$i] -eq "''" ) -or ( $IgnoreColumns -contains $columns[$i] ) ) {
                        $columns.RemoveAt($i);
                        $values.RemoveAt($i);
                    }
                }

                # todo ^ handle int or String excape ($obj.ConnectionString.GetType() | select -exp name) == "String"
                # ^ really no need.. int can be handles as strings


                # Generate Update and Where string, for use in Update and InsertOrUpdate modes
                $list = @(); 
                for( $i=0; $i -lt $columns.count; $i++) { 
                    $list += "[$($columns[$i])]=$($values[$i])" 
                } 

                $updateString = $list -join ", ";
                $whereString = ($objectKeys | % { "[$_]=$($whereValues[$whereColumns.IndexOf($_)])"}) -join " AND ";
                
                $columns = $columns | % { "[$_]" }
                
                switch ($mode)
                {
                    'Insert' {
                        $query = "INSERT INTO $table ($( $columns -join ", ")) VALUES ($( $values -join ", "))";
                    }
                    'Update' {
                        $query = "UPDATE $table SET $updateString WHERE ($whereString)"
                    }
                    'InsertOrUpdate' {
                        $query = @"
                                    IF EXISTS (SELECT * FROM $table WHERE $whereString)
                                        BEGIN
                                            UPDATE $table SET $updateString WHERE ($whereString)
                                        END
                                    ELSE
                                        BEGIN
                                            INSERT INTO $table ($( $columns -join ", ")) VALUES ($( $values -join ", "))
                                        END
"@

                    }
                    'Delete' {
                        $query =  "DELETE FROM $table WHERE ($whereString)"
                    }
                }

                $queries += $query; 
                #$rowCount += Write-SQLData -query $query -conn $conn;
            }
            $rowCount = Write-SQLData -query $queries -conn $conn;

        } catch {
            Write-Host -BackgroundColor Red "Error: $($_.Exception)"
            break;
        }
    }

    end {
        If ($?) {
            $rowCount
            #Write-Host 'Completed Successfully.'
            #Write-Host ' '
        }

        #return $?;
    }

}