Connect to SQL with Powershell

Connect to SQL Server with Powershell used to feed a powershell dashboard

Script post colors https://copdips.com/2018/05/grep-like-powershell-colorful-select-string.html

Param(

[Parameter(Mandatory=$false,

ValueFromPipeline=$true)]

[String[]]

$dataType=1

)
# Check if SqlServer module is installed and install it

if (!(Get-InstalledModule sqlServer)) {Install-Module -Name SqlServer}

# Param input 1 for facilites and 2 for providers, option 3 to set password

[string] $Server= "SQLServer"

[string] $Database = "myDatabase"

[string] $UserSqlQuery= @"

select * from my table

"@

[string] $UserSqlQuery2= @"

select * from my table2

"@

$outfile = "C:\support\scripts\dashboard\data\my-data1.xml"

if ($dataType -eq 3){ # Save password creds to file, must be set per machine

$c | Export-CliXml -Path 'C:\support\scripts\dashboard\data\cred.xml'

Break

}

if ($dataType -eq 2)

{$UserSqlQuery=$UserSqlQuery2

$outfile = "C:\support\scripts\dashboard\data\my-data2.xml"

}

$resultsDataTable = New-Object System.Data.DataTable

$credential = Import-CliXml -Path C:\support\scripts\dashboard\data\cred.xml

#Run SQL Command

function run-SqlQuery ($sqlServer, $sqlDatabase, $sqlQuery, $dbCreds ) {

$dbUser = $dbCreds.UserName#.Replace("\","")

$dbPass = $dbCreds.GetNetworkCredential().password

$dbDataSet=invoke-sqlcmd -query $sqlQuery -database $sqlDatabase -serverinstance $sqlServer -username $dbUser -password $dbPass

return $dbDataSet

}

$resultsDatatable = run-sqlQuery $Server $Database $UserSqlQuery $credential

$resultsDatatable |Export-Clixml -Path $outfile