Run a SQL Server query using .NET (without the "SqlServer" module)

Invoke-ParamQuery.ps1

Function Invoke-ParamQuery {
    param(
        [String]$Query,
        $Parameters=@{},
        [Data.SqlClient.SqlConnection]$Conn,
        [int]$Timeout=3,
        [switch]$CloseConn,
        [switch]$DiscardResults
    )
    if ($conn.State -eq "Closed") {
        $conn.Open()
    }
 
    $cmd = New-Object System.Data.SqlClient.SqlCommand($Query,$Conn)
    $cmd.CommandTimeout = $Timeout
 
    ForEach ($p in $Parameters.Keys) {
        [void]$cmd.Parameters.AddWithValue("@$p",$Parameters[$p])
    }
 
    $dataTable = New-Object System.Data.DataTable
    if ($DiscardResults) {
        # Execute without result sets
        $rowsAffected = $cmd.ExecuteNonQuery()
    } else {
        # Create a data table that collect the results of the query
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
        $dataAdapter.Fill($dataTable) | Out-Null
    }
 
    If ($CloseConn -and $conn.State -eq "Open") {
        $conn.Close()
        $conn.Dispose()
    }
 
    return $dataTable
}
 
Function New-Connection {
    param(
        [string]$Instance,
        [string]$Database
    )
    $conn = New-Object Data.SqlClient.SqlConnection "Server=$Instance;Initial Catalog=$Database;Integrated Security=True"
 
    return $conn
}