functions/private/Confirm-IsValidConnection.ps1
|
function Confirm-IsValidConnection { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $name, [Parameter(Mandatory = $true)] [string] $type, $value ) $result = @{check = "IsValidConnection"; name = $name; type = $type; value = $value} if ($value -is [Hashtable] -and $value.ContainsKey("sqlConnection") -and $value.ContainsKey("sqlTestCommand")) { # confirm string is a valid try { $connection = New-Object System.Data.SqlClient.SQLConnection($value.sqlConnection) # confirm access to connection try { $connection.Open() try { $command = New-Object System.Data.SqlClient.SqlCommand($value.sqlTestCommand, $connection) $out = $command.ExecuteReader() if (($out | Measure-Object).Count -eq 0) { throw } $result.Add("errorFlag", 0) } catch { $result.Add("errorFlag", 1) $result.Add("level", "Fatal") $result.Add("message", "Test sql command failed '$($value.sqlTestCommand)' please check database connection settings.") } } catch { $result.Add("errorFlag", 1) $result.Add("level", "Fatal") $result.Add("message", "Could not connect to '$($value.sqlConnection)' please check database connection settings.") } finally { $connection.Close(); } } catch { $result.Add("errorFlag", 1) $result.Add("level", "Fatal") $result.Add("message", "Invalid connection string '$($value.sqlConnection)'.") } } else { $result.Add("errorFlag", 1) $result.Add("level", "Fatal") $result.Add("message", "Valid connection string requires a hashtable with both sqlConnection and sqlTestCommand keys (example: connection = @{sqlConnection=""Data Source=<server>;Initial Catalog=<database>;Integrated Security=True;"";sqlTestCommand=""SELECT <test> FROM <schema><table>""}") } return (New-CheckResult @result) } |