<#
Function for extracting SQL data into CSV format that is friendlier for excel than the the way SQL management studio does it. (line breaks in text causes excel to fuck up due to lack of string delimiters in sqlmanstud)
Simply replace the sqlcommand string at the bottom, the data will be output to a file in the current path in which the scriptenv runs.

Written by Emil Holgersen on 2020-10-30
#>

function Invoke-SQL {
    param(
        [string] $dataSource = 'rejk-p-sql003\sql01',
        [string] $database = 'AfcCookedIFSv2',
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables

}

#Example:
$data = Invoke-SQL -sqlCommand 'SELECT * FROM [AfcCookedIFSV2].[pom].[ObjOrder] o Join [AfcCookedIFSV2].[pom].objordercardordering oco on oco.orderid = o.orderid Where Customerid in (''396919'')'
$data | Export-Csv -Path .\SQLData.csv -NoTypeInformation -Encoding Unicode